Passing multiple values in a single Cognos Report Studio value prompt

I found a way to pass multiple value combinations through in a single Cognos Report Studio static choices prompt.  Here I describe the generic approach using just two values passed in each available choice.

In a value prompt (P_Prompt) static DisplayValue and UseValue choices are defined as:

DisplayValue   UseValue

Choice 1:          ~1[Prompt value 1],~2[Prompt value 2]

Choice 2:          ~1[Prompt value 2],~2[Prompt value 3]

The UseValues comprise two Member Unique Name (MUN) tokens separated by a comma and identified with a leading ~1 or ~2 (which are unique character combinations).

The key to the approach is the macro expression #join(',',substitute('~[n]','',grep('~[n]',array(split(',',prompt('P_Prompt','token'))))))# which returns the [n]th token identified in the [n] value choice made.

Here’s a description of how the macro works from the inside out.  The UseValue chosen in response to the prompt is returned as a token. The split macro creates multiple tokens from the single token returned using a comma as the delimiter.  The multiple tokens are placed in an array using the array macro.  The grep macro identifies the single array element containing the ~[n] search item specified and the substitute macro removes the ~[n] identifier from the token.  The join macro converts the array item (now only one remaining) back into a string for use as a MUN.

If I choose the DisplayValue “Choice 1” when running a report, the UseValue “~1[Prompt value 1],~2[Prompt value 2]” will be available to the underlying report.  The macro calculation #join(',',substitute('~1','',grep('~1',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 1] within the report whilst #join(',',substitute('~2','',grep('~2',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 2] within the report.

Similarly, if I choose the DisplayValue “Choice 2” when running a report, the UseValue “~1[Prompt value 2],~2[Prompt value 3]” will be available to the underlying report.  The macro calculation #join(',',substitute('~1','',grep('~1',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 2] within the report whilst #join(',',substitute('~2','',grep('~2',array(split(',',prompt('P_Prompt','token'))))))# will return [Prompt value 3] within the report.

Maybe others have found another way to achieve the same result.  If so, I’d be interested in how you’ve done it.