Report Studio: OPTIONAL MULTISELECT prompt against dimensional source

[martijnheijde50]

All,

I hope someone can help me out here! I am using Report Studio against a dimensional source (An SSAS cube published through a FM package). Things are relatively new to me since I always have been using Report Studio against a relational source. So maybe I am overlooking something simple, but I spent my fair amount of time on this already.  ;)

I created two OPTIONAL MULTISELECT prompts. (One on a YEAR level and one one some kind of CATEGORY level. Levels are in two different dimensions) So the selected values (sets) are stored in the two parameters. That works fine. But my question is now: How do I use the parameters to filter the data in the crosstab? (the data I prompt for is NOT in the rows or columns of the crosstab).

I looked at various options, but didn't really find a proper solution:

1) Details filters => not recommended on a dimensional source
2) Slicer => Not sure if this is appropriate when filtering on more than one level. And the other thing is that it looks lik this makes the prompts 'required' instead of 'optional'. (And a slicer does only effects the measures, it doesn't touch the levels. But this might not be a problem as long as I don't have the members in the rows or columns of the crosstab). Also not 100% sure if a slicer will work with sets. (But even then: I would still loose the 'optional' part in my prompt)
3) Using the TUPLE function => No option, since you can not use a set inside a tuple. (Remember: I want multiselect prompts, so the paramaters will (potentially) store sets)
4) Dragging the filtered sets into the edges of the crosstab => As mentioned: I don't want to see those sets in the report. I only want to filter on them.

Any thoughts? Remember that I want to keep the prompts OPTIONAL and MULTISELECT....

Thanks in advance!

[CognosPaul]

A slicer is exactly what you need. The MDX standard technically states you need to slice by a single member, but it is very easy to create that member from a set.

Your statement that you would lose the optional part is incorrect. I assume you're using the [Cube].[Dim].[Hier]->?Parameter? syntax. It is true that using that syntax would force it to be required. The prompt in that syntax is called a "Prompt Alias". Cognos will analyze the query before creating the MDX and generate the prompt as needed. A better way would be to use a prompt macro.

Code Select Expand

#
prompt/many(
  'Parameter Name'
  , 'DataType'
  , 'Default Value'
  , 'Before Text'
  , 'Source'
  , 'After Text'
)
#

The prompt macro function allows you to specify a default value, in case the user opts not to select any. The default value can be almost anything, including the root or default member of your hierarchy. The before and after text allows you to add expression fragments to whatever the user selects. While the source gives Cognos the context needed to generate the prompt (you can ignore that if you have a prompt page).

In your case, you would need something like this:

Code Select Expand


#
promptmany(
  'Year'
  , 'mun'
  , 'defaultMember ([sales_and_marketing].[Time].[Time])'
  , 'set('
  , '[sales_and_marketing].[Time].[Time]'
  , ')'
)
#

In that example, if the user does not select a year, Cognos will automatically slice by the defaultMember of the hierarchy, which is usually the rootMember. If the user does select one or more values, Cognos will wrap them in the set function, and the query will be sliced with: set([sales_and_marketing].[Time].[Time].[Year]->:[PC].[@MEMBER].[20120101-20121231];[sales_and_marketing].[Time].[Time].[Year]->:[PC].[@MEMBER].[20130101-20131231])

The MDX engine will automatically attempt to convert a slicer set into a calculated member. Sometimes it doesn't work, or it works in a way that is undesirable. For example, you may want to find the average for the selected years instead of the total. In that case you can force it by using aggregate summary functions and the member function.

Code Select Expand

#
promptmany(
  'Year'
  , 'mun'
  , 'defaultMember ([sales_and_marketing].[Time].[Time])'
  , 'member(average(currentMeasure within set set('
  , '[sales_and_marketing].[Time].[Time]'
  , '),'+sq('calcYearCode')+','+sq('calc year caption')+',[sales_and_marketing].[Time].[Time])'
)
#


When run with selected values, the resulting expression might look like
member(average(currentMeasure within set set([sales_and_marketing].[Time].[Time].[Year]->:[PC].[@MEMBER].[20120101-20121231];[sales_and_marketing].[Time].[Time].[Year]->:[PC].[@MEMBER].[20130101-20131231]),'calcyear','calc year caption',[sales_and_marketing].[Time].[Time])

which is a valid expression in a slicer.

[martijnheijde50]

Thanks a lot. This definitely pushed me in the right direction!