Prompts Using the Same Hierarchy

[xyz]

Hi Gurus,

My datasource is cube, which is built in SSAS, I have to develop a report in Cognos Report studio 10.1, using DMR functions. I have requirement like I have two prompts, which are using the same hierarchy, say suppose hierarchy name is Chart of Accounts dimension. For prompt1 if am choosing Level 1 member then prompt 2 should always show one level less than Level 1 member, means prompt 2 can show all the members from level2, level3 etc. Please find the attachment of the hierarchy.

Example: Say suppose, if in prompt1, if I have chosen 'Profit' whihc is a level 1 member then prompt2 should display all the members from level 2 till all the leaf members. Then prompt2 should display like 'Sales', 'Costs', 'Fixed', 'Variable'.

[bdbits]

If I follow you, what you are looking for is a set of cascading value prompts. See the General>Cascade Source property for the prompt control.

But why not just use a tree prompt? It's much simpler for you and I think it is a better GUI for the user.

[xyz]

Thank you reply bdbits,

Yes, I have used cascading prompt. But I am not getting the result what I am expecting.

Actual my requirement is say suppose I have chosen US in prompt1, which is a level 2 member then I have to show all the members below that level in prompt2. Under US, I have CT and MA which are level 3 members and under CT, have XY and ZX. Similarly under MA, I have QA and PY. Then in prompt to I have to show CT, MA, XY, ZX, QA and PY.

Basically I have to show all the members in prompt2, which are less than prompt1 selection.

Thanks & Regards,
Xyz

[Lynn]

Sounds like you need to use a descendants function. Have you tried that?

[MMcBride]

Lynn has it  :)

I too am forced to deal with PeopleSoft tree structures...  But I force Balanced Hierarchies at the ETL level unlike your Ragged Hierarchies. (Ragged is how PeopleSoft handles these tree's Transformer just doesn't like them so we forced Balanced...)

Anyway back to the problem - I get distracted easily...

I have 2 Prompts
Prompt 1:
Prompt_01 - (Chart of Accounts) In your data there is only 1 value but I tested with my Data and I have 5 different values at this level.

I then built a second prompt using Prompt_01 as a cascade source so any data items I see are tied to the first prompt.
The data item I am using for this Second prompt is:
descendants ([Cube].[Segment Dim].[Segment Dim].[Segment Level 2],2,  self after)

My Cube is a transformer cube so you may need to tweak the syntax a little
But this gave me what you are looking for.
All level 2's all Level 3's within the Context of Level 1

However there is another problem that I didn't solve...
How will your prompt filter work?

Since you don't know if your users will be selecting items from level 2 or level 3 you will need to change the prompt filter to be dynamic - I attempted to use the same logic you can apply to Tree Filters where you use the "All Values" level but that didn't seem to work.
There may be an easy solution to this but I couldn't get it to work...

So While the Descendant logic made my prompts work correctly I couldn't get that prompt to work with my query...

[Lynn]

I would guess a prompt macro needs to come into the mix here. If you search for pretty much anything Paul has posted on prompt macros with dimensional sources you can probably get some ideas on what might work.

I'm a little busy at the moment...will look when I can.

[xyz]

Thank you very much MMcBride and Lynn.

Actually my hierarchy is a organisation hierarchy, which is a unbalanced and ragged hierarchy.
I gave a example like if in prompt1 level2 member is selected then in prompt2 I have to display level3 and level4 members only, if my hierarchy has further more levels down those members also has to be displayed in prompt2.

My expression is like below:

descendants(set(#promptmany('Parameter1','memberuniquename')#), 7, self after)

Assuming I have seven levels in my organisation hierarchy, I guess above expression will give all the members in prompt2. Please correct me, if my expression is wrong.

[CognosPaul]

Your expression will return all descendants, but this may not be exactly what you need. Are you looking to return only the leaf members, or the parents as well? You also haven't address what will happen if the user declines to select a value in the second prompt.

When the user selects a value from Prompt 1, the page will refresh and prompt 2 will be rendered with the descendants of prompt 1. The expression you wrote handles that nicely. Now the user can opt to select a value from prompt 2, or simply run the report.

Do you want the final set to be a combination of the two prompts, or only the members from the last prompt selected? Either scenario is possible

combination:
#promptmany('Prompt2','mun',promptmany('Prompt1','mun','[Default Member]','set(','',')'),'set(' + promptmany('Prompt1','mun','[Default Member]')+',','',')')#

If Prompt2 is not selected, it will default to Prompt1. If Prompt2 is selected, it will append the selected MUNs from Prompt1 before the selected MUNs from Prompt2. If neither Prompt1 nor Prompt2 are selected, the expression will resolve to [Default Member] which could be the All member from the hierarchy, a level, or another data item containing a set expression.

[xyz]

Thanks for the reply CognosPaul, you are awesome.

My requirement is I have to display all the members in prompt2 and prompt2 is mandatory prompt. Whatever member  selected in the prompt2, I am displaying the children of it in the list report as one column. Regardind applying of the filter in the report, I have to think how to handle it.

CognosPaul, can you please help on the topic of counting consecutive week trend posted by my colleague.


Thanks & Regards,
Abdul