Cognos Analytics

Hello,
I created a report where I have a prompt with variable >pBereich<.  This prompt is based on a query and have also a static value >ALLOBJ< ("All objects"). The report works as expected. 

We are using Cognos Analytics version = 11.2.4 FP4

Now I want to add a new column in the output (simple list based on queries) where I using the variable. The column/calculation I add looks like:
if (?pBereich? = 'ALLOBJ')
then
(
  replace ([OBJSP] ; '/content' ; '')
)
else
(
  replace ([OBJSP] ; ?pBereich? ; '')
)
As long as the prompt is used with  >ALLOBJ< the report give a result (as expected).

Selecting a value from the prompt list, the result (the simple list) is empty.

Basically, it is completely irrelevant what the value of the variable is. Either the replace function does something or it does not, but in any case a text should be returned.

If I change the line
  replace ([OBJSP] ; ?pBereich? ; '')
to
  'dummy text'
or
  ?pBereich? 
then the report works normal / as before, showing 'dummy text' or the value I selected in the prompt.
(using 'dummy text' + ?pBereich? also result in empty list)

Is this beaviour right or wrong? I assume it is wrong - but why and what do I have to do to be able to use the variable in the column/calculation?

Note: 
Using another variable from another (static values only) prompt like this:
    'any text + ' + ?pSourceType?
I do not get this empty result behaviour - but the output is:
    any text + /content%
No idea why there is this % character at the end of the output, because the static value for variable pSourceType is only /content

Thanks
Michael

Hi Michael,

I just read your post quickly and have to ask: don't you have to place the macro #SQ()# for your "else replace" line so it looks like this:

else
(
replace ([OBJSP] ; #sq(prompt('pBereich';'String'))# ; '')
)

?parameter? returns just the value without single qutoes. sq() adds them to the string and should give the correct syntax.

Cheers

------------------------------
Robert Dostal
Team Leader BI
GEMÜ Gebr. Müller Apparatebau GmbH & Co. KG
Ingelfingen

Original Message:
Sent: Wed February 05, 2025 04:47 AM
From: Michael Haass
Subject: Using prompt variable in column get an empty result

Hello,
I created a report where I have a prompt with variable >pBereich<.  This prompt is based on a query and have also a static value >ALLOBJ< ("All objects"). The report works as expected. 

We are using Cognos Analytics version = 11.2.4 FP4

Now I want to add a new column in the output (simple list based on queries) where I using the variable. The column/calculation I add looks like:
if (?pBereich? = 'ALLOBJ')
then
(
  replace ([OBJSP] ; '/content' ; '')
)
else
(
  replace ([OBJSP] ; ?pBereich? ; '')
)
As long as the prompt is used with  >ALLOBJ< the report give a result (as expected).

Selecting a value from the prompt list, the result (the simple list) is empty.

Basically, it is completely irrelevant what the value of the variable is. Either the replace function does something or it does not, but in any case a text should be returned.

If I change the line
  replace ([OBJSP] ; ?pBereich? ; '')
to
  'dummy text'
or
  ?pBereich? 
then the report works normal / as before, showing 'dummy text' or the value I selected in the prompt.
(using 'dummy text' + ?pBereich? also result in empty list)

Is this beaviour right or wrong? I assume it is wrong - but why and what do I have to do to be able to use the variable in the column/calculation?

Note: 
Using another variable from another (static values only) prompt like this:
    'any text + ' + ?pSourceType?
I do not get this empty result behaviour - but the output is:
    any text + /content%
No idea why there is this % character at the end of the output, because the static value for variable pSourceType is only /content

Thanks
Michael

------------------------------
MichaelHaass
------------------------------