Reporting off a Cube

[ComplexQry]

My requirement for this report is to allow the user to select any member from a tree prompt and then to display all the levels below that member and its members.  For example, we have a dimension with 5 levels.  So the user selects a member from the 2nd level.  The Report needs to show Level 3, Level 4, and Level 5.  I am thinking that utilizing the descendants function will satisfy my needs for this requirement, but I have had no luck.  I appreciate your help in advance.

Thanks

[MDXpressor]

The descendants function will only allow you to specify the number of levels from the ROOT level, not from a point in the heirarchy, unfortunately. 

Sadly, I'm not familiar with any functions or set of functions that will provide you with this data.

[josepherwin]

I'm just wondering about your report requirement....

From my understanding of your explanation, it appears that the business would just like to analyse the data instead of a report....

Wouldnt it be better to provide them with Analysis Studio where they can slice and dice the data easily?

[bonniehsueh]

I am also fairly new to MDX functions, but I would suggest looking through the a document on Cognos KB named: Cognos 8 dimensional Functions. It's a consolidated list of MDX functions and descriptions. If not already, I would also suggest searching on MDX in the KB. I've noticed that there seems to be more documentation on this topic than there was last October.

[josepherwin]

I havent really thought about this, but it may give you some idea on how to achieve it.

Try using an IF statement on your data item:

IF User selection is a member of Hierarchy 1
Then Display hierarchy 2, 3, 4, 5
ELSE IF User selection is a member of Hierarchy 2
Then display hierarchy 3, 4, 5

and so on and so forth....

Not sure whether it's possible or not, but logically it's doable  :-\

Another method is, I've just tried to create a simple crosstab report where each level of hierarchies are indented and it gave me everything from the highest to the lowest hierarchies... the problem here is only the user input that should filter what's being displayed in the report...

[Robl]

Include all levels of the hierarchy on the report.
When the user selects from the tree prompt it will filter the data so the first few columns only have a single row in them,
Assuming the user could select lvl1 then the report would need to be large enough to accommodate this many columns anyway.

You could sell this as a good thing because all the reports will be consistent with the columns displaying the levels in the same order regardless of what is selected so when comparing two reports with different level of filters they still match up.
Especially useful if/when the user drops the data to excel.

If you really don’t want to see the columns above the level the user selected you might be able to extract the level of the prompt selection and the use a conditional format to hide any column with a higher level.

[ComplexQry]

Include all levels of the hierarchy on the report.
When the user selects from the tree prompt it will filter the data so the first few columns only have a single row in them,
Assuming the user could select lvl1 then the report would need to be large enough to accommodate this many columns anyway.

You could sell this as a good thing because all the reports will be consistent with the columns displaying the levels in the same order regardless of what is selected so when comparing two reports with different level of filters they still match up.
Especially useful if/when the user drops the data to excel.

If you really don’t want to see the columns above the level the user selected you might be able to extract the level of the prompt selection and the use a conditional format to hide any column with a higher level.

Thanks for the help, by the way.  Could you provide some syntax for this?

[Robl]

Take a look at the funtion available in cognos.. I *think* there one called 'level' that returns the level of the hierarchy.. but I might be wrong.