Hello Folks,
I have a report which is built on a DMR model, this report is getting called from a third application portal. Now this report contains some prompts which is required to be passed by the third party apps from where this report is getting called from. The third party app is forming the prompt values and passing it to the report, however since i have used slicers the prompt would except member unique names instead of the member captions.
So what i did is I took a data item in my query and wrote the following code:
set([Dimensional Layer].[Location].[Location].[Country]->[all].[b]#sb(prompt('Param_Country','token'))#)[/b]
And I put this dataitem into the slicer, so this was working just perfect.
But I have one more prompt which is of Reporting period.
Now my hierarchy for Reporting Period is
Organization>Reporting
Now the data in the reporting batch table is something like this.
Org Id|Reporting period
10 |2012-2013
10 | 2013-2014
11 |2012-2013
11 |2013-2014.
Now what is getting passed in the parameter by the calling app is '2012-2013'
Now since my MUN could be
[Dimensional Layer].[Reporting Batch].[Reporting Batch].[Reporting Period]->[all].[10].[2012-2013]
[Dimensional Layer].[Reporting Batch].[Reporting Batch].[Reporting Period]->[all].[11].[2012-2013]
Now here I cannot decide which MUN should be hard coded (Every user will have access to different set of organizaiton and it can be more than 1) so I am not able to prepare a set here and slice the data.
It can be done very easily using a detail filter but I dont want to use a detail filter since it is a DMR model.
I am running out of ideas, can anyone please let me know what could be the best solution to accomplish this?
Regards
Ravi
[/code]
How are you getting the users' org?
If it's coming from a macro function, you could do something like:
set(#
'[Dimensional Layer].[Reporting Batch].[Reporting Batch].[Reporting Period]->[all].['
+ join('].[' + prompt('ReportingPeriod','token')+']',[Dimensional Layer].[Reporting Batch].[Reporting Batch].[Reporting Period]->[all].['
,split(',',$getOrgs))
+ '].[' + prompt('ReportingPeriod','token')+']'#)
so if $getOrgs returns 10,11, and ReportingPeriod returns 2012-2013 you should get
[Dimensional Layer].[Reporting Batch].[Reporting Batch].[Reporting Period]->[all].[10].[2012-2013],[Dimensional Layer].[Reporting Batch].[Reporting Batch].[Reporting Period]->[all].[11].[2012-2013]
Quote from: CognosPaul on 15 Jan 2014 09:52:08 AM
How are you getting the users' org?
If it's coming from a macro function, you could do something like:
set(#
'[Dimensional Layer].[Reporting Batch].[Reporting Batch].[Reporting Period]->[all].['
+ join('].[' + prompt('ReportingPeriod','token')+']',[Dimensional Layer].[Reporting Batch].[Reporting Batch].[Reporting Period]->[all].['
,split(',',$getOrgs))
+ '].[' + prompt('ReportingPeriod','token')+']'#)
so if $getOrgs returns 10,11, and ReportingPeriod returns 2012-2013 you should get
[Dimensional Layer].[Reporting Batch].[Reporting Batch].[Reporting Period]->[all].[10].[2012-2013],[Dimensional Layer].[Reporting Batch].[Reporting Batch].[Reporting Period]->[all].[11].[2012-2013]
Hi Paul,
Thanks a lot for the suggestion, Well I am not getting the Org's as the parameter, I do have a drop down of Org's but by default it should show data for all Org's, that is where the pain begins.
Regards
Ravi.
So a user will only select one org at a time? In what format will it be, a mun?
I can think of a few ways of getting the reporting period for each batch. Try the following:
filter([Dimensional Layer].[Reporting Batch].[Reporting Batch].[Reporting Period],roleValue('_businessKey',currentMember([Dimensional Layer].[Reporting Batch].[Reporting Batch])) = #prompt('ReportingPeriod','string')#)
Assuming ReportingPeriod is returned as a string like '2012-2013'.