MITS Query Language (MQL) for White Cup BI

The MITS Query Language is a simple query language used my White Cup BI to extract and present data from the hypercubes.  The MQL statement is the only thing that is stored by White Cup BI when a report is saved. This means that no graphical components are required to recreate a report. The MQL statement includes all of the information about the current exploration, columns, sorting, filtering, and formatting. If your White Cup administrator has granted you the proper permissions, you can modify this command manually to create specific displays quickly. MQL is also used in the creation and modification of dashboard and scorecards.

 
Accessing the MQL Statement in White Cup BI

To access a report’s MQL statement:

1. Click the Options button from the report’s main tool bar.
2. Select Edit MQL from the list.
3. The MQL statement can be modified as desired, following the correct syntax as provided below.
4. Click the Execute button to run the code and generate the report.
   

 
Understanding MQL Statements

MQL (or FLASH) statements comprise one of the two components stored by White Cup BI when a report is saved (the other being the report description). Each FLASH statement holds all of the necessary information about the current exploration, currently displayed columns, sorting, filtering, formatting, etc.

You can manually modify a FLASH statement to create specific displays quickly, and you can also make manual modifications to a FLASH statement that allow you to create custom reports that cannot be created using the standard tools and wizards provided in the White Cup BI browser client. 

Here is the basic syntax and an example of a FLASH statement.

FLASH <appName> [identifiers] [columns] HEADING "[heading]"

FLASH SALESDEMO R W COST.Y COST.Y-1 HEADING "2Y Cost R by W"

Token                                             Description                                        Example

Working with Identifiers in a Flash Statement

In this example, ‘W’ is the abbreviation for the WAREHOUSE identifier. The example below shows an initial exploration by warehouse.

FLASH SALESDEMO W

Specifying Identifier Values for Select Exploration

Explicit IDs can be specified using the WITH keyword. The following example will drill-down into warehouse 4 and then by the SALES REP identifier:

FLASH SALESDEMO WITH W = "4" SR

Continuing with that example, here will drill-down to the specific sales rep Bob Donis  and then we add the CUSTOMER identifier at the end. 

FLASH SALESDEMO WITH W = "4" WITH SR = "BOD" C

The WITH keyword can be used in conjunction with the NOT modifier as shown in the following statement.  In this example, the resulting report would explore all of the customers under all of the sales reps in warehouse 4, but would NOT display sales reps HOUSE1 or HOUSE2.

FLASH SALESDEMO WITH W = "4" WITH SR NOT "HOUSE1" "HOUSE2" C

While specific identifier IDs will depend on both the ERP and the cube, here are some common identifiers you'll find in most configurations.  If you are ever in doubt as to the identifier ID for any given identifier, you can drill down by that identifier using the regular interface and then check the MQL to see what identifier ID the system is using.

Working with Eons in a Flash Statement

Simply put, and EON in White Cup BI is a reference to an amount of time.  In a standard White Cup BI configuration, dates are relative to the most recent transaction in the hypercube being reported upon.

For example, Month is the eon for all the activity in the current calendar month.  Similarly Quarter refers to the current quarter, and Year to the current year.  There is a chart below that shows the common eons used in most cubes.

If wish to see this year's sales, you'd use the column name (SALES) followed by a period and then the eon Y.

FLASH SALESDEMO SALES.Y

To see the last year's sales, you'd add a -1 to the eon, telling White Cup BI to go back one year.

FLASH SALESDEMO SALES.Y-1

There are two special time periods available for the last three and last thirteen months.  This gives you a rolling total regardless of when the traditional quarters and years fall.

M-1..3 provides the last three months

M.1..13 provides the last thirteen months

These are special eons, and using the double period with other vales (like M-5..9) will not work. 

Common Eons

These are the most common eons you'll see in White Cup BI using a standard configuration.  They are based on the calendar year.  If you have a custom set of eons, these eons may or may not apply.  If you have a customization to use a fiscal calendar, then your quarters and years may be based on the start of the fiscal year rather than Jan 1.

Working with Columns in a FLASH Statement

The FLASH statement below would display the “Sales (year-to-date)” column and the “Profit (back 1 quarter)” column, including the values for all of the warehouses. 

FLASH SALESDEMO W SALES.Y PROFIT.Q-1

Specifying Sorting

By default, the rows of any report are sorted by identifier description. You can instruct White Cup BI to sort the report by the values in a specific column using the BY or BY-DSND modifiers.

 The following flash statement will display sales dollars year-to-date for all warehouses, but instead of sorting by the default warehouse description the report will be sorted by the values in the SALES.Y column in descending order:

FLASH SALESDEMO W SALES.Y BY-DSND SALES.Y

A FLASH statement can include any number of column sorts or filters using common relational operators. 

FLASH SALESDEMO SR BY SALES.M C BY-DSND SALES.M SALES.Y

In this example, the list of all sales reps will be displayed with an exploration by customer. The sales reps will be sorted in ascending order based on their corresponding values in the SALES.M column and the customers under those sales reps will be sorted in descending order based on their corresponding values in the SALES.M column. However, notice that the SALES.M column is not displayed - the SALES.Y column is displayed instead. This example shows that you can perform sorting and filtering based on a column that is not displayed in the report. For this reason it is a good idea to keep an eye on your MQL statement as there may be sorting or filtering in place that is based on columns that are not visible.

MQL Language Reference