Pre-Aggregate Report Expressions

Right Required: Author Insights

Create pre-aggregate expressions, also called "push-down expressions", in Visualizer. Pre-aggregate expressions are evaluated in the Insights database. These expressions apply at the space level and are disabled by default. Once pre-aggregated expressions are enabled, create custom attributes and measures in the Visualizer Expression Builder dialog. Custom attributes and measures are based on BQLClosed Business Query Language expressions syntax.

Enable Pre-Aggregated Expressions

  1. Select Admin → Manage Space → Space Properties → Push Down Expressions to Database.
    Note:
    • If enabled, the results of existing report expressions change because the expression is now aggregated in the database.
    • The Push Down Expressions to Database feature supports all report expressions except those that include positional calculations and dimensional expressions. To use these, you must disable Push Down Expressions to Database.

Aggregation Rule Operators

For custom measures, aggregation rule operators perform the equivalent of specifying aggregation rules. The entire BQLClosed Business Query Language expression is enclosed using an aggregate rule operator.

SUM([Order_Date: Sum: Quantity]*2)

AVG([Order_Date: Sum: Quantity]*2)

If an aggregate operator is not specified, SUM is used as the default aggregation.

Examples of Pre-Aggregate Report Expressions

Custom Attribute / Custom Group Example


IIF([Products.Product Category] IN ('Desktops','Notebooks'),'Computers Group','Other Electronics Group')

SELECT USING OUTER JOIN IIF([Products.Product Category] IN ('Desktops','Notebooks'),'Computers Group','Other Electronics Group') 'COL0' , [Order_Date: Sum: Revenue USD] 'COL1' FROM [ALL]

Cross-Hierarchy Custom Group Example


IIF([Products.Product Category] IN ('Desktops','Notebooks') AND [Stores.Store Name] = 'Mango Online Store', 'Computers from Online Store','Electronics from other Stores')

SELECT USING OUTER JOIN IIF([Products.Product Category] IN ('Desktops','Notebooks') AND [Stores.Store Name] = 'Mango Online Store', 'Computers from Online Store','Electronics from other Stores') 'COL0' , [Order_Date: Sum: Revenue USD] 'COL1' FROM [ALL]

Custom Attribute Based on a Measure Example


IIF(Sum([Inventory_Date: Sum: Current_Inventory]) > 5000, 'More', 'Less')

SELECT USING OUTER JOIN IIF(Sum([Inventory_Date: Sum: Current_Inventory]) > 5000, 'High', 'Low') 'COL0' , [Inventory_Date: Sum: Current_Inventory] 'COL1' , [Products.Product] 'COL2' FROM [ALL]

Custom Measure with Aggregate Rule Operator Example

This example shows both a custom measure and the Aggregate Rule operator comparing SUM and AVG.


SUM([Order_Date: Sum: Quantity]*2)

AVG([Order_Date: Sum: Quantity]*2)

SELECT USING OUTER JOIN AVG([Order_Date: Sum: Quantity]*2) 'COL0' , SUM([Order_Date: Sum: Quantity]*2) 'COL1' , [Products.Product Category] 'COL2' FROM [ALL]

Custom Measure Based on an Attribute Example


Sum([Order_Date: Sum: Unit Cost]*[Accounts.Upsell Likelihood])

SELECT USING OUTER JOIN Sum([Order_Date: Sum: Unit Cost]*[Accounts.Upsell Likelihood]) 'COL0' , [Products.Product] 'COL1' FROM [ALL]

See Also