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
Quote from: ricky_villa81 on 09 Mar 2016 03:43:52 AM
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.
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)
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] )
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
Quote from: ricky_villa81 on 09 Mar 2016 04:25:25 AM
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.
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
Got it, working now... Sorry missed the difference you pointed out before.
Quote from: ricky_villa81 on 09 Mar 2016 06:06:25 AM
Did you have both items in the same query at the same time?
Yep - stacked in the rows of a crosstab.
Quote from: ricky_villa81 on 09 Mar 2016 06:06:25 AM
What is your data source? Mine is a Transformer cube...
The same - a Powercube (the sample great-outdoors_sales_en cube)
Quote from: ricky_villa81 on 09 Mar 2016 08:32:35 AM
Got it, working now... Sorry missed the difference you pointed out before.
Great! Glad you got a working solution! :)
MF.