0 Replies Latest reply: Sep 5, 2012 10:13 AM by ANDREW MCILWRICK RSS

    Grouping Expressions

      Hi

       

      I would appreciate any advice on the best way to specify expressions that apply to groups of rows based on data values.

       

      Seq     Type    Desc                  Code  Balance

      20      ASSETS  Cash and Equivalents  1100  10,000
                                            1120  100,000
                                            1130  50,000
      25      ASSETS  Receivable Net of All 1210  10,000
                                            1220  50,000
      30      ASSETS  Inventory             1410  10,000   
                                            1420  15,000
      40 < ARBITRARY EXPRESSION HERE >

      50      LIAB.   Payroll               2410  10,000
                                            2420  12,000
      60 < ARBITRARY EXPRESSION HERE >

       

      Across the dimensions of Seq, Type, Desc and Code it is obviously possible to specify normal chart sub-totalling.

       

      Instead, I need to "plug-in" a number of additional chart rows at say lines 40 and 60. These need to contain customizable expression code. So maybe Seq 40 might be the balances of Seq ( 20 + 25 ) - 30. In other words it is not a straightforward Sum. The Seq of 20,25 and 30 do not belong to a parent dimension.

       

      The expression needs to manipulate the values held in other rows. Seq 60 might be the balances of Seq 40 ( itself derived ) - 50.

       

      The data table can contain the rows for 40 and 60, but it is the syntax or methodology for basing the expression on the other rows of the table that I am struggling with. Rows 40 and 60 have no balances of their own. I cannot calculate the balances in the source database, or the script as the values need to change based upon user selections.

       

      I am using QV11SR1. I have tried using Set Analysis to derive these expressions but with no luck. I am not familiar with the AGGR function.

       

      Any advice would be much appreciated.

      Regards

      Andrew.