Skip to main content

Pentaho+ documentation is moving!

The new product documentation portal is here. Check it out now at docs.hitachivantara.com

 

Hitachi Vantara Lumada and Pentaho Documentation

MQL formula syntax

Parent article

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 NameParametersDescription
ORTwo or more boolean expressionsReturns true if one or more parameters are true.
ANDTwo or more boolean expressionsReturns true if all parameters are true.
LIKETwoCompares a column to a regular expression, using % as a wild card.
INTwo or moreChecks to see if the first parameter is in the following list of parameters.
NOWN/AThe current date
DATEThree numeric parameters: Year, month, and dayThe specified date
DATEVALUEOne text parameter: year-month-dayThe specified date
CASETwo or moreEvaluates 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.
COALESCEOne or moreReturns the first non-null parameter. If all parameters are null, the message in the last parameter is returned.
DATEMATHOne expressionReturns a date value based on a DATEMATH expression (see DateMath Javadoc for full syntax).

The following table contains examples of the functions:

Function NameExample
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 is 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.