7 Replies Latest reply: May 15, 2012 2:40 AM by lixiaowei RSS

Pivot Table: Conditionally hide/display expressions

jstephens

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!

  • Pivot Table: Conditionally hide/display expressions
    jstephens

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

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

    • Pivot Table: Conditionally hide/display expressions
      Sajeevan Govindan

      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

       

      • Pivot Table: Conditionally hide/display expressions
        Oleg Troyansky

        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...

        • Pivot Table: Conditionally hide/display expressions
          jstephens

          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. Wink

          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?

          • Pivot Table: Conditionally hide/display expressions
            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...