5 Replies Latest reply on Aug 24, 2016 10:37 AM by Todd Moses

    Data Grid - Independent Totals

    Todd Moses

      I have two instances of the same data source, linked by a "Period Date" field so that I can create a data grid report with parameters.

       

      However, I'd like to use the second data source as the total row so that it is not impacted by the users selections from the parameters.

       

      I can do this with a lines report.  Is it possible with a data grid?

       

      Example:

      Red Cars = 1

      Blue Cars = 1

      Boats = 2

      Airplanes = 2

      Total =  6

       

      When they select "Cars" from a filter, I want to see:

      Red Cars = 1

      Blue Cars = 1

      Total =  6

        • 1. Re: Data Grid - Independent Totals
          Joe Oppelt

          See attached.  (V 8.2 workbook).

          • 2. Re: Data Grid - Independent Totals
            Joe Oppelt

            Sheet 1 uses a table calc to do a LOOKUP to get whatever you pick from the list of items in that dimension.


            When you use a table calc as a filter, it still leaves the underlying table intact on the sheet, and just DISPLAYS what was requested, rather than eliminate actual rows from the table as a quick filter does.  thus, the TOTAL line still adds up whatever is in the entire table.  Pretty powerful, but it can also be frustrating if you don't get what's happening under the hood.

             

            Sheet 2 uses a LOOKUP as well, but rather than select from a filter list, it uses a parameter value to do a CONTAINS operation.


            One caveat for this method.  If you type in something that gets NOTHING from the table, you get no rows at all, and therefore no total.

            • 3. Re: Data Grid - Independent Totals
              Todd Moses

              Sheet1 is VERY close to what I'm trying to do, but it appears that in order for the Grand Total line to remain intact, the bottom value on the filter needs to be selected.

               

              is the "Max" mandatory?

              • 4. Re: Data Grid - Independent Totals
                Joe Oppelt

                Not sure what you mean by your first statement.  If I deselect "red cars" from the filter, I still have a grand total of 6.

                 

                As for "MAX", there has to be some sort of aggregation of the field inside the LOOKUP function.  MAX.  MIN.  Both work.  A lot of times you can use ATTR for a dimension like this, but for some reason that one is adding a (star) to the filter list.  So I just went with MAX here.

                • 5. Re: Data Grid - Independent Totals
                  Todd Moses

                  It turns out that the issue I was seeing was an anomaly in the data I'm pointing to, and not a problem with your solution.

                   

                  Thank you for your help.