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.
