Calculated Columns

There are three ways to create calculated columns in WC BI.  The first is from the column header.  The second is from the column selector.  And the third is by manually adding it to the MQL that defines the report.

Adding a Calculated Column from the Column Header

Clicking the column header exposes a menu of options.  

The "Add Quick Calculated Column" tool allows you to create Trend, Difference, or Percent of Total quickly

Select the Quick Calculated Column from the menu and you'll get a dialog like the one below.  Select your option and click Add.  The column will appear on the report.

Adding a Calculated Column from the Column Selector

This is the more traditional way to add a calculated column to a report.  It also offers options the Quick Calculated Column tool does not.

While viewing a report, click on the Columns button in the menu.  At the bottom of the page, beneath the lists of available columns, is button labeled Add Calculated Column.  

Clicking this button opens the column wizard shown here.

The thirteen available functions are presented in a drop down menu.  Select the function desired and a new column selector will appear.  It will ask for a base column and a compare column.  These represent the two columns used in the calculation.

A side note about DIFF and SUBTRACT.  On the surface these would seem to be the same function, and basically they are.  They differ in the order of columns they use.  SUBTRACT is base column minus compare column.  DIFF is compare column minus base column.  Keep this in mind when selecting the function and contributing columns.  

The PERCENT function is available starting with version 12.1.  It allows you calculate the percent value of one column against another.  It will divide one column by the other, multiply the results by 100, and add a percent sign to the results.

Adding a Calculated Column from the MQL

If you wish to add calculated columns directly to the MQL, this is possible from the Edit MQL dialog.

While viewing a report, click on Options and select Edit MQL.

MQL, MITS Query Language, is a collection of commands and variables that make up a technical definition of the report.  The MQL is what is stored when a report is saved and it runs when a report is opened.  MQL syntax is covered elsewhere in MITS documentation.

The syntax for adding a calculated column to the MQL looks like this:

FUNCTION(base column,compare column)

Here is an example:

ADD(SALES.M,SALES.M-1)

This column adds the current month's sales to the previous month's sales.  Notice there are no spaces in the expression and that the two columns are separated by a comma.

Using Columns from External Cubes in Calculations

When using the column selector or manually editing the MQL, columns from external cubes can be used in your calculated columns.  

If you're adding a calculated column using the columns selector, there will be a tab at the top of "Change Displayed Columns" called "Additional Analytics.  Select the cube, the column, and the eon just as you would for columns in the base cube of your report.

When adding columns from external cubes in the MQL, use the following syntax:

EXTERNAL.COLUMN(cubename,columnid)

Here is an example of how that would look in practice:

ADD(SALES.M,EXTERNAL.COLUMN(AP,INVOICED.AMT.S))

EXTERNAL.COLUMN() is the reference.  AP is the external cube.  INVOICED.AMT.S is the column.