Dynamic prompt default value

[oscarca]

Hello Cognoisers,

I am trying to create a detailed filter that either filters the week that you have selected or if nothing is selected in the prompt then it will filter current week as default.
This is as far I have come with the solution:

([Week] in (?pWeek?)) or (?pWeek? is null and [Week] = [Current week])

Current Week data item looks like this = cast(extract(year;current_date);VARCHAR(4)) + '/' + cast(_week_of_year(current_date);VARCHAR(2)), which gives me for example 2015/48.
The filter doesnt give me any error but if I dont choose any week(s) then it generates all weeks instead of current week which would be the desired result.
If anyone has a solution for this it would be very much appreciated.

Best regards,
Oscar

[CognosPaul]

Prompt macros would work great here
Use this as a filter:

Code Select Expand

#promptmany(
   'pWeek'
  ,'string'
  ,'[Week]=[Current week]'
  ,'[Week] in ('
  ,''
  ,')'
)
#

What's happening for you is if the pWeek prompt alias is empty, the entire filter is removed. By using the default parameter in the prompt function you can explicitly define what happens when the pWeek parameter is empty.

[oscarca]

Hey Paul,

I tried this filter:
#promptmany(
   'pWeek'
  ,'string'
  ,'[Week]=[Current week]'
  ,'[Week] in ('
  ,''
  ,')'
)
#

and doesn't seem to work either. When I run the report and don't select any week in the prompt it still gives me all the weeks in the list. But if I make the filter (prompt macro) required it gives me automatically Current week. Do I need to make any static choices or default selections ?. The desired result would be If don't choose anything in the prompt it will filter the report with current week i.e to have it optional. I attach the report example.

[MFGF]

Hey Paul,

I tried this filter:
#promptmany(
   'pWeek'
  ,'string'
  ,'[Week]=[Current week]'
  ,'[Week] in ('
  ,''
  ,')'
)
#

and doesn't seem to work either. When I run the report and don't select any week in the prompt it still gives me all the weeks in the list. But if I make the filter (prompt macro) required it gives me automatically Current week. Do I need to make any static choices or default selections ?. The desired result would be If don't choose anything in the prompt it will filter the report with current week i.e to have it optional.

Hi,

Where are you using this expression? In a detail filter? If so, you're trying to use a relational reporting technique (detail filters) in a dimensional report, and that can lead you to so much pain and anguish :)

Remove your detail filter and use the expression in a query calculation directly in your crosstab...

Cheers!

MF.

[oscarca]

Thank you CognosPaul
It actually solved it by making the filter required, so ignore last comment.
Could the same logic also be applied to:  ?
cast(year([Date]), VARCHAR(4)) = substring(?pYTD?;1;4) and [Datum] <= ?pYTD? + '-31'

i.e if pYTD is not selected it would take Year to date from current month instead ?

(cast(year([Date]), VARCHAR(4)) = substring(?pYTD?;1;4) and [Date] <= ?pYTD? + '-31') or (?pYTD? is null and cast(year([Date]), VARCHAR(4)) = substring([Current month];1;4) and [Date] <= [Current month] + '-31')

Aslo congrats on the new GIS integration job.


MFGF, the report is relational not dimensional but I get your point.  Thank you for also good advice :)

[CognosPaul]

In general it's a bad idea to filter on a function. Indexes generally can't be used with a function, so it would cause a full table scan.

The +'-31' in the expression confuses me. But I think otherwise I know what's going on. Is Current month and pYTD returning a value that looks like YYYY-MM? So concatenating -31 onto it would effectively build a quasi valid date string? Does your database know what to do with 2018-02-31?

The solution is still to use macro functions:

Code Select Expand

#prompt(
  'pYTD'
  ,'token'
  , '[Date] between ' + sq(timestampMask(_first_of_month($current_timestamp),'yyyy-mm-dd')) + ' and ' + sq(timestampMask(_last_of_month($current_timestamp),'yyyy-mm-dd'))
  , '[Date] between '''
  , ''
  , '-01'' and ' + sq(timestampMask(_last_of_month(prompt('pYTD','token',timestampMask($current_timestamp,'yyyy-mm'))+'-01T00:00:00.000Z'),'yyyy-mm-dd'))
  )
#



This is assuming pYTD is returing a value like 2018-02. If pYTD is null, it will return something like [Date] between '2018-10-01' and 2018-10-31'. If a value is selected in pYTD, like 2018-02, it will return [Date] between '2018-02-01' and '2018-02-29'