Combination of prompts

[mahieusteph]

Hi all,

I am working on an Essbase cube and I want to combinate 2 prompts in order to create a data set.

My dimension is Scenario and the members are named this way :
ACTUALRATE1
ACTUALRATE2
BUDGETRATE1
BUDGETRATE2
FORECASTRATE1
FORECASTRATE2

To select my members, I have 2 prompts with static choices :
prompt 1 contains   ACTUAL, BUDGET and FORECAST
prompt 2 contains   RATE1 and RATE2

Prompt1 is a multiselect and prompt2 is not multi-select.

When I choose
prompt 1 = ACTUAL, FORECAST
and
prompt 2 = RATE2
I want to have in my data set the members
ACTUALRATE2 and
FORECASTRATE2

Do you know if it is possible ?

Thanks,

Stephanie

[CognosPaul]

There are a few ways to do this. Some ways more elegant than others, but it will all amount to the same thing.

Generally for something like this I'd like to see the structure of the MUN of at least two of the members. That way you could build the MUNs directly from the prompts. Since you have a finite number of possibilities, we can do it the quick and stupid easy way.

First, drag in all of the members into your query. Every member should have an entry in the list. This way you can reference the names of the members instead. The use values should be ACTUAL, BUDGET, FORECAST, RATE1, RATE2.

The final expression will need to use array functions. Try something like this:

Code Select Expand

set(
#
'['+
    join(prompt('Prompt2','token')+'],['
      split(';'
        , promptmany('Prompt1','token')
      )
    )
    + prompt('Prompt2','token')
  +']'
#
)

The Prompt1 values will split into an array:

ACTUAL
FORECAST


The join will join them using the value from Prompt2:
ACTUALRATE2],[FORECAST

Prompt2 will be concatenated onto that fragment:
ACTUALRATE2],[FORECASTRATE2

And then wrap the fragment in square brackets:
[ACTUALRATE2],[FORECASTRATE2]

Wrapping that in a set function will get you the results you want.

Unfortunately there doesn't seem to be any way of doing a Cartesian product on arrays in macros. This means that you are limited to having one prompt many and one or more single prompts.

[mahieusteph]

Hi PaulM,

Thanks for your answer. This way it is working.

Here is the final code for the function :

set(
#
'[DASHB - prompt].[Scenario].[Scenario]->:[DO].[Scenario].[REPORTING_SCENARIOS].['+
    join(prompt('chaine2','token')+'],[',
      split(';'
        , promptmany('chaine1','token')
      )
    )
    + prompt('chaine2','token')
  +']'
#
)

I just have one more question. Since we added all the scenarios in the request, I assume that if we create another combination of scenario we will have to change both prompts and query. Is there a way to get the same result without dragging all the members in the query ?

Stephanie

[CognosPaul]

Yes, you can construct the member unique names directly using the prompts with a single tweak to the below code:

Code Select Expand


set(
#
'[DASHB - prompt].[Scenario].[Scenario]->:[DO].[Scenario].[REPORTING_SCENARIOS].['+
    join(prompt('chaine2','token')+'],[DASHB - prompt].[Scenario].[Scenario]->:[DO].[Scenario].[REPORTING_SCENARIOS].[',
      split(';'
        , promptmany('chaine1','token')
      )
    )
    + prompt('chaine2','token')
  +']'
#
)

Now you can use any scenario from the list without having to drag them in.

[mahieusteph]

Many thanks PaulM, this is exactly what I needed