It is sometimes desirable to choose a different metric dynamically depending on the database table against which the Strategy SQL Engine issues SQL if the two tables contain a column with the same name with values at different levels. A practical example would involve the Sum() and Count() metrics where users may want to issue Count() against the column in a detail level table while they may want to issue Sum() against the same column in the corresponding aggregate level table.
This can be accomplished using a combination of ApplySimple() and ApplyAgg() functions at the Fact and Metric definitions, respectively. The following example is created within the Strategy Tutorial project, specifically using the ORDER_DETAIL and ORDER_FACT tables.
Create a Fact using two expressions with ApplySimple() definitions as follows:
Fact_Qty Sold: ApplySimple("Count #0", QTY_SOLD)
against the ORDER_DETAIL table:
Fact_Qty Sold: ApplySimple("Sum #0", QTY_SOLD)
against the ORDER_FACT table:
Create a metric using the ApplyAgg() function on Fact_Qty Sold as follows:
M_Qty Sold:ApplyAgg("#0", Fact_Qty Sold)
The Strategy SQL Engine then correctly selects the underlying form expression for the Fact depending on the corresponding source table:
| |
select a11.ORDER_ID ORDER_ID, |
|
select a11.ORDER_ID ORDER_ID, |
|
