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.

Conditional SQL in Cognos by using prompt values to control line commenting

Here's a technique that allows the omission of certain sections of SQL in response to prompt values in Cognos Report Studio:

1 #join('|',substitute(prompt('Prompt','token','Excl'),'',grep(prompt('Prompt','token','Excl'),array('Incl', 'Excl /* '))))#

2 And column_name is not null 
3 #join('|',substitute(prompt('Prompt','token','Excl'),'',grep(prompt('Prompt','token','Excl'),array('Incl', 'Excl */ '))))# 

The prompt named Prompt has two values, Incl and Excl.

When the prompt value is 'Excl' the grep macro in line 1 returns the array element 'Excl /* '. The substitute macro replaces the array element with an array element containing ' /* '. The join macro converts the array element to a  string. /* is used as the start of a comment block in Cognos.

Line 3 resolves to */ when the prompt value is 'Excl' thus commenting out line 2 entirely.

Passing prompt parameter displayValue in URL to Cognos

I'd been used to passing prompt parameter values for Cognos reports in a URL by just simply using &p_<parameter_name>=<value>.

However, I came across a report where both the useValue and displayValue of the parameter were involved in the final output and I discovered that using &p_<parameter_name>=<value> seems to result in <value> being used as both the useValue and displayValue.  This was no good as the report required the two different values as set up on the prompt page in the report.

After endless searching and guesswork I eventually stumbled across how to send the two values separately in a URL:

&p_<parameter_name>=<selectChoices><selectOption useValue="<useValue>" displayValue="<displayValue>"/></selectChoices>

Commenting Cognos Report Studio expressions

I'd always wanted to be able to comment some of the more complex expressions I've written to aid subsequent maintenance.  Eventually I stumbled across how to do it (I think it might have been on COGNOiSe) and I have been using it liberally ever since.

#/* Insert comment here */#