Building MUN using Tree prompt

[riteshsinghrathod]

Hi guys,

Need help in building MUN using Tree prompt.

The query which I'm using in Dataitem to build MUN looks like this...

#'[Cube].[Time Period hierarchy].[Time Period]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^'+prompt('P_Month','memberuniquename','[201501 ]')+' YTD]'#


But this errors out as it's bringing in whole MUN instead of only selected member(Example- 201507).

I want something like this---

[Cube].[Time Period hierarchy].[Time Period]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^201507 YTD]

But I'm getting

[Cube].[Time Period hierarchy].[Time Period]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^[Cube].[Time Period hierarchy].[Time Period]->:[TM].[Time Period].[Time Period].[@MEMBER].[2015^20153^201507] YTD]


Note : Using token instead of memberuniquename also brings in the same error

[MFGF]

Hi guys,

Need help in building MUN using Tree prompt.

The query which I'm using in Dataitem to build MUN looks like this...

#'[Cube].[Time Period hierarchy].[Time Period]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^'+prompt('P_Month','memberuniquename','[201501 ]')+' YTD]'#


But this errors out as it's bringing in whole MUN instead of only selected member(Example- 201507).

I want something like this---

[Cube].[Time Period hierarchy].[Time Period]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^201507 YTD]

But I'm getting

[Cube].[Time Period hierarchy].[Time Period]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^[Cube].[Time Period hierarchy].[Time Period]->:[TM].[Time Period].[Time Period].[@MEMBER].[2015^20153^201507] YTD]


Note : Using token instead of memberuniquename also brings in the same error

Hi,

If your prompt is returning a MUN, wouldn't your macro just look like this?

#prompt('P_Month','memberuniquename','[Cube].[Time Period hierarchy].[Time Period]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^201501 YTD]')#

Cheers!

MF.

[riteshsinghrathod]

Hi MF,

We can specify direct member/MUN in the default value it should be fine. But however If I use MUN then default value ll be month as Im prompting user for month selection. it ll look like this.

#prompt ('P_Month','memberuniquename','[Cube].[Time Period dim].[Time Period hieracrchy]->:[TM].[Time Period].[Time Period].[@MEMBER].[2015^20153^201507]')#



note: Source is tm1 cube.

[MFGF]

Hi MF,

We can specify direct member/MUN in the default value it should be fine. But however If I use MUN then default value ll be month as Im prompting user for month selection. it ll look like this.

#prompt ('P_Month','memberuniquename','[Cube].[Time Period dim].[Time Period hieracrchy]->:[TM].[Time Period].[Time Period].[@MEMBER].[2015^20153^201507]')#



note: Source is tm1 cube.

Sorry - I can't make sense of what you're saying. What is the issue?

MF.

[riteshsinghrathod]

I have created a dataitem in which Im using below logic..

'[Cube].[Time Period dim].[Time Period hierarchy]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^'+roleValue('_memberCaption',#prompt ('P_Month','memberuniquename','[201501]')#)+' YTD]'

Im able to obtain the MUN(given below) which i was looking for, but I believe cognos is treating this as a string instead of member, and hence it just displays whole MUN(used a singleton) in report instead of member.

[Cube].[Time Period dim].[Time Period hierarchy]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^201504 YTD]

I also receive the below error when I try to pull this dataitem in the cross tab layout(the main).

Error:
MDO-ERR-0002

      Not able to correctly prepare the decomposed query 'Query1' 

[MFGF]

I have created a dataitem in which Im using below logic..

'[Cube].[Time Period dim].[Time Period hierarchy]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^'+roleValue('_memberCaption',#prompt ('P_Month','memberuniquename','[201501]')#)+' YTD]'

Im able to obtain the MUN(given below) which i was looking for, but I believe cognos is treating this as a string instead of member, and hence it just displays whole MUN(used a singleton) in report instead of member.

[Cube].[Time Period dim].[Time Period hierarchy]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^201504 YTD]

I also receive the below error when I try to pull this dataitem in the cross tab layout(the main).

Error:
MDO-ERR-0002

      Not able to correctly prepare the decomposed query 'Query1'

Why are you doing this? Why not just prompt the user for the member they want to use?

You're actually building a character string in the report - not a MUN. If you were building a MUN your literal at the beginning of the expression would be within the macro, but then you couldn't use a _roleValue() function within the macro.

You need to understand that a macro will be evaluated when the query is being formulated (ie before it gets executed) whereas to use a dimensional function such as _roleValue() this is processed as part of the query execution. These are obviously different timings - one returns results before the query is executed, the other returns results after the query is executed. In a nutshell, the approach you are using here will not work.

MF.

[riteshsinghrathod]

Hi MF,

Users are prompted for month selection under P_Month parameter....

For this user selected month, I need to get the YTD member.

For example if user selects 201405 month, then I need a column displaying me revenue by using 201405YTD member(we have Month YTD members built in) and hence Im trying to build MUN.

