Passing Parameter values into Data Items / #prompt Question

[andy_mason_84]

Hi All,

In report studio I have say 4 optional cascading prompts

Level 1 -> Level 2 -> level 3 -> Level 4

In my report I have a list

Company | Level 1 | Level 2 | level 3 | Level 4 | Value

Now, what I want to do is if they only choose something from level 1 then I want the report to show

Company | Level 1 | Value

Which I have done by setting up a variable to say ?Level 2? is null and used it as a render variable on the column.


However, even though it does not display the columns it doesn't aggregate the value to level 1, it still splits it out by the various levels so you get something like this

Company | Level 1 | 10
Company | Level 1 | 10
Company | Level 1 | 10

When what I want is

Company | Level 1 | 30

So what I thought to do is in the query item tell it to but '' in Level 2/3/4 if nothing is selected in its parameter.

If (?level 2? Is null)
THEN
(Level 2)
Else
('')

This way everything will be blank and will group together.

The problem being by adding this code into the query item it makes the prompts REQUIRED and they need to be optional.

Any idea how to make it optional and still use it in the data item?  I tried #prompt('Level 2', 'token', '')# but didn't seem to like that either, but not sure I was using it correctly.

Thanks,

AM

[CognosPaul]

Render variables simply show/hide specific columns in the list. The field is still being included in the SQL.

The trick is to leave the render variable, but change the way you're handling the field.

First thing, in your prompt page set up a value prompt with multiselect and set to checkbox. That will contain one static value, the full path of the Level as use and "Level 2" as display. Let's call this parameter Level2.

In the level2 data item in your report, you would do:

#promptmany('Level2','token',sq('0'))#

If the prompt is checked, it will send [NS].[Hierarchy].[Level 2] to the parameter. If nothing is selected, it will return '0'. Change the render variable on Level2 to be paramvalue('Level2') is not null)

[hespora]

Good Morning,


very sorry for reviving this rather old thread, but I do have a specific question to this technique:

Is there any way to adapt this to work on multiple fields being suppressed with just one prompt? Let's say, I have a sales report, and I want to enable/disable inclusion of material Level, as in SKU *and* SKU Description. Merging both fields into one would work but really isn't an Option.


Thanks,
Mark.

[hespora]

nvm, solved it. On the prompt static value, instead of using the full path to the data item, specify any string as a toggle. Then, in your data item, use if then else to check whether the promptmany macro matches the string you specified, if so, then full path to data item; if not (null).

Works, I'm just assuming this is actually way more load on the back end.