Creating a Trend Indicator for a KPI in a Scorecard
Creating a Trend Indicator for a KPI in a Scorecard
Trend indicators can be created in your scorecards as an additional target value. Additionally, if you are importing SSAS 2005 KPIs, trend arrows are created for you automatically, if the KPI you are importing has a trend associated with it. A scorecard is shown below that contains KPIs based off of measures from an Analysis Services cube, with an additional target column added for trend.
To add a trend to an existing KPI - not based off of an SSAS 2005 KPI, follow the below steps:
1. Select the KPI in the workspace browser.
2. Click New Target to add a new target to the KPI.
3. Assign a name, Trend, to the target and click on the 1 (Fixed Values) link in the Data Mapping column.
4. Click Change Source and select your data source from the list of data sources, click OK.
5. Map the trend target to your trend value - note, if you don't have this value already as a calculation in your cube you can select the Use MDX tuple formula and enter MDX for the calculation as follows:
(([Time].[Calendar].CurrentMember, [Measures].[Units])-([Time].[Calendar].CurrentMember.PrevMember, [Measures].[Units]))/([Time].[Calendar].CurrentMember.PrevMember, [Measures].[Units])
6. This has specified the trend value that will show in the scorecard. Click OK.
7. Click on Select scoring pattern and indicator.
8. Select the appropriate scoring patter, in the above sample, we select Increasing is Better.
9. Select Band by stated score (advanced) for the banding method. Click Next.
10. Select your trend indicator - note, if you haven't already added a trend indicator to your workspace, you will need to right click on
11. Click Next.
12. Click on Specify Data Mapping.
13. Select Change Source and select your data source from the list of data sources, click OK.
14. Select Use MDX tuple formula, enter the above formula again.
15. This has specified the value that will be used for scoring for the band by stated score, it can be different from the value specified in the trend target value - but in this case we use the same number. Click Finish.
16. Either enter new threshold setting or use the default ones.
17. Now, click on the Display link for Trend - you may want to adjust the format. In our case, we will multiply by 100 and assign a % symbol at the end.
18. Repeat for each KPI you wish to add a trend to.
19. Now, construct a scorecard from these KPIs, make sure and include Time periods in your scorecard on the rows or columns so we can evaluate the trend expression.
As mentioned previously, trend indicators automatically come in for SSAS 2005 KPIs, note that they will come in with default names of Value, Goal and Status and Trend. So if you wish to use them in the same scorecard as other KPIs, you need to rename the Value and Goal and Status labels.
To create KPIs off of SSAS 2005 KPIs, run through the scorecard wizard and make the selection to Import SSAS 2005 KPIs. Once imported into a scorecard and after renaming as mentioned above, you can use these KPIs in scorecards already created as shown below.
Alyson Powell Erwin (alysonp@microsoft.com)