Passing Tree Prompt Parameter Value to Relational Query

[MB_DIAA]

Hello,

I could use some help trying to pass a tree prompt parameter value to a relational query all in the same report.

The report was originally built using an OLAP data source. At a high level, the OLAP data source contains departments and their annual expenses. One of the tree prompts on the report allows the end users to select multiple departments by navigating through the "Department" dimension in the OLAP data source.

What we would like to do is take the department the end user selects on the tree prompt, and pass it through to another query subject which will hit a db2 table and pull back a list of all the employees in that department. This would also need to support multi select so if an end user selects multiple departments we would want both department ID's passed through to the relational query subject.

For example:

User runs report and selects the following from the departments tree prompt:

Departments -> HQ Office -> 1001 - Finance
and
Departments -> HQ Office -> 1002 - Sales

as parameter "Department".

This would generate a report which displays the yearly expenses for both the Finance & Sales departments.

What we are looking to add is the following:

A basic list sourced via a relational query hitting our db2 table which stores the department and employees in each department.

In the relational query we would want the filter to be "RELATIONAL_TABLE.DEPARTMENT in ('1001 - Finance' , '1002 - Sales') based on the selections made on the tree prompt.

Does anyone have any experience with this?

[CognosPaul]

I've done something similar.

Can you post an example of the MUN generated from the prompt? You can use macro functions to strip the mun to generate the filter, but the way it's written depends heavily on how the dimension is defined.

[MB_DIAA]

I've done something similar.

Can you post an example of the MUN generated from the prompt? You can use macro functions to strip the mun to generate the filter, but the way it's written depends heavily on how the dimension is defined.

This is the MUN of the lowest level in our dimension:

[GENERAL_EXPENSES].[Entities].[Entities]->:[DO].[Entities].[Overall].[Departmental View].[HO].[EXECUTIVE_HO].[Finance Summary].[DIAA Summary].[Non-Regional Summary].[1001223]

This is an essbase cube and the structure is less than ideal to say the least. Our relational data source has that same department ID (1001223) as a column.

[CognosPaul]

Out of curiosity, what do you expect to happen if a user selects a higher level? Do you want it to filter by that?

For example, if a user selected:
United States
Canada/Ontario
England/West Midlands/Birmingham

I would expect the filter to be:
    [country] = 'United States'
or [state] = 'Ontario'
or [city] = 'Birmingham'

Is this scenario likely?

If the user only selects members in the department level, you could do something like:

Code Select Expand


[DepartmentID] in (#
join(',',
substitute('\]','',
substitute(
  '\[GENERAL_EXPENSES\]\.\[Entities\]\.\[Entities\]->:\[DO\]\.\[Entities\]\.\[Overall\]\.\[Departmental View\]\.\[HO\]\.\[EXECUTIVE_HO\]\.\[Finance Summary\]\.\[DIAA Summary\]\.\[Non-Regional Summary\]\.\['
  ,''
  ,split(';',
  promptmany('department','mun')
)))
)
#)


[Robl]

The easiest way is to set the value passed in the parameter to be the "bkey" of the dimension.

Assuming the dim has been built in the way I would expect this will give a comma separated set of the departments.
So your filter could just be department = ?department?


To test,
create a new report.
Add a text box prompt called "TEST".
Add a layout calculation with "Paramvalue('TEST')
Add a layout calculation with "ParamDisplayvalue('TEST')
Set your original report to drill through to this report.
On the drill through setting where you map the attributee change the right most setting to Bkey.

If Bkey doesn't work then have a play with the other options, caption, description etc; if the cube has been build at all well one of them will give you the value you need.

This should also work if someone selects a higher level in the tree as I think it returns the bkey of the lowest level of the dim.
But test it, as I may be wrong.