As highlighted earlier I used ..#'[Cube].[Time Period].[Time Period]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^'+prompt ('P_Month','memberuniquename','[Cube].[Time Period].[Time Period]->:[TM].[Time Priod].[Time Period].[@MEMBER].[2015^20153^201507]')+' YTD]'#

but this gives parsing error on or before 146 of ********.................

[MFGF]

Hi MF,

Users are prompted for month selection under P_Month parameter....

For this user selected month, I need to get the YTD member.

For example if user selects 201405 month, then I need a column displaying me revenue by using 201405YTD member(we have Month YTD members built in) and hence Im trying to build MUN.

As highlighted earlier I used ..#'[Cube].[Time Period].[Time Period]->:[TM].[Time Period].[Time Period].[@MEMBER].[Month YTD^'+prompt ('P_Month','memberuniquename','[Cube].[Time Period].[Time Period]->:[TM].[Time Priod].[Time Period].[@MEMBER].[2015^20153^201507]')+' YTD]'#

but this gives parsing error on or before 146 of ********.................

What do you mean by "I need to get the YTD member"? Is your requirement to prompt for a month and see aggregated measures for all the months up to and including that one within the context of the year?

If so, you could simply use a periodsToDate() function to get these month members - then aggregate your measure values within these members.

I explained why your current approach will not work already. You need to consider alternatives.

MF.

[riteshsinghrathod]

Hi MF,

Thanks for your guidance.

Since the levels are missing in our cubes, we were unable to use the predefind functions of cognos R.S. and hence resolved to MUN's

But however level creation is in progress so that should resolve the issue.

[MFGF]

Since the levels are missing in our cubes, we were unable to use the predefind functions of cognos R.S. and hence resolved to MUN's

Not true. There are actually levels there even through they are not named and visible within the package tree. If you know how to, you can use them in dimensional expressions.

I'm assuming you think you can't use the periodsToDate() function because it needs a level? Although you can't drag a level in to the expression (because it's not visible in the package tree) you can still reference it using a level() function based on an ancestor of the member you are prompting for:

periodsToDate(level(ancestor([Your time hierarchy] -> ?P_Month?, 2)), [Your time hierarchy] -> ?P_Month?)

I think I posted this before in another of your threads where you reported a coercion error, but then didn't specify the exact error and respond any further?

You can test this by creating a new crosstab report. Drag a query calculation into the rows of the crosstab, set the type to be "other", and use this expression. Make sure you drag in the hierarchy object - it should appear with a three-part name ie [package].[dimension].[hierarchy] in both places in your expression. Tell us what you find.

MF.

[MDXpressor]

I'd agree with MF on this one. 
I think adding an alternate hierarchy to the cube is overkill in this case.  I think what you are asking for is YTD calculation.  If there is something more, let us know what the end goal is, as well as the approach you've taken and (presumably) struggled with.  There are many ways to accomplish objectives in MDX.  Simplify, don't complicate.

When prompting a user, you are basically asking them to set a specific member as their starting position in the hierarchy.  Always try to make this one member, rather than a set, or a custom member, if you can.

So, to get a single month (I'd use your full time dimension so that users can view other time periods with the same report).  Most of my cubes are historical in nature. I can use the predictable nature of a time dimension to get me to the current month, quarter, or year with simple OLAP calls, BUT, my cube needs to have the current month as the last member of the time dimension for this to work.  Watch for this in powercubes, they will quite often build a full time dimension beyond the current month.  In those cases, this simple tactic to find current month wont work.  In cases where you need to carry time beyond the 'current month', Try using defaultMember during your cube build.

Current month:   
lastChild( lastChild ( lastChild ( [Top Node MUN] ) ) )

or as a result in sudo English
(latest month ( latest quarter ( latest year) ) )

Before I get torched, this is certainly not the only way to get your latest month, but it is a way which avoids prompting at all.  From here a user can drill up/down right in the data container.

Point being you want a single node, be it a year, quarter or month, as long as the result is a single member.
You also want to know at what level are you going to set the bounds under which the statement will include members.  i.e. Is this a YTD, QTD, MTD?  Should it be dynamic as the user drills?  i.e. when at the month level should it be QTD, and at the Quarter level it should be YTD?  These are all important clues to help solving your issue.  But let's see if we can set you on the right path.

First, follow MF's advice, use periodsToDate calcl, you don't need to solve this in the cube, unless all else fails.

Periods to Date returns a SET:
PeriodsToDate( [ Level_Expression [ ,Member_Expression ] ] )

Months in a year:
PeriodsToDate([Year Level], [single member at the month level]) will give you all the months up to and including the single member which are peers under the same parent at [Year Level].

Quarters in a year:
PeriodsToDate([Year Level], [single member at the quarter level]) will give you all the quarters up to and including the single member which are peers under the same ancestor at [Year Level].

Months in a quarter:
PeriodsToDate([Year Level], [single member at the month level])

Dynamic months in quarters or quarters in years so a user can drill up and down:
PeriodsToDate(parent[single member at the month level], [single member at the month level])