SSAS/MDX FAQ 
MDX FAQ MDX-How do you format or round KPI expression value?
MDX FAQ MDX-How do you format or round KPI expression value?
| MDX-How do you format or round KPI expression value? |
| 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:
|
Comments (3)
Most read
- How to install Adventure Works SQL DW and Analysis Services 2005/2008 sample database and project
- MDX-How can I get Last (Previous) Year to Date (YTD) values?
- What TCP port SQL Server Analysis Services 2005 uses
- MDX-How do you format or round KPI expression value?
- MDX-How do you calculate monthly average of one year, optionally including empty months?
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
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.
Note: We are using PeriodToDate function