Pivot Table: Conditionally hide/display expressions

[#1]

I have a series of pivot tables created. I would like the user to be able to choose which of the following possibilities to display:

1. Units (POSUnits)
2. Amount (POSAmount)
3. Both Units & Amounts

I was thinking of creating an input box that would allow the user to select an option from a drop-down list, then use variables to show or hide expressions. In other words, I would like to conditionally display or hide pivot table expressions. Can this be done? If so, how?

Thanks in advance for your help!

[#2]

Here's what I tried, by it doesn't work:

If($(vExpressionToDisplay)= '$$', Sum(POSAmt), Sum(POSQty))

[#3]

Hi,

Have a look at the attached QVW file. Is this what you wanted?

Basically, I have created an inline load statement with Chart and ChartExpression as two variables in it like below.

Charts:

LOAD * INLINE [

Chart, ChartExpression

Units, Sum(POSUnits)

Amount, Sum(POSAmount)

];

Then I am giving the Chart variable as a list box for the user to select. In the pivot table I used month as dimension and $(=ChartExpression) as the expression.

Hope this will give you an idea how to deal with this issue.

Best Regards,

Sajeevan

[Oleg_Troyansky · Accepted solution]

When the number of expressions is fixed, you can use dynamically generated expresisons, like in the example above... If you need to conditionally control the number of columns (expresisons) in a pivot table, - the only way to accomplish it today is by creating duplicate objects with the desired number of columns in each and conditionally show/hide one of the objects. Pivot tables don't allow any other conditional control of the number of columns...

[#5]

Sajeevan, thanks for your great example. But Oleg is right...The number of expressions to display is not fixed. I was looking for an easy fix, but it doesn't look like I'll get it.

I haven't worked with Actions yet & was trying to avoid this since I'm in a rush and am unaware of the pitfalls. I've already checked out John's 'DynamicReportTemplate.qvw' example; however, the QVW legacy application I'm working with has 23 sheets with over 100 pivot tables. Would you create Actions if you were me?

[Oleg_Troyansky]

jstephens wrote:the QVW legacy application I'm working with has 23 sheets with over 100 pivot tables.

Sounds like it was a dartboard, with very little business requirements defined... When you upgrade to version 10, you'll be able to track what tabs and what Charts are being actually used, and you'll see the few that are truly valuable, and the many that are just sitting there...

Conditional Hide/Show doesn't require Actions. It's enough to have Fields or Variables controlling the conditions, and the "Show Conditions" based on the values in those Fields and Variables. However, if all 100 Pivot Tables potentially need to be duplicated - I wouldn't do it either...

Try to narrow focus to a manageable scope, and then decide what to do...

[#7]

As Oleg suggested you can conditionally hide/show any sheet and objects by using controlling variables or fields. Have a look the example attached.

Here, I am displaying the sheets depending on the selection the user make in Sheets list box - Look at the sheet properties / General tab / Show Sheet to understand the condition for displaying the sheet. There are two chart objects in each sheet - look at chart properties / layout / show to understand the condition for displaying the chart.

Hope this will help you.

[#8]

  how to hide  certain expressions,Chart Style:

Can anyone have any idea on how to solve this?