Manually Creating Calculated Columns

In addition to using the Create Calculated Column and Add Quick Calculated Column tools for creating a calculated column, you can also add a column manually by editing a report's MQL statement.

Open a report where you want to create a calculated column. Click Options and select Edit MQL. This will open the MQL editor.

MQL, MITS Query Language, is the code that describes the report in terms the database underlying MITS Discover can understand and knows what information to return when presenting a report. An MQL statement is made of a verb (it is always FLASH), a subject (the cube), the drill down path, sorts and filters, the columns, and additional report display details.

For this topic, we are specifically interested in the columns. Here is an example of a simple MQL statement. It returns the sales for the previous two months (INVOICED.AMT.M-1 INVOICED.AMT.M-2) for all warehouses (L1) in the cube SALES.

FLASH SALES L1 INVOICED.AMT.M-1 INVOICED.AMT.M-2

There are several functions available. Below is a chart showing the basic functions.

ADD(col1,col2) = col1 + col2
SUBTRACT(col1,col2) = col1 – col2
DIFF(col1,col2) = col2 – col1
MULTIPLY(col1,col2) = col1 * col2
DIVIDE(col1,col2) = col1 / col2
MARGIN(col1,col2) = (col1 – col2) / col1 * 100
TREND(col1,col2) = ((col2 – col1) / ABS(col1)) * 100
POT(col1) Calculates a percent of total for each row
EXTERNAL.COLUMN(hypercube,col1) Specify a hypercube and a column ID from another hypercube

Using the example MQL, here are how you'd add those two columns by manually editing the MQL.

FLASH SALES L1 ADD(INVOICED.AMT.M-1,INVOICED.AMT.M-2)

The syntax is FUNCTION(col1,col2). There a no spaces in the function.

Only two columns can be used in a function, but any number of columns can be nested. In our example, if we wanted to add the total for the last three month, the MQL would look like this.

ADD(INVOICED.AMT.M-3,ADD(INVOICED.AMT.M-1,INVOICED.AMT.M-2))

The syntax here is FUNCTION(col3,FUNCTION(col1,col2))

In addition to all of the above, static numbers can be used in place of any column in the function. Here is an example of multiplying a single column by 100.

FLASH SALES L1 MULTIPLY(INVOICED.AMT.M-1,100)

Static numbers with decimal places can be used as well. When presenting calculated columns with decimals, MITS Discover will always display the results with number of decimal places equal to the greatest number of decimal places in the columns of the function. If a column has one decimal place and the second column in the function has three decimal places, the result will display with three decimal places.