...
- Table: Orders (BT_ORDER_FACT)
- ID = BC_FACT_ORDER_TURNOVER
- Name = Turnover
- Formula = SUM( [PentahoDoc:BT_ORDER_FACT.BC_FACT_ORDER_NRPRODUCTS] * [PentahoDoc:BT_PRODUCT.BC_DIM_PRODUCT_PRICE] )
- Exact = Yes
...
Now, suppose we want to generate the multiplication of the 2 sums (different use-case), we define the formula as "[PentahoDoc:BT_ORDER_FACT.BC_FACT_ORDER_NRPRODUCTS] * [PentahoDoc:BT_PRODUCT.BC_DIM_PRODUCT_PRICE]" (without the SUM) and specify an aggregation for the 2 used business columns. The generated SQL will then be:
...
- ID = BC_FACT_ORDER_TURNOVER_TAXES
- Name = Turnover Taxes
- Formula = [PentahoDoc:BT_ORDER_FACT.BC_FACT_ORDER_TURNOVER] * 7 / 100
- Exact = Yes
...
Function Name | Parameters | Description | Example |
|---|---|---|---|
OR | 2 or more boolean expression parameters | Returns true if one or more parameters are true | OR( |
AND | 2 or more boolean expression parameters | Returns true if all parameters are true | AND( |
LIKE | 2 parameters | Compares a column to a regular expression, using "%" as wild cards | LIKE([PentahoDoc:BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "%SMITH%") |
IN | 2 or more parameters | Checks to see if the first parameter is in the following list of parameters | IN([PentahoDoc:BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "Adam Smith"; "Brian Jones") |
NOW | none | The current date | NOW() |
DATE | 3 numeric parameters, year, month, and day | A specified date | DATE(2008;4;15) |
DATEVALUE | 1 text parameter "year-month-day" | A specified date | DATEVALUE("2008-04-15") |
CASE | 2 or more parameters | Evaluates the first, third, etc parameter, and returns the second, fourth, etc parameter value | CASE( |
COALESCE | 1 or more parameters | returns the first non null parameter | COALESCE( |
DATEMATH | 1 expression parameter | returns a date based on an expression. Provide link to org.pentaho.pms.mql.DateMath javadoc. | DATEMATH("0:ME -1:DS") - 00:00:00.000 of the day before the last day of the current month |
...