Use Variable as Dimension

[#1]

I want to create a variable that lists out several field options that would be incorporated into a table chart that would allow the user to change the dimension by changing the variable selection.  I want to avoid using an if statement as a calculated dimensior for performance reasons. 

[its_anandrjs]

Hi kcambell,

As i understand you want to create a list which holds different dimension list and when you select it. Then you have to analysis pertecular data am i right. If you have any sample then provide me.

Regards,

Anand

[johnw]

I'd just use a cyclic field group.

It's easy to do what I think you're asking for, though.  Create a variable like vField.  Use $(vField) as your chart dimension.  If you want to limit the users to specific fields and give them a drop down, say, go to the constraints tab of the input box, select "predefined values in drop-down", checkmark "listed values", and then list your field names separated by semicolons in the box.

[#4]

I have tried what you suggested, but all it does is list out the variable selection instead of the data that would belong to that field name.

[johnw]

It works fine for me.  See attached, which also compares it to using a cyclic dimension, my suggested approach.

Edit: It looks like you probably forgot the dollar sign expansion, $().  If I leave that off, I see just the field name, not the field values.

[#6]

Thank you!  I was missing the dollar sign.  Any suggestion on how to get field names to display in the drop down without the brackets around it and still get it to still work as a field selection?

[johnw · Accepted solution]

Interesting!  I'd thought you could list the field without the brackets, and it would work, but it doesn't.  Makes sense in hind sight.  Since we have an = sign in the dimension, the dimension ends up like =Customer Name, which isn't valid.  Easily fixed, though.  Leave the brackets off in the drop down, then add them in the dimension:

=[$(vField)]

Fixed in the attached.

[#8]

That worked perfectly!!! Thank you so much!

[#9]

Hi,

Thanks kcampbell to have initiated such topic, I was also interested in such behavior! In fact, I have few reports which look alike, the only difference is only on one or two dimensions. So I wanted to avoid duplicating report and play with a variable to show/hide them.

I have an extra request to your initial one, which is to add an 2nd field, which remain optional. I have checked the option "Suppress When Value Is Null" for the 2nd field, but the pivot table do not render any row if no value is selected for the 2nd field.

Is it consider as a bug? Or may be I don't understand the meaning of the option "Suppress When Value Is Null"

I have modified John's file as per my requirement.

[johnw]

So far as I know, you can't really hide a dimension column in a pivot table that way.  I think you would have to play with the display condition on two different charts, one with one dimension, one with two dimensions.  Alternatively, you could step up to a full macro implementation of dynamic charting.  See attached.

I'm told that version 11 has conditional enable of dimensions and expressions.  This will likely be a better way to handle dynamic charting than macros, but I haven't poked at version 11 yet.  Our shop is still on version 9.