How to parse MUN value in cognos?

[Sue]

Hello All,

The MUN looks something like the one below: [DWActivity].[AllDates].[AllDates].[Month]->:[PC].[@MEMBER].[20120901-20120930]
How to parse just [20120901-20120930]?  I need to build a report based on 2 different cubes. I don't have to combine the data but I use a value prompt(for Month Level). Is there a way I could use the parsed MUN in report filters? Please help

Thanks much!!

[pricter]

If the muns of the two cubes are alike you can use a combination of macro
to change the mun.

More specifically you can use 'substitute' and 'prompt' macro.

which is the mun of the second cube?

[Sue]

Thanks so much!! Yes the MUN is the same on the 2nd cube as well. Can you please help me with an example on how to use substitute in a prompt macro? I have tried but it did not work. Please advise.

[pricter]

Unfortunately I do not have a package with two cubes in order to test it but I
imagine that the following could work

Code Select Expand

#substitute('DWActivity','DWActivity2',prompt('month','memberuniquename'))#

assuming that the MUN of the second cube is

[DWActivity2].[AllDates].[AllDates].[Month]->:[PC].[@MEMBER].[20120901-20120930]

[Sue]

Thanks once again for your response!!
I am not able to get this to work! :(
Is this calc a slicer or dataitem or filter? Sorry I am new to MDX!
* I have a value prompt that displays month using month level from cube 1.
* Then I have a simple list report from cube 1 (this works)
* Then I bring in another list report from cube 2. How do I use the date filter from the value prompt in this query?
* Also I need to display YTD numbers based on the value prompt as well.

Please help!

[pricter]

Which is the MUN of the second cube?

Then I bring in another list report from cube 2. How do I use the date filter from the value prompt in this query?

Use as data item

Code Select Expand

#substitute('DWActivity','DWActivity2',prompt('month','memberuniquename'))#

Code Select Expand

* Also I need to display YTD numbers based on the value prompt as well.

Use the periodsToDate ( level , member )
Returns a set of sibling members from the same level as "member", as constrained by "level". It locates the ancestor of "member" at "level" and returns that ancestor's descendants at the same level as "member" (up to and including "member"). Typically used with a time dimension. This function appears in the Rolling and Moving Averages interactive sample report.


Example: periodsToDate ( [great_outdoors_company].[Years].[Years].[Year] , [2004/Mar] )
Result: Returns values for [2004/Jan], [2004/Feb], [2004/Mar]

[Sue]

Thanks so much!!!

[DWActivity].[AllDates].[AllDates].[Month]->:[PC].[@MEMBER].[20120901-20120930] - Cube1 MUN

[DWActivity2].[AllDates].[AllDates].[Month]->:[PC].[@MEMBER].[20120901-20120930] - Cube2 MUN

So in the code,  #substitute('DWActivity' ---> is this the month level that I have to use something like ([DWActivity].[AllDates].[AllDates].[Month])? Please advise.

[pricter]

The
prompt('month','memberuniquename')
returns the MUN of the selected month
:
[DWActivity].[AllDates].[AllDates].[Month]->:[PC].[@MEMBER].[20120901-20120930]

and the substitute search the above for 'DWActivity' and replace it with the 'DWActivity2'

So as a results you will have
[DWActivity2].[AllDates].[AllDates].[Month]->:[PC].[@MEMBER].[20120901-20120930]

which is the MUN of the month for the second cube.

[Sue]

Thank you !!! Thank you!! Truly appreciate your help !!