Hi All ,
Need a quick help, how can we extract the MUN of a Member Selected in the prompt ?
Quote from: riteshsinghrathod on 21 Aug 2014 11:19:50 AM
Hi All ,
Need a quick help, how can we extract the MUN of a Member Selected in the prompt ?
It depends on what your prompt is returning. It might be a member, a set or a value. It also depends what you want to do with the MUN. Can you tell us a little more about what you're trying to achieve?
MF.
Hi ..
Thanks for your reponse.
Its a tree prompt for time Period Dimension (Source is TM1 Cube and we dont have levels for year,quarter or month).
What Im trying to get is YTD revenue for the selected month. Since Im missing levels.. I wont be able to use periodstoDate function..Even if I try to use lastperiod function thats not helpful as its not accepting substring (coercion error).
So the only option I think of is getting MUN values in query Calculation for YTD.
The good thing is that we have months in 201404(yyyymm) and we have direct members for Month YTD in format "201404 YTD". So the plan is to populate "201404 YTD" in the query calculation for the 201404 month selection.
Quote from: riteshsinghrathod on 22 Aug 2014 03:02:01 AM
Hi ..
Thanks for your reponse.
Its a tree prompt for time Period Dimension (Source is TM1 Cube and we dont have levels for year,quarter or month).
What Im trying to get is YTD revenue for the selected month. Since Im missing levels.. I wont be able to use periodstoDate function..Even if I try to use lastperiod function thats not helpful as its not accepting substring (coercion error).
So the only option I think of is getting MUN values in query Calculation for YTD.
The good thing is that we have months in 201404(yyyymm) and we have direct members for Month YTD in format "201404 YTD". So the plan is to populate "201404 YTD" in the query calculation for the 201404 month selection.
Hi,
If you're using a tree prompt, how can you ensure the user is choosing a month member (as opposed to a quarter member or a year member)?
I guess you could use the roleValue() function to get hold of the MUN but I don't see how it would help you here?
roleValue ( '_memberUniqueName' , [your hierarchy] -> ?YourParam?)
MF.
hmmm...
Let me try...
Any help on how can we modify the generated MDX query.
Quote from: riteshsinghrathod on 22 Aug 2014 08:14:10 AM
hmmm...
Let me try...
Any help on how can we modify the generated MDX query.
Hmmm.
Assuming the structure of the hierarchy is Year > Quarter > Month you could use the periodsToDate() function as follows:
periodsToDate(level(ancestor([Your time hierarchy] -> ?YourParam?, 2)), [Your time hierarchy] -> ?YourParam?)
This would prompt for the member (ie the month), go up two levels to get the year, return the level (which exists even though it isn't explicitly named in TM1) and use this in the periodsToDate function.
Does this give you what you need?
MF.
Have tried this but returns the coercion error.
Quote from: riteshsinghrathod on 22 Aug 2014 09:43:27 AM
Have tried this but returns the coercion error.
Hi,
A coercion error probably means you put the wrong object into the expression. In the expression above, the object is the hierarchy you see when viewing the package using the "Metadata Tree" view. I'm guessing you dragged the top-level member of the hierarchy in by mistake if you are viewing the "Members Tree" view of the package? You can tell you have the correct object, because it will appear in the form [Package name].[Dimension name].[Hierarchy name] when you insert it.
Cheers!
MF.