Share via

Displaying Dates in a Scorecard

  • Article

It frequently comes up that you may want to show a date in your scorecard, either as an actual value, a target value or just as an extra piece of information such as when was this KPI last updated in the source. One way that you can display dates in your scorecard is to make use of the MDX "Properties" expression.  Note, that this will only work for data being sourced from an Analysis Services cube.   See the simplistic example below.

For this example, I will simply show a date as an extra piece of information.  In this scorecard, I will be showing my top 20 selling products and I want to see the date that the product was first introduced alongside of the sales information. First, I construct my KPI as follows.  I create the Actual and Target values as I normally would, then I add a new Target, which I will call "Intro Date".  For the intro date value, I create it using an MDX expression as follows:

clip_image002[5]

Make sure and set the calculation column to either "Default" or "Source data", otherwise, nothing will appear when you first look at your results.

Note that I have a cube with an existing Member Property on the SKU level of my product dimension that is "Intro Date".

Then, when I construct my scorecard, I will place SKUs on the rows of my scorecard using a custom formula to give me the top 20 and I will need to drag the "Intro Date" metric out to the scorecard as well if it doesn't automatically appear.  This results in the following scorecard:

clip_image004[7]

This easily enables me to see the intro date in my scorecard as an additional target value.

It is possible to show a date as an actual value in this same manner, but remember, the dates have to be pulled as a member property of a dimension member, this makes construction of the cube and KPI a bit more difficult.  If you did choose to create a KPI with an actual value that is a date and a target value that is a date, you would have to score the KPI using "Band by Stated Score" and your calculation expression would have to resolve to a number in order to be banded appropriately as we can only band numerically.  For more information on banding by stated score see the following post.

Hope this helps. 

Alyson Powell Erwin

alysonp@microsoft.com

Comments