Value Prompt on report page

[fml55555]

Hi Gurus,

I have created a value prompt in the report page along side the lists and graphs. The values in list and graph are filtered based on the value in the value prompt. I am facing the following problems

1. I want an 'All' option the the prompt which would give the O/P when all value are selected.
2. There are some values of the prompt which do not have any corresponding values in the graph or list. I want the prompt to only show values which have values in list or chart
3. I want the choice (the first time the report runs to be 'All')

How can these be achieved?

[MFGF]

Hi Gurus,

I have created a value prompt in the report page along side the lists and graphs. The values in list and graph are filtered based on the value in the value prompt. I am facing the following problems

1. I want an 'All' option the the prompt which would give the O/P when all value are selected.
2. There are some values of the prompt which do not have any corresponding values in the graph or list. I want the prompt to only show values which have values in list or chart
3. I want the choice (the first time the report runs to be 'All')

How can these be achieved?

Hi,

You haven't said whether you are using a dimensional or relational package? As you mention lists, I assume relational?

The approach I would take would be to firstly add a Static Value to your prompt with a Use Value of 'All'. Set this to be the default selection of the prompt. Then change the filter on each report object (list/chart) to be [Your item of data] = ?YourParam? OR 'All' = ?Yourparam?

To limit the rows displayed in your prompt to a subset of the rows you currenltly display, add a filter as needed to the query providing the prompt values.

Cheers!

MF.

[fml55555]

Hi MFGF,

Thank you so much for your reply. I am however getting the following error

RSV-VAL-0002 Invalid expression Case ParamDisplayValue('Parameter1') = 'ALL'  then 'A1'

end. CRX-YXX-4010 A parsing error was found at or near the position 46 in the expression "Case ParamDisplayValue('Parameter1') = 'ALL'  then 'A1'

end ".

[fml55555]

Here is the set of complete error messages

RSV-RND-0111 Conditional display values associated with prompt controls must not have an expression that is data dependent. The expression is ' Case ParamDisplayValue('Parameter1') = 'ALL'  then 'A1'

end'.

RSV-VAL-0002 Invalid expression Case ParamDisplayValue('Parameter1') = 'ALL'  then 'A1'

end. CRX-YXX-4010 A parsing error was found at or near the position 46 in the expression "Case ParamDisplayValue('Parameter1') = 'ALL'  then 'A1'

end ".

[fml55555]

I created a variable for the static selection. Is that necessary?

[Lynn]

Here is the set of complete error messages

RSV-RND-0111 Conditional display values associated with prompt controls must not have an expression that is data dependent. The expression is ' Case ParamDisplayValue('Parameter1') = 'ALL'  then 'A1'

end'.

RSV-VAL-0002 Invalid expression Case ParamDisplayValue('Parameter1') = 'ALL'  then 'A1'

end. CRX-YXX-4010 A parsing error was found at or near the position 46 in the expression "Case ParamDisplayValue('Parameter1') = 'ALL'  then 'A1'

end ".

You don't specify where you are attempting to use this expression and I'm not entirely sure what the purpose of the transformation is since there isn't any prior mention of A1 in your post. There is also no prior mention of conditional display, so I think I've totally lost track of what you are trying to achieve.

Oh dear, and now you've posted again about a variable. The muppet posted a sound approach to solve your problem and there was no mention of a need for variables.

Setting those points aside for a moment, your case syntax is incorrect. You are missing WHEN and END. Here is a link explaining the case statement: http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.cogsql.8.4.0.doc/cogsql_id2002case.html

In addition, ParamDisplayValue is somewhat suspect. Normally this would only be included in a layout calculation. In a filter or query item I would expect to see ?Parameter1? syntax.

P.S. It is a good practice to give your parameters a meaningful name.

[MFGF]

You don't specify where you are attempting to use this expression and I'm not entirely sure what the purpose of the transformation is since there isn't any prior mention of A1 in your post. There is also no prior mention of conditional display, so I think I've totally lost track of what you are trying to achieve.

Oh dear, and now you've posted again about a variable. The muppet posted a sound approach to solve your problem and there was no mention of a need for variables.

Setting those points aside for a moment, your case syntax is incorrect. You are missing WHEN and END. Here is a link explaining the case statement: http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.cogsql.8.4.0.doc/cogsql_id2002case.html

In addition, ParamDisplayValue is somewhat suspect. Normally this would only be included in a layout calculation. In a filter or query item I would expect to see ?Parameter1? syntax.

P.S. It is a good practice to give your parameters a meaningful name.

Yep. Everything Lynn said. Sometimes I am sure she can read my thoughts. In fact, come to think of it, there's no doubt. If you don't believe me, ask her what I'm thinking right now! :)

MF.

[fml55555]

 :) Thank you so much MFGF and Lynn.

[MDXpressor]

Yep. Everything Lynn said. Sometimes I am sure she can read my thoughts. In fact, come to think of it, there's no doubt. If you don't believe me, ask her what I'm thinking right now! :)

MF.

I highly doubt that Lynn wants to spend any time with your thoughts...

[Lynn]

If the muppet's thoughts are inclined to travel to the Bahamas or perhaps even to The Waldorf Astoria then I might be persuaded to tag along :)


Sent from my iPhone using Tapatalk

[MFGF]

I highly doubt that Lynn wants to spend any time with your thoughts...

Yikes! That is probably very true! She wouldn't want to know anything about staying in Nassau or a swish hotel in New York, I imagine!

If the muppet's thoughts are inclined to travel to the Bahamas or perhaps even to The Waldorf Astoria then I might be persuaded to tag along :)


Sent from my iPhone using Tapatalk

Oh. There you go. As I said - proof she can read my thoughts!! ;)

MF.