Examples of pre-aggregate report expressions
This table shows examples of pre-aggregate report expressions and their logic query:
| Example type | Expression | Logic query |
|---|---|---|
| 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 Note: This example shows
both a custom measure and the Aggregate Rule operator comparing SUM and
AVG.
|
Sum: SUM([Order_Date: Sum: Quantity]*2) AVG: 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] |
Examples of Pre-Aggregate Report Expressions
Custom Attribute / Custom Group Example
Example Expression
IIF([Products.Product Category] IN ('Desktops','Notebooks'),'Computers Group','Other Electronics Group')
Logical Query
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
Expression
IIF([Products.Product Category] IN ('Desktops','Notebooks') AND [Stores.Store Name] = 'Mango Online Store', 'Computers from Online Store','Electronics from other Stores')
Logical Query
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
Expression
IIF(Sum([Inventory_Date: Sum: Current_Inventory]) > 5000, 'More', 'Less')
Logical Query
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 Expression
SUM([Order_Date: Sum: Quantity]*2)
AVG Expression
AVG([Order_Date: Sum: Quantity]*2)
Logical Query
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
Expression
Sum([Order_Date: Sum: Unit Cost]*[Accounts.Upsell Likelihood])
Logical Query
SELECT USING OUTER JOIN Sum([Order_Date: Sum: Unit Cost]*[Accounts.Upsell Likelihood]) 'COL0' , [Products.Product] 'COL1' FROM [ALL]