Getting Month and its fytd populate in a prompt

[cognos05]

Hi ,

I have a requirement to show May 2019 and May 2019 FYTD  , June 2019 and Jun 2019 FYTD etc in a prompt, so users can select either May 2019 or May 2019 FYTD .

May 2019 FYTD - is nothing but Jul 2018 to May 2019 months based on fiscal period.

My hierarchy in the transformer cube is like

FY 2019  Fiscal Year
July 2018  Fiscal Month
Aug 2018
Jun 2019

So now I have to do a periods to total on May 2019 to get months from jun 2018 to may 2019 . I have to generate the FYTD for each member .

How can i do a dynamic expression and feed to the prompt .

My output I expect is something like

Jul 2018
July 2018 FYTD
Aug 2018
Aug 2018 FYTD
etc..
Jun 2019
Jun 2019 FYTD


Thanks and any suggestions is appreciated.

[CognosPaul]

Can you get away with offering a YTD checkbox? Can you also describe your hierarchy in a little more detail?

[cognos05]

Attached the image of time hierarchy .

Yes I can check if check box is a good option , but would like to know if something getting these combined is possible.

Even if its hard coded for 2 years I think it will be okay to update once a year .

[CognosPaul]

Since you're using PowerCubes you're not using DQM, which means you can't use the macro case statement.

If you CAN use a YTD checkbox, do the following


Create three data items.

Data Item Name	Expression
Month Not YTD	#prompt('Month','mun')#
Month YTD	periodsToDate([Cube].[Dim].[Hier].[YearLevel],#prompt('Month','mun')#)
Month	#sb(promptmany('YTD','token','Month Not YTD'))#

Make a checkbox prompt with the parameter name "YTD". Static display value "YTD" use value "Month YTD"

In your data container use the Month data item. When the YTD checkbox is checked it will point to the Month YTD data item, otherwise it will point to the Month Not YTD data item.

This also assumes the user doesn't need to select multiple months.

[cognos05]

Hi Paul ,

Month YTD   periodsToDate([Cube].[Dim].[Hier].[YearLevel],#prompt('Month','mun')#)/td]


what is /td at the end of month ytd expression .

I am trying to understand what you have suggested.

so first I create a month prompt with month values in it . not the years just month , for example , i havce something like children ([2019]) which will list jul 2018 to jun 2019 .

Then I create 3 data items as you stated.

Then create a checkbox with static values on it having YTD as display and "Month YTD" as use value .

can you explain me the sequence on how this works.

Thanks for all your help .

[CognosPaul]

That /td] should not have been there, I removed it from the expression.

You should have two queries - one feeding your prompt and another for the report.

In the prompt query you can use something like children([2019]) or even just the month level.

In the report query you should have the three data items like I said.

The month data item should have

Code Select Expand

#sb(promptmany('YTD','token','Month Not YTD'))#

Another problem there - the parameter data type needs to be token, not mun. The sb wraps the returned value from the prompt function in square brackets. The prompt function defaults to Month Not YTD.

So if the checkbox isn't selected, the data item would look like:

[Month Not YTD]

but if the checkbox is selected the end result would be:
[Month YTD]

It's simply a complicated way of selecting the correct data item that you want. When the checkbox is selected the selected month will be wrapped in the periodsToDate function, whereas if the checkbox is not selected it will just return the month.

[cognos05]

Hi Paul,

Thanks for your help,This is working fine , I have only one issue, so the autosubmit option is not there for checkbox group , so when I select a month and uncheck the box the values will not change until I choose another month.

Can we do anything to get this functionality.

Thanks,

[CognosPaul]

Instead of a checkbox, use a radio prompt that has

Display	Use
YTD	Month YTD
Not YTD	Month Not YTD

It is possible to write JS that will set up the autosubmit, but let's try this first.

[cognos05]

can you tell me when we should use token and when we should use mun .

Also does sb takes all the month ytd values and put square bracket around it .

[CognosPaul]

token is an expression fragment.

consider this:

Code Select Expand

#prompt('topOrBottom','token','top') + 'Count')#([set],20,[Measure])

The token allows us to modify any part of an expression, including parts of a function.

a mun prompt has additional validation to make sure the value you're passing is actually a member. You can pass muns to a token prompt, but you can't use a mun prompt to change functions.