Wednesday, March 25, 2009

Dissecting Paint Repository - II


In the last article, I had talked about dissecting Paint Repository.  Let me now give you some insights on how to use the Calculation Wizard to create new columns that compare two existing columns.

In the Paint repository, we have two columns "Dollars" and "Month Ago Dollars".  Using the calculation wizard, we can create the following four calculated columns based on the expression templates provided by OBIEE

  • Change
    CurrentX - ComparisonX
  • Index
    1.0 * CurrentX / ComparisonX
  • Percent Change
    100.0 * (CurrentX - ComparisonX) / ComparisonX
  • Percent
    100.0 * (CurrentX / ComparisonX)
CurrentX        -> Column using which Calculation wizard was invoked
ComparisonX -> Column chosen in the wizard for comparison

Here, if we want to calculate "% Chg Month Ago Dollars" i.e change in Dollars from its Month Ago value, we can use the calculation wizard as shown below.

Right click "Dollars" column and select "Calculation Wizard"



Click Next and choose "Month Ago Dollars" as shown below



Click Next and choose the comparison column that we would like to create. We can choose all four and the wizard will create 4 new columns with the code in it to do the comparison.


Click Next and Finish. A new column called "% Chg Month Ago Dollars" will be created as we chose only Percent Change above. See the code that the calculation wizard has created for the new calculated column.