MDX-How do you format or round KPI expression value?
User Rating: / 13
PoorBest 
Written by Vidas Matelis   
Monday, 07 May 2007 18:17

Q: How do you format KPI expression value?

A: There are few ways you can format KPI expression value:

  • Define calculated measures and use them in Value or Goal expressions. Format measures value using FORMAT_STRING. Note that creating separate calculated measure for each KPI expression (Value, Goal, Status and Trend) is highly recommended. When Analysis Services finds in KPI definition expression that is more than reference to another measure, then SSAS creates hidden calculated measure. If you create calculated measure, you have more control on formating and how it is calculated - for example you can use SCOPE to improve calculated measures performance.
  • In KPI expression definition you can use format function to change how value is displayed. Example:

Format( [Measures].[a] / [measures].[b], "#,##0.0000")

  • Use ROUND function:

ROUND( [Measures].[a] / [measures].[b], 2)

< Prev   Next >
 
Comments (3)
3 Tuesday, 08 April 2008 07:26
RaviKumar
Hi All..

I have worked with the Solution given by "Vidas Matelis ".. and it was worked..
I have created this KPI in my system AdventureWorks Example which is in the Path

"C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Enterprise"

We have also a similar problem..
which is having KPI with Calculated member..

Thank You Once again,
B.V.A.Ravi Kumar
2 Tuesday, 08 April 2008 02:05
admin
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.
1 Monday, 07 April 2008 15:25
Nanda
Can you give us an example of using calculated member, we have created a member but still the data is not getting displayed we are not sure what the problem.
Note: We are using PeriodToDate function

Tags: faq, kpi, mdx
 

Data Warehouse Explorer