KPI Example using calculated member and
KPI Example using calculated member and PeriodToDate:
1. In cube MDX script editor add 2 calculated measures. One for KPI value and another one for KPI goal. Example:
-- KPI values and goals
-- KPI value = current year to date [Reseller Order Quantity]
Create Member CurrentCube.[Measures].[Reseller Order Quantity YTD_Value] AS
SUM( PeriodsToDate([Date].[Calendar].[Calendar Year]
, ClosingPeriod([Date].[Calendar].[Month]
, [Date].[Calendar].[All Periods])
)
, [Measures].[Reseller Order Quantity]
)
, FORMAT_STRING = '#,##0' -- Here is your formating
, VISIBLE=0;
-- KPI goal = previoius year to date [Reseller Order Quantity].
Create Member CurrentCube.[Measures].[Reseller Order Quantity YTD_Goal] AS
SUM( PeriodsToDate([Date].[Calendar].[Calendar Year]
, ParallelPeriod([Date].[Calendar].[Calendar Year]
, 1
, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods])
)
)
, [Measures].[Reseller Order Quantity]
)
, FORMAT_STRING = '#,##0' -- Here is your formating
, VISIBLE=0;
2. In KPI Tab create new KPI: "Reseller Order Quantity YTD"
3. For new KPI change associated measure group to "Reseller Sales".
4. For new KPI in "Value Expression" field specify: "[Measures].[Reseller Order Quantity YTD_Value]"
5. For new KPI in "Goal Expression" field specify: "[Measures].[Reseller Order Quantity YTD_Goal]"
6. Specify calculation for "Status Expression" and "Trend Expression". For this test purpose I put in value 1.
7. Save and deploy changes. Enjoy KPI values that are formated to your specification.