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
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.
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.
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.
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:
#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*/
)#
so we cannot preselect a dynamic month value in a prompt? It has to be filtered only on the report?
Quote from: cognos05 on 01 Jan 2020 01:33:26 PM
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.