Convert String to MUN

[Ricardo Julio "Ricky" Villa]

Hi,

Does anyone know how to convert a string to a MUN?

I need to take the category code from the Current Month relative time category then use this to construct the MUN for the same month in the default time hierarchy.

I have successfully done this to get the correct MUN, but as a string only (shown below) - I am unable to convert this string to a MUN.

This expression:
substring ( rolevalue ( '_memberUniqueName', children ( [Production Revenue Cube].[All Dates].[Month Categories]->:[PC].[@MEMBER].[Current Month] ) ) , 57 )
Gives me:
'->:[PC].[@MEMBER].[20160201-20160229]'

Which I then concatenate to get the full MUN path in the default time hierarchy:
'[Production Revenue Cube].[All Dates].[All Dates].[Month]' +

substring ( rolevalue ( '_memberUniqueName', children ( [Production Revenue Cube].[All Dates].[Month Categories]->:[PC].[@MEMBER].[Current Month] ) ) , 57 )

End up with:
'[Production Revenue Cube].[All Dates].[All Dates].[Month]->:[PC].[@MEMBER].[20160201-20160229]'

How can I remove the quotes ( ' ) to get a MUN...?

Cheers,

Ricardo Julio "Ricky" Villa

[MFGF]

Hi,

Does anyone know how to convert a string to a MUN?

I need to take the category code from the Current Month relative time category then use this to construct the MUN for the same month in the default time hierarchy.

I have successfully done this to get the correct MUN, but as a string only (shown below) - I am unable to convert this string to a MUN.

This expression:
substring ( rolevalue ( '_memberUniqueName', children ( [Production Revenue Cube].[All Dates].[Month Categories]->:[PC].[@MEMBER].[Current Month] ) ) , 57 )
Gives me:
'->:[PC].[@MEMBER].[20160201-20160229]'

Which I then concatenate to get the full MUN path in the default time hierarchy:
'[Production Revenue Cube].[All Dates].[All Dates].[Month]' +

substring ( rolevalue ( '_memberUniqueName', children ( [Production Revenue Cube].[All Dates].[Month Categories]->:[PC].[@MEMBER].[Current Month] ) ) , 57 )

End up with:
'[Production Revenue Cube].[All Dates].[All Dates].[Month]->:[PC].[@MEMBER].[20160201-20160229]'

How can I remove the quotes ( ' ) to get a MUN...?

Cheers,

Ricardo Julio "Ricky" Villa

Hi,

Have you looked at the linkmember() function? Sounds like this will do exactly what you need? No need to mess around with strings or MUNs - just plug in the relative category code and the month level of the main time hierarchy as arguments.

Cheers!

MF.

[Ricardo Julio "Ricky" Villa]

Cheers dude, that worked as:

linkMember ( firstChild ( [Production Revenue Cube].[All Dates].[Month Categories]->:[PC].[@MEMBER].[Current Month] ), [Production Revenue Cube].[All Dates].[All Dates] ) )

Out of interest, do you know if it is possible to convert a string to a MUN (without using prompt / substitute macro)...?

Easy now,

Ricardo Julio "Ricky" Villa (scorer of that goal, 1981)

[Ricardo Julio "Ricky" Villa]

Typo in last post, removed last bracket:

linkMember ( firstChild ( [Production Revenue Cube].[All Dates].[Month Categories]->:[PC].[@MEMBER].[Current Month] ), [Production Revenue Cube].[All Dates].[All Dates] )

[Ricardo Julio "Ricky" Villa]

Unfortunately, does not look like this solution will work :(

I have used this expression to create a "Current Month" data item:
linkMember ( firstChild ( [Production Revenue Cube].[All Dates].[Month Categories]->:[PC].[@MEMBER].[Current Month] ), [Production Revenue Cube].[All Dates].[All Dates] )

When I create a second data item such as:

"Current Year" data item:
linkMember ( firstChild ( [Production Revenue Cube].[All Dates].[Year Categories]->:[PC].[@MEMBER].[Current Year] ), [Production Revenue Cube].[All Dates].[All Dates] )

Expected result:
"Current Month" = Feb 2016
"Current Year" = 2016

Actual result:
"Current Month" = Feb 2016
"Current Year" = Feb 2016

Or both = 2016 if "Current Year" placed as first data item in the query.

Using ancestor ( [Current Month] ) also gives same incorrect results.

Suggestions...?

Cheers,

Ricardo Julio "Ricky" Villa

[MFGF]

Unfortunately, does not look like this solution will work :(

I have used this expression to create a "Current Month" data item:
linkMember ( firstChild ( [Production Revenue Cube].[All Dates].[Month Categories]->:[PC].[@MEMBER].[Current Month] ), [Production Revenue Cube].[All Dates].[All Dates] )

When I create a second data item such as:

"Current Year" data item:
linkMember ( firstChild ( [Production Revenue Cube].[All Dates].[Year Categories]->:[PC].[@MEMBER].[Current Year] ), [Production Revenue Cube].[All Dates].[All Dates] )

Expected result:
"Current Month" = Feb 2016
"Current Year" = 2016

Actual result:
"Current Month" = Feb 2016
"Current Year" = Feb 2016

Or both = 2016 if "Current Year" placed as first data item in the query.

Using ancestor ( [Current Month] ) also gives same incorrect results.

Suggestions...?

Cheers,

Ricardo Julio "Ricky" Villa

Hi,

Sounds like you did something weird here. I just successfully tested the following:

linkMember( firstChild( [my Current Month relative time member]), [Month level from my main Time hierarchy])

This gave me the current month member but from the main hierarchy not from the relative time hierarchy.

ancestor( linkMember( firstChild( [my Current Month relative time member]), [Month level from my main Time hierarchy]), [Year level from my main Time hierarchy])

This gave me the current Year member from the main hierarchy (ie the year member the Current Month member is a descendant of).

Cheers!

MF.

[Ricardo Julio "Ricky" Villa]

Hi,

Did you have both items in the same query at the same time?

What is your data source?  Mine is a Transformer cube...

Cheers,

Ricardo Julio "Ricky" Villa

[Ricardo Julio "Ricky" Villa]

Got it, working now... Sorry missed the difference you pointed out before.

[MFGF]

Did you have both items in the same query at the same time?

Yep - stacked in the rows of a crosstab.

What is your data source?  Mine is a Transformer cube...

The same - a Powercube (the sample great-outdoors_sales_en cube)

Got it, working now... Sorry missed the difference you pointed out before.

Great! Glad you got a working solution! :)

MF.