Tuesday, August 17, 2010

Single Select Tree Prompt on a Relational Model

You can base Tree prompts not just on Dimensional Model but on relational Model as well. This involves using macros and decoding MUNs. This has been covered in article on IBM's support site but I am covering the same here for the benefit of those who use this blog as a one-stop-site for all Cognos needs.

The below solution works for single select Tree Prompt. I am working on multi-select Tree Prompts and would update this article once I have the solution.

Requirement: Create a Date Dimension Tree Prompt based on a Relational Date Dimension Table and filter the report to retrieve data for the selected dates.


Step 1: In the FM Model, create a stand alone Date Regular Dimension based on the existing Date Dimension query subject in the Presentation Layer View.

This Dimension would be used for creating the Tree Prompt.

Step 2: In the report create a tree prompt based on Date Dimension.

Step 3: Create a filter as below:

[Date] = #csv(substitute(']','',grep(']',split('].', grep('all',split('->',prompt('dte','memberuniquename','[Presentation Layer].[Date Dimension].[YEAR].[DATE]->[all].[2008].[Q1 2008].[Jan 2008].
[Wk 1].[2008-01-01]')))))))#

The above filter works as explained:

  • First the MUN for the selected Tree Prompt value is retrieved.
    In the above Example: [Presentation Layer].[Date Dimension].[YEAR].[DATE]->[all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01]

  • Then we split the retrieved MUN into 2 parts based on the symbol "->".
    In the above Example: The MUN is split into [Presentation Layer].[Date Dimension].[YEAR].[DATE], [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01]

  • We then select the part containing "all".
    In the above Example: [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01] is selected.

  • Then split the part again into multiple parts based on the symbol "].[".
    In the above Example: [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01] is split into [all, 2008, Q1 2008, Jan 2008, Wk 1, 2008-01-01]

  • Retrieve the part containing "]".
    In the above Example: 2008-01-01] is selected.

  • Replace "]" with "".
    In the above Example: 2008-01-01] is converted to 2008-01-01.

Thus [Date] is compared with 2008-01-01 and the filter works as expected.


Sehgal's said...

nice write-up... good going....

Anonymous said...

Hi Zephyr,

Great solution, not too sure if u already have multi-select Tree Prompts solution, would really appreciate if you can share with me how it works, Thanks a lot :)

email: siochengcheong@hotmail.com

Zephyr said...

I am sorry, didn't get a chance to work on this. You will need to play around with the the expression. Will try to post something on this but don't know when.

Arik said...

It seems like a bit of a tricky way.
You can also achieve the same goal without macros - only by using the report studio's tree prompt's wizard.
Start by building a DMR query subject in FM (create a regular dimension in framework manager and populate it with the query items you want to filter in the tree prompt, i.e. 2 levels: [Product Type], [Product Name]).
Then add a tree prompt to the report. In the tree prompt's query put the highest hierarchy of the regular dimension you created in FM as the query's data item. In the query u want to filter on using the tree prompt (query1 i.e.) add a filter like this:
[Product Type] in ?Products?
Then just play with the tree prompt's properties in order to achieve multi-selection and seeing all of the levels that you populated your regular dimension with in the FM.

Hope it helps

Zephyr said...

Thanks for the info Arik.

Zephyr said...

I have a question here, did you see any disadvantages or draw-backs in using a hybrid model?

Cause my understanding of DMR is that Cognos builds a virtual cube based on the dimensional queries before servicing the report output.I am just curious here as to how Cognos deals with DMR and Relational sections in the report.

I am still unsure of mixing DMR and relational querying. Hope you can throw more light on what happens behind the scenes.

Arik said...


When using the tree prompt, the filter in the original query (query1 i.e.) has to be on the regular dimension you created in the FM which you also use as the prompt's query data item. otherwise it won't work.
In addition a small clarification:
the data item used in the tree prompt's query is set to rootMember().

Regarding the hybrid model - i'm experiencing with it myself so i don't have any important conclusions yet.

Anonymous said...

Here is code for multi select tree prompt on relational data. Add below code relational query filter.
[Relational].[Product Line] in
(#csv(substitute(']','',grep(']',split(';',split(')',split('[',split('].', grep('all',split('->',promptmany('MyPrompt',
'memberuniquename','Root Member','set(', '', ')'))))))))))#)

Hope this helps.

Anonymous said...


The multi-select code only works when selecting a single value in the multi-select prompt. As soon as more than one value is selected and error is encountered. Looks like a problem from the comma after the first value in the string.

Are you able to help?

Anonymous said...

did anyone find how to make it work on multi select, the code breaks for more than one value..

Neerav Singh said...

I was able to make it work using "join", wherever the value of prompt is being "split" into 2 values....

#csv(array(join(',',substitute(']','',grep(']',split('||',join('||',grep('all',split('->',join('||',split(')',join('||',grep(']',substitute(';','||',substitute('\[','||',substitute(']\.','||',split('].',promptmany('promptname', 'memberuniquename','rootMembers([rootmembers])', 'set(', '', ')'))))))))))))))))))#

It would work , you might get 1 redundant value of a level name, but it does not effect my logic so left it..

chowdary123 said...

Neerav, still its not working, its working for single select, but when selected multiple values, no data is coming.

here is the calculated column used for the filter

select distinct a.project_ id, a.project_FA_rate_eff_date, a.project_FA_rate_type
from DW_FIN.dw_project_activity_award_dim

There are 5 LEVELS USED IN THE REPORT, and the relational filter is defined as
LEVEL1 IN (calculated column) or LEVEL2 in (calculated column) or
LEVEL3 in (calculated column) or so on....

pls let me know how to get it done.

chowdary123 said...

Neerav, sorry correction for the calculated column

#csv(array(join(',',substitute(']','',grep(']',split('||',join('||',grep('all',split('->',join('||',split(')',join('||',grep(']',substitute(';','||',substitute('\[','||',substitute(']\.','||',split('].',promptmany('Parameter1', 'memberuniquename','rootMembers([Secured Dimensions].[PRMPT_DEPARTMENT_TREE_DIM].[Department Tree])', 'EMPTYset(', '', ')'))))))))))))))))))#

Anonymous said...

Hi chowdary123,

it do not work for multi selection (only single).

have u test it?

chowdary123 said...

yes its working only for single select, its not working for multi select, thats what i was mentioning.
Pls let me know if any one knows how to make it work for multi-select from different levels

Anonymous said...


hire comes the changes for multi-selection:

#csv(array('"'+join('";"',substitute(']','',grep(']',split('||',join('||',grep('all',split('->',join('||',split(')',join('||',grep(']',substitute(';','||',substitute('\[','||',substitute(']\.','||',split('].',promptmany('Parameter1', 'memberuniquename','rootMembers(Secured Dimensions].[PRMPT_DEPARTMENT_TREE_DIM].[Department Tree])', 'EMPTYset(', '', ')'))))))))))))))))+'"'))#

and for the KEYS:

'"'+TRIM(CHAR([LEVEL1_ id]))+'"'

'"'+TRIM(CHAR([LEVEL2_ id]))+'"'

and the relational filter:

[calculated column] contains [LEVEL1]
[calculated column] contains [LEVEL2]
[calculated column] contains [LEVEL3]