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.
Solution:
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.
Posts
17 comments:
nice write-up... good going....
Thanks!!
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
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.
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
Arik
Thanks for the info Arik.
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.
P.S.
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.
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.
Thanks,
-Rajgopal
Hi,
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?
did anyone find how to make it work on multi select, the code breaks for more than one value..
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..
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.
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(', '', ')'))))))))))))))))))#
Hi chowdary123,
it do not work for multi selection (only single).
have u test it?
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
Hi,
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:
LEVEL1:
'"'+TRIM(CHAR([LEVEL1_ id]))+'"'
LEVEL2:
'"'+TRIM(CHAR([LEVEL2_ id]))+'"'
and the relational filter:
[calculated column] contains [LEVEL1]
or
[calculated column] contains [LEVEL2]
or
[calculated column] contains [LEVEL3]
or
....
The above solution did not work for multi selection. Does anyone have a solution?
Post a Comment