The MITS Query Language is the a simple query language used my MITS Discover to extract and present data from the hypercubes. The MQL statement is the only thing that is stored by MITS Discover 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 MITS 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 MITS Discover
To access a report’s MQL statement:
- Click the Options button from the report’s main tool bar.
- Select Edit MQL from the list.
- The MQL statement can be modified as desired, following the correct syntax as provided below.
- 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 MITS 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 MITS Discover 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
|The verb of the statement. All MQL statements start with the word FLASH.
|The name of the Hypercube that will be queried.
|Establishes the report’s exploration (or drill- down) path.
|The column IDs of the columns displayed in the report. Column sorting and filtering are also displayed here.
|HEADING " [heading]"
|The heading that will be displayed at the top of the report.
|HEADING "2Y Cost R by W"
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.
|Customer - Bill To
|Customer - Ship To
|Location (GL Location)
|Vendor - Bill From
|Vendor - Ship From
|Vendor - Product
Working with Eons in a Flash Statement
Simply put, and EON in MITS is a reference to an amount of time. In a standard MITS Discover 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 MITS 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.
These are the most common eons you'll see in MITS Discover 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.
|Any Previous Month
|M-number (e.x. M-1, M-2, M-9)
|Any Previous Quarter
|Q-number (e.x. Q-1, Q-2, Q-9)
|Any Previous Year
|Y-number (e.x. Y-1, Y-2, Y-9)
|Year to Date as of Last Month End
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
By default, the rows of any report are sorted by identifier description. You can instruct MITS Discover 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
Adds bottom totals to the current report
Summarizes the totals by the values in a column. For example, BREAK-ON C!STATE would add totals in each column for each unique state in the C!STATE column.
Sorts the display in ascending order by the specified column.
Sorts the current report display in descending order by the specified column.
Restricts the viewable number of rows returned for the lowest identifier in the current drill-down path.
The first word of every MQL statement. When used alone in character mode MITS, the flash statement for the current report will be displayed.
Specifies the heading that will be displayed at the top of the report.
Displays the identifier IDs
Inserts a blank row between horizontal rows for the specified identifier.
Suppresses the display of totals down to the specified exploration level.
Hides the identifier IDs in each identifier row.
Hides the identifier abbreviations in each identifier row.
Removes all horizontal rows that have a ZERO in every column.
Used to request specific identifiers or column range values for filtering purposes.