Problems using Render Variables to dynamically hide/show columns on List Report

[User123]

I have a report I have created where I need to dynamically render columns based on what a user selects that they want to see.  In order to do this, I built a variable for each column and then used that variable as the Render Variable for the List column. The first problem I encountered was that the report was returning too many records. For example - if a user on wants to see total number of items ordered and does not want to see the order id, the report was hiding the order id, but still showing using order id as the lowest level of detail and not aggregating the total number of items ordered.

So, in order to fix this, I entered an expression in each data item that is to be dynamically rendered:

IF (?ShowOrderID? = 'Yes') THEN
([Package].[OrderDetails].[Order ID])
ELSE
(Null)

However, now when I run the report, it immediately returns no data - it is blank.  I have tried using 0 instead of Null and in my string fields I have tried returning an empty string, but all with the same results.  I tried a CASE statement as well, but there was no difference.

The report I am working on has 20 different data items to be rendered dynamically, so changing something in order to test it takes bit of time.  So, I built a much smaller one for testing that only has 3-4 items to render dynamically.  The strange thing is that the smaller report works. 

Does anyone have any ideas?  I have tried everything I can think of and am at a loss.

Thanks!

[User123]

I figured out what the issue was.  It actually had nothing to do with the rendering dynamically.  The issue was that the first two columns on the report where also dynamically populated via a dropdown list.  I only had each data item in the query once, so if the item picked for the first two columns was also not selected for to show, the expression I was using would make the value for that data item null and therefore the report returned nothing. 

So I fixed it by inserting the data item into the query twice, once for the first two dynamic columns and then once for the render columns.

[david.stachon]

...one thing you're going to want to test is that the grain of the visible columns makes sense.

for example, if you had:

Canada $199
USA     $299

..and decided to "hide" the country column, you would still get:

$199
$299

...to make the report rollup the measure, you'll need to take a different approach. But if you don't, don't worry about it.

[User123]

David,

Thanks for your reply and the info.

For all of the measures in the report, I have changed both the aggregate function and the roll up aggregate function to be either total or average, based on which one I need.

Will that take care of what you are referring to?

[david.stachon]

......no, it won't.

..but it may not matter depending on your report. All you need to do to test is hide all the columns that you're prompting for (i.e with the prompt you've created). If you see measures not aggregating (meaning, not rolled together for each unique combination of dimensions/attributes that are on the list) then you'll need to revisit. Othewise, the approach you're taking is fine.

As an example, with all columns showing let's say you have:

Canada, Ottawa, 10
Canada, Toronto, 5
USA, New York, 30
USA, Washington, 25

...if you hide city, i think you would want to see: (notice the measure rolled up)

Canada, 15
USA, 55

...if you hide city, and country, i think you would want to see:

70

...what may happen is if you hide city, and country is to see:

10
5
30
25

(which I don't think you would want...the measures not rolling up based on what is displayed)

...however, if you have other columns on that report which break down those numbers then you have nothing to worry about.

Hope that makes sense!

[roberta_nodes]

Hi,
I have exactly this problem!!!
I would like to skow different aggregate values according to the displayed columns in a crosstab.
But by hiding columns the measures are not rolling up based on what is displayed!

What can i do??
Please help me!!

Thx in advance

......no, it won't.

..but it may not matter depending on your report. All you need to do to test is hide all the columns that you're prompting for (i.e with the prompt you've created). If you see measures not aggregating (meaning, not rolled together for each unique combination of dimensions/attributes that are on the list) then you'll need to revisit. Othewise, the approach you're taking is fine.

As an example, with all columns showing let's say you have:

Canada, Ottawa, 10
Canada, Toronto, 5
USA, New York, 30
USA, Washington, 25

...if you hide city, i think you would want to see: (notice the measure rolled up)

Canada, 15
USA, 55

...if you hide city, and country, i think you would want to see:

70

...what may happen is if you hide city, and country is to see:

10
5
30
25

(which I don't think you would want...the measures not rolling up based on what is displayed)

...however, if you have other columns on that report which break down those numbers then you have nothing to worry about.

Hope that makes sense!

[kc9400]

I know this is an old post, but did you get an answer to the problem?