Hi
I need to build a report studio report based on 2 different cubes. I don't have to combine the datasets but I need to use the value prompt based on the Month level in the report globally. How do I use the prompted date value in the report globally? Should I use filter() and item()? Is there a better way to accomplish this?
Truly appreciate your help!
Sue
If the category codes are constructed the same for the date dimensions in both cubes, you can use a prompt macro to construct a mun. The prompt would pass the category code (use value) into the mun prompt macro.
For constructiong a MUN with a prompt macro:
http://www-01.ibm.com/support/docview.wss?uid=swg21343356
Thanks tjohnson!! Sorry for responding late. I have been trying to get this to work for the past 2 days, no luck. The category code for the month is something like ("2012-09-01","2012-09-30"). The category code for the month level in both the cubes are the same but I get model item invalid error. How to use prompt macro to handle category code something like the one above?
Also, how to calculate YTD using prompt macro? Please advise.
Post your MUN
I used the following filter and it seems to be working now. But the value prompt picks only one month and I am not sure how to use the prompt macro to pick one value as opposed to promptmany.
filter([DW_XYZ].[All Dates].[All Dates].[Month],[DW_XYZ].[All Dates].[All Dates].[Month].[Month - Category Code] in (#promptmany('p_Month','string')#))
Also, how to use periodsToDate() with the above filter to calculate YTD sales? Please advise.
Thanks so much!!
For a single value, instead of using a filter function, you can construct the entire MUN following this example:
#'[great_outdoors_sales_en].[Products].[Products].[Product line]->:[PC].[@MEMBER].['+prompt('PLine','token')+']'#
The periods to date function needs a single MUN, so if you use the above syntax, it will work with the periods to date function.