Drill Down with Tree Prompt

[matthew]

Hi,

I have a question about drilling in reports.

In my framework I have a drill hierarchy of a company's internal organisation, simple overview:

- operation
- division
- sub division
- teams

I use this drill in a crosstab to view some financial figures. So the row of the crosstab contains the drill, the columns contain the month and the measure is revenue.

I want people to be able to select a certain member in the drill hierarchy (for example a certain sub division) so i've added a tree prompt, which allows users to select a member in the hierarchy. But here comes the problem:

When i select a certain member in the prompt (so lets say i choose a sub division) and I run the report, in the crosstab it also shows me all the members above that member, like this:




But i don't want it to show the members above it, i just want it to show the subdivision. How can i fix this?

[MFGF]

Hi Matthew,

You didn't say how you are limiting the members in the rows based on the selection you make in the Tree prompt.  You also didn't specify whether you want the users to be able to select more than one member?

The best way to do this is to replace what is in your rows currently with a query calculation, using the expression:

[Your namespace].[Your dimension].[Your hierarchy] -> ?Your parameter?

or, alternatively

set([Your namespace].[Your dimension].[Your hierarchy] -> ?Your parameter?)

The first option will allow a single member to be selected from the tree prompt, and the second will allow multiple members to be selected.  In both cases, your row headings should end up as only the selected member(s) from the prompt, and not their ancestors too.

Regards,

MF.

[matthew]

Hi MFGF,

Thank you very much! The set() expression works! :D. Except, now the following problem is raised:

People are able to select whether they want to see organisation 1 or organisation 2, which are both different hierarchy's and different tree prompts. So they get to see a different tree prompt, depending on a choice they make in a value prompt above that("do you want to select organisation 1 or 2", depending on that choice, tree prompt 1 or 2 is shown). So in the report, they should see the organisation depending on the choice they've made. I've already tried to change my query calculation to something like the following (names are simplified):

if (SelectOrgan1OrOrgan2 = '1') then (set([namespace].[dimension].[hierarchy1] -> ?Organisation1?)) else (set([Your namespace].[Your dimension].[Your hierarchy2] -> ?Organisation2?)).

in this example, SelectOrgan1OrOrgan2 is the parameter of the value prompt which allows people to see one of the tree prompts. But this doesn't work, because cognos doesn't allow two different hierarchy's in one query calculation. Any thoughts?

Thanks in advance.

[MFGF]

Hi,

Off the top of my head, you could try stacking two separate query calculations below each other in the rows - one for each hierarchy - and conditionally hide one or the other based on your organisation prompt. Would this work?

Regards,

MF.