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..
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.
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...
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.