selecting the last completed month when the report runs by default .

[cognos05]

Hi ,

Transformer cube is source.
I have a prompt where i show all month level members  and use the selected month in report.

it has month like

Jul 2019
Aug 2019
sep 2019

ETc ..

my query to the prompt is

[SALES].[Time Period].[Time Period].[Months]

Just the month level .


now i need to preselect the month in prompt which is last completed month

[SALES].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[20191101-20191130]

I am able to form this expression in a data item ,but is it possible to set the data item as a default selection ? in the prompt ?

How to achieve this

[cognos05]

This is the value I am adding in the default selection .

#'[SALES].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].['+timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month(timestampMask($current_timestamp,'yyyy-mm-dd')+'T00:00:00Z'),'yyyymmdd') + ']'#

I think default selection will accept only static values, so i am not sure how to achieve this.

[dougp]

Is this for something you want to schedule, or are you trying to set a default for a user running the report interactively?

I have found that if I want to schedule a report that has a date prompt, I must remove the prompt and compute the default value in the filter.

If you want to use a computed default value in a date prompt on a prompt page or report page -- well, you can't (yet).  You will need to use JavaScript to make that happen.

[cognos05]

how to preselect it for report running interactively.

I saw this post
https://blogs.perficient.com/2017/03/09/value-prompt-with-dynamically-chosen-default-values-in-cognos-rs/

but not sure how to relate it with my mun value and level member

case when [Year]=year(current_date) then 1 else [Year] end

in my case my level value returns all months in level

[SALES].[Time Period].[Time Period].[Months]

this is mun value for current month

#'[SALES].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].['+timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month(timestampMask($current_timestamp,'yyyy-mm-dd')+'T00:00:00Z'),'yyyymmdd') + ']'#


so how can i create a dataitem which would compare my month level value and current month macro mun and return 1.

I tried to do something like this and got incompatible data type error
case when [Timeperiod]=#'[SALES].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].['+timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month(timestampMask($current_timestamp,'yyyy-mm-dd')+'T00:00:00Z'),'yyyymmdd') + ']'#
Then
(1)
Else
([Timeperiod])
END

Any suggestions is appreciated.

[CognosPaul]

It looks like you're thinking about this relationally. You would never want to do an if/then = 1 like this.

Are you trying to filter the entire query by this one month? Put the mun into your slicer.

Try this:

Code Select Expand

#prompt(
   'Month' /*parameter name*/
, 'mun' /*data type*/
, '[SALES].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].['
  +timestampMask(_first_of_month($current_timestamp),'yyyymmdd')
  + '-'
  + timestampMask(_last_of_month(timestampMask($current_timestamp,'yyyy-mm-dd')+'T00:00:00Z'),'yyyymmdd')
  + ']' /*default value*/
)#


[cognos05]

so we cannot preselect a dynamic month value in a prompt? It has to be filtered only on the report?

[MFGF]

so we cannot preselect a dynamic month value in a prompt? It has to be filtered only on the report?

If you look more carefully at the code Paul posted you'll see it is a prompt macro with a default argument of the current month. Using this in your report - in this case as a slicer - will result in a prompt with a default. Paul has added comments to illustrate what each of the arguments is too.

Cheers!

MF.