Sample 10: Using Formulas

This Essbase report script sample demonstrates how you can use column calculation formulas to manipulate the column value of a particular row or a constant.

In this report sample, each % column represents the quarterly values as a percent of Sales for the respective quarter. In addition, the Avg column represents an average value for the two quarters.

                                      Actual Product Market

                              Qtr1      %     Qtr2     %       Avg 
                          ======== ====== ======== ====== ======== 

Sales                      141,245 100.00  136,193 100.00  138,719 
Cost_of_Goods_Sold          58,104  41.14   56,281  41.32   57,193 
  Margin                    83,141  58.86   79,912  58.68   81,527 
Marketing                   11,211   7.94   11,302   8.30   11,257 
Payroll                     43,817  31.02   43,827  32.18   43,822 
Miscellaneous                  302   0.21    1,859   1.36    1,081 
  Total_Expenses            55,330  39.17   56,988  41.84   56,159 
    Profit                  27,811  19.69   22,924  16.83   25,368 
    Profit_%                    20   0.01       17   0.01       18 
    Margin_%                    59   0.04       59   0.04       59

Use the following script to create Sample 10:

// This report performs column calculations based on values in a 
// report row.

<PAGE (Scenario, Product, Market)
Actual

      <COLUMN (Year)
      Qtr1 Qtr2

{ DECIMAL 2 3 4 }
{ NAMEWIDTH 22 WIDTH 7 3 4 } 
{ ORDER 0 1 3 2 4 5 }

<ROW (Accounts)
{ SAVEROW } Sales 
    !

{ CALCULATE COLUMN "%" = 1 % "Sales" 1 }
{ CALCULATE COLUMN "% " = 2 % "Sales" 2 }
{ CALCULATE COLUMN "Avg" = 1 + 2 / 2. }

<DESCENDANTS Accounts
    !

Note:

You can include comments in the report by preceding the text with //. The Report Extractor ignores everything that follows the double slash. You can use comments to explain report processing.

The SAVEROW command reserves space for a row member that the CALCULATE COLUMN command calculates. In this case, the calculation affects SALES. The ! is required after the member name.

The CALCULATE COLUMN command allows column numbers, row names, or constants in formulas. You can read the first calculation this way: "% equals column 1 as a percent of Sales in column 1."

Each calculated column label must be unique. Note how the second calculated column label has a blank space after the % sign.

To specify a constant, define a number followed by a period. You can use a constant in either a column or row calculation. The last column calculation takes the sum of columns 1 and 2 and divides by the value 2. This formula is interpreted as (1+2)/2, not 1 + (2/2.).

As noted in Sample 7: Using Aliases, the ORDER command arranges columns in the specified order. By default, calculated columns are added to the end of existing columns retrieved from the database. In this example, columns 0-2 are automatically retrieved, based on selected members. Columns 3-5 are the calculated columns. The ORDER command applies to both retrieved and calculated columns.