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.
Can you get away with offering a YTD checkbox? Can you also describe your hierarchy in a little more detail?
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 .
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.
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 .
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
#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.
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,
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.
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 .
token is an expression fragment.
consider this:
#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.