If you are unable to create a new account, please email support@bspsoftware.com

 

Creating a parameter with one value to filter multiple values on a dimension

Started by jroberts, 22 Feb 2017 10:45:43 AM

Previous topic - Next topic

jroberts

I have a promp list (multiple selection) with a few dimensional values: A, B and C. I have a filter with an IN clause with the prompt parameter and it works fine to filter the dimension. If I select A and B the filter works.
I want to create another list value with A+B, so if the user select that it will filter by both elements. How can I do that?
So the prompt list static choices will look like this:
Use - Display
A - A
B - B
C - C
A,B - A+B

My question is ... how can I format the USE value of A+B in order to work on the IN clause filter on the master query?

CognosPaul

If this is a dimensional query, you really should avoid using detail filters. My experience with detail filters has been negative as you're never entirely certain where the filter will be applied, and the resultant MDX will almost certainly not be what is intended.

In this case what is happening is the filter you're using is based on a prompt alias, which is determining the correct datatype from the context. Anything other than a mun (memberuniquename) will be rejected with an error.

Try the following:
#
prompt(
    'param'
  , 'token'
  , '[Cube].[Dim].[Hier].[All]->:[RO].[All]'
)
#

In your prompt object, use:

UseDisplay
[Cube].[Dim].[Hier].[All]->:[RO].[2016]2016
[Cube].[Dim].[Hier].[All]->:[RO].[2017]2017
set([Cube].[Dim].[Hier].[All]->:[RO].[2016],[Cube].[Dim].[Hier].[All]->:[RO].[2017])2016+2017

Token instructs the parameter to expect an expression fragment, with no validation added. The third option in the project object is a set expression, which would not validate as a mun.