In a recent MPUG Presentation, “Reports, Dashboards, and all that Jazz,” I covered building a Resource Usage report using the OLAP Cubes.
As a follow-up to that presentation, this article provides a step-by-step approach to building a very similar report.
In building this report, we will be using an Excel add-on called OLAP Pivot Table Extensions, which will allow us to easily perform calculations in an OLAP Pivot Table. It was downloaded from CodePlex. Personally, I have found this add-on very useful for almost all Pivot-Table-related reports.
Also, if you are not familiar with using Pivot Tables, I suggest that you get yourself primed up on that topic.
Assumptions:
- I will assume in this report that your Analysis Services Cubes have been built, are operating successfully, and that you have the necessary permissions to author and publish reports to your Business Intelligence Center
- The steps detailed below are possible only with versions of Excel 2007 or higher.
To get started use the following navigation:
PWA >> Business Intelligence Center >> Templates >> Your OLAP Cubes
From the list of the cubes, select the OLAPPortfolioAnalyzer cube.
This will open a template in Excel.
Now drag and drop the fields in the Pivot Table Area as shown in the picture below.
This will yield a Pivot Table like the one shown below.
Now let’s try to show % Usage instead of hours. To achieve this, we will need to calculate this value. This is where the OLAP Pivot Table Extensions will come into the picture.
Let us define % Usage as (Work/Capacity).
Right-click on the Pivot Table and select OLAP Pivot Table Extensions.
This will open up a dialog box like the one shown below.
Now write the formula we agreed upon above (shown in the picture below) and click Add to Pivot Table.
This will add the % Usage field to the Pivot Table.
Now let’s adjust the number format and add some color to the table. We might also remove the Capacity and Work columns, if we want to look at only the % Usage. You can remove any column from the Pivot table by dragging the column out of the Field List.
Now all that is left is to use conditional formatting to highlight the outliers. If we agree on the following colors:
50-100% = Green
>100% = Red
0-50% = Yellow
NA = Gray
then we can use Conditional Formatting as shown below:
From the Home tab, click on the Conditional Formatting button, and select Manage Rules
This will open the Conditional Formatting Rule Manager dialog box as shown below.
Click on New Rule to start building a new conditional formatting rule.
Select the Options, and set the format for Rule 1 (mentioned previously in the article).
Repeat the same steps for each other color. You will end up with rules as shown below:
and the results on the Pivot Table will appear as shown below:
There you have your Heat Map!!
Now, if you want to publish this to SharePoint, all you have to do is select the Save to SharePoint option while saving, as shown below.
This will make the report available in Excel Services.
Note: If you are using Excel 2007, the option to publish this to SharePoint would be Office Button >> Publish > Excel Services.
