Skip to main content
Pentaho Documentation

MQL Formula Syntax

You can apply global or user and role row-level constraints using MQL.

Global Constraints

You can use all of the standard operators, and any of the following functions when defining a global constraint:

Function Name Parameters Description
OR Two or more boolean expressions Returns true if one or more parameters are true
AND Two or more boolean expressions Returns true if all parameters are true
LIKE Two Compares a column to a regular expression, using % as a wild card
IN Two or more Checks to see if the first parameter is in the following list of parameters
NOW N/A The current date
DATE Three numeric parameters: Year, month, and day The specified date
DATEVALUE One text parameter: year-month-day The specified date
CASE Two or more Evaluates the odd-numbered parameters, and returns the even numbered parameter values. If there are an odd number of parameters, the last parameter is returned if no other parameter evaluates to true.
COALESCE One or more Returns the first non-null parameter. If all are null, the message in the last parameter is returned.
DATEMATH One expression Returns a date value based on a DATEMATH expression (see related links below for a link to the DATEMATH Javadoc)

The following table contains examples of the functions:

Function Name Example
OR
OR( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
    [BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000 ) 
AND
AND( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
     [BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000 ) 
LIKE
LIKE([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "%SMITH%") 
IN
IN([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "Adam Smith"; "Brian Jones")
NOW
NOW()
DATE
DATE(2008;4;15) 
DATEVALUE
DATEVALUE("2008-04-15")
CASE
CASE( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars"; "European Cars";
      [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "AsiaCars"; "Asian Cars"; "Unknown Cars")
COALESCE
COALESCE( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME];
          [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERID]; "Customer is Null" ) 
DATEMATH
DATEMATH("0:ME -1:DS")

This expression represents 00:00:00.000 on the day before the last day of the current month. 

 

User and Role Row-Level Constraints

The MQL Formula syntax for defining a user or role row constraint is:

[table.column] = "row"

The table and column are defined as part of a metadata business model. Here is an example that isolates access to data from the Sales department:

[BT_OFFICE.BC_DEPARTMENT]="Sales"

It's also possible to give or deny access to an entire role, or a single user, by selecting that user or role, then using a TRUE() or FALSE() boolean for a constraint.