Determine the parent of a child within the hierarchy.

[christopher_gaal]

All:

I have a requirement to produce a report that is targeted at one level of our organization structure (Level 4). But the report requirements have 3 sections:

Section 1

Level 3 total sales, one column

Section 2

Level 4 sales of the specificied (via parm) L4 value, as well as columns for all the other L4 values which are under L4's L3 parent

Section 3

Level 5 sales for each L5 value under the specified L4


So the question is how to pass in a specific level 4 value, and determine its parent level 3 value, so I can get a total on Section 1, a series of totals for both Section 2 and 3.

Chris..

[MDXpressor]

You'll probably find a tree prompt most effective for the Level 4 Parameter.

[Level4]:
[Heirarchy].[Level4]->?Level4 Parameter?

Section1 (Levev3):
parent([Level4])

Section3 (Level5):
children([Level4])

You can find a list of all the OLAP functions on the Cognos Proven Practice site.

[christopher_gaal]

MDX

Thanks a lot for this... I'm beginning to understand your logic... to advance this a bit more, the report is for multiple L4 values (hoping to eventually burst it) as this will be a batch/scheduled report, so an interactive tree prompt is not necessary (sorry for not being more clear).

=========================
This is what I've been able to gleen:

Report Section 1:
Query: Metrics-Region Block(L3 in Location Dim)

Filter:
parent([District])

Report Section 2:
Query: Metrics-District Block(L4 in Location Dim)

Filter:
[Presentation View].[Location Hierarchy].[Financial Location].[District]=?District Prompt?
** right now I can insert this individually by UI, and via report parms in the scheduler, but I eventually want the district to drive from those that exist in the hierarchy.

Report Section 3:
Query: Metrics-Store Block(L5 in Location Dim)

Filter:
children([District])


My question is how in Section 1/3 queries, the district/L4 value is known. Because I get the following error:

Referenced data item 'District' is not found in the query 'Metrics - Stores Block'

Chris...

[MDXpressor]

A couple of tweaks:

For the District Calc:
The operator is not '=' it is '->'.  Therefore your syntax in the calc for [District] is as follows:  [Presentation View].[Location Hierarchy].[Financial Location].[District]=?District Prompt?

Of note:  None of these are filters, they are all data items in the query; the '->' operator simply identifies a single member of a heirarchy (in this case a member at level4 of the Location Heirarchy.  While it does also restrict or focus the data, it is still syntax that exists in the data item, not in a filter item.

Therefore the syntax for the the Level3 of location based on it being the parent of the selection of the District parameter is:
Parent([District])

and Level5 is:

Children([District])

Again, just to beat a dead horse, these are not filters, they are calculations.