<< Using IT Analytics to Create Custom KPIs and Scorecards in SharePoint 2010 PerformancePoint Tips and Tricks - Validating Your System Center 2012 Migration >>

Using IT Analytics KPIs within Excel Scorecards

By Rob Reyes at 1/11/2012 7:01 PM
Filed Under: IT Analytics, Key Performance Indicators, PerformancePoint, Scorecards, Excel

In our previous two posts, found here and here, we showed you how to create and present KPIs in a number of different ways. In this last article of our KPI series, we will show you how to present these KPIs by creating a scorecard in Microsoft Excel 2010.

 

Click to view larger image

 

Business Need

Management has been very excited with the number of new reports available, along with the speed in which these have been made available.  There are some additional users that are offline and would like to view this data in Excel.

 

Create the Pivot Table View in Excel

We will start by creating a Pivot table view that filters on the Windows 2008 Monitoring Management Pack and the Counter Name "%Free Space." We then add "Average" as our measure, filter on the servers we are interested in and add  "Entity - Name" to see the average value for each associated disk drive.

 

 

We now click on the "Export to Microsoft Excel" button to recreate our pivot table view in Excel.

 

Click to view larger image

 

With the pivot view ready, we have access to all the dimensions and measures within Excel.  We will add the KPI Trend measure to the spreadsheet, rename the column to "Trend" and add some formatting.

 

Click to view larger image

 

Create the Scorecard

Now that we have the data, we can focus on formatting the scorecard.

 

First, we will add indicators to the "Average" measure.  We highlight the average values in the "B" column and click on "Conditional Formatting" and select "Shapes."  These shapes are then automatically added to the cells we had highlighted.

 

Click to view larger image

 

We will next select on "Manage Rules" under "Conditional Formatting" and click "Edit Rule".  For this example, we select the third option so the shape formatting is applied to all "Average" fields.  The last change is regarding the scale for the icon values.  We change the values to match the thresholds we defined in our SSRS report and change the type to "number" since the "Average" is already set as a percentage.

 

 

Below is a screenshot of how our scorecard is looking.

 

Click to view larger image

 

Next is to format the "Trend" column by selecting the trend value in column "C."  We click on "Conditional Formatting" and click on directional shapes.

 

Click to view larger image

 

We then go to "Manage Rules" and click on "Edit."  We select the third option to apply this formatting to all trend values.  We check the box "Show Icon Only" and leave our values and percentages at default.

 

 

After some additional formatting to the spreadsheet, we have our final scorecard.

 

Click to view larger image

 

Summary

IT Analytics has made it extremely easy to find data, allowing us to focus on presenting data in a number of formats.  We were asked to create a scorecard in Excel so even more users could view the data.  We built out a pivot table view, exported this view in to Excel and added formatting to the pivot table view.  In Excel, a multitude of users can view the latest information quickly, and act accordingly.