SharePoint 2010 | SQL Server 2008 | SQL Server 2008R2 | SQL Server 2012 (Denali)
Early this year I stumbled across a very interesting Windows desktop gadget, that is capable of showing an Excel Services element on the Windows desktop. Here, element can be a certain named region, a pivot table or a chart in an Excel Services document. The technology for showing this element is the Excel Services REST API (REST stands for Representational State Transfer).
You can find the article about the desktop gadget as well as the download link for the gadget itself here. Installation and configuration of the gadget is pretty well explained on the linked site, so I can keep this short here.
After adding the gadget to your desktop, the gadget still needs to be configured.
By clicking on the gadget configuration icon, the configuration dialog is displayed:
| Workbook |
The URL to your Excel Services workbook, for example
http://srv1/PowerPivot/Gadget.xlsx |
| Show in gadget |
Here you can pick from any named region, pivot table or chart that should be displayed in the “expanded” state of the gadget (flyout) |
| Thumbnail |
Here you can pick from any named region, pivot table or chart that should be displayed in the normal state of the gadget. This is what you see on your desktop first |
| Refresh |
refresh interval of the gadget |
So the simple idea is to create a nice pivot table and to use this as the basis for the gadget. For my example I created a simple pivot table based on the Finance perspective of the Adventure Works OLAP cube. For the pivot table I have the year on the filter, the sales amount and the operating profit KPI as the data and the departments on the rows.
If we use this large table in our gadget, the thumbnail view gets pretty much “microscopic”:
So, for the thumbnail (default) view you should choose a much smaller area. The flyout (detail view) is much better, but the KPI indicators and the filters are not shown:
So, here are a few tips and tricks you can use to make the gadget look nicer.
1. Use time filters for current year, current month etc.
Of course we want our gadget to always show the latest values. Because of the auto refresh interval, we don’t have to care about this. But usually we will also use a time filter to restrict the data to a specific week, month etc. Since we don’t see the filter, the idea is to have this set automatically to the current time.
This can be easily done in the Excel Pivot Table by using date filters. Unfortunately, these filters don’t work in the filter area, so we have to place the time hierarchy on the rows or columns. Then we can apply a filter (for example current year) as shown here:
For Adventure Works this would not result in any data since the sample date is only available for the years shown in the screenshot. However, in real life scenarios, this would be a good choice for the filter.
2. For thumbnail view, convert the pivot table to formulas
Formulas are much easier to handle and to format, compared to a pivot table. Remembering the very narrow available space for the thumbnail view, having full control of the layout is important. You may even want to hard-code the thumbnail view. Let me explain what I mean. In order to have the connection name at a single place, we start in a blank sheet by putting the connection name in a named cell called “OLAPConnection” (feel free to choose a different name):
As the next step, let’s construct the current date member. From Management Studio, we can see that the MDX name of the time members looks like this (may be different in your cube, this example is taken from the Adventure Works sample database):
- [Date].[Calendar].[Calendar Year].&[2001]
- [Date].[Calendar].[Month].&[2001]&[1]
- [Date].[Calendar].[Date].&[20010101]
Here we have January 1, 2001. This is easily constructed using Excel’s time functions. So we simply add these fields to our Excel sheet
| Label/Name |
Cell |
Formula |
| Today |
B3 |
=Now() |
| Year |
B4 |
="[Date].[Calendar].[Calendar Year].&[" & Year(B3) & "]" |
| Month |
B5 |
="[Date].[Calendar].[Month].&[" & Year(B3) & "]&[" & Month(B3) & "]" |
| Day |
B6 |
="[Date].[Calendar].[Date].&[" & 10000*Year(B3)+100*Month(B3)+Day(B3) &"]" |
Of course you could also add fields for the previous month, the week etc., just depending on the needs of your scorecard. I named the cells B4 as MDXYear, B5 as MDXMonth, B6 as MDXDay. This is how the result looks like:
I had to fake the current date in order to see some values. Therefore I replaced the formula for today with this one:
=Date(2004, Month(Now()), Day(Now()))
Of course, you won’t want to do this in a real life scenario but since the sample dataset contains no data for 2011 I had to use this “time machine formula”.
Before you start wondering what all this is good for, let’s query some data. For example, let’s assume that we want to see the operating profit (which is on the account ‘Operating Profit’) for the current year. So this would by our Excel formula:
=CUBEVALUE(OLAPConnection,"[Measures].[Amount]","[Account].[Accounts].[Operating Profit]",MDXYear)
In order to show this value in our gadget, I placed it on a new sheet and adjusted the column width and height a little bit.
In order to get a nice flyout, I also created a simple pivot chart in the Excel sheet, showing the operational profit during the year. After saving the Excel file, our gadget now looks like this:
And here is the flyout:
3. (Conditional) background colors and fonts are preserved in the REST API
In order to include a kind of traffic light approach, we already found out that Excel indicators are not yet supported in the REST API. However, conditional formatting is supported, so you can easily create a scorecard like the follow:
Also KPI indicators can be created using special characters, for example from the WingDings font as shown in the following example:
For this example, I used a separate table of banding ranges and an Excel VLookup to find the appropriate color for the indictor. Here, 1=green, 2=yellow, 3=red. In the Excel cell I used conditional formatting to choose the text color appropriately. But as we always want to display a certain element (here, the diamond from the WingDings font), I used a custom format for each of the cells, so the number (1, 2 or 3) is not shown but only one character. The corresponding character for the diamond is “u”, so the custom format looks like this:
And of course, there is a lot more you can do with all these formatting, cube functions etc.
3. Make changes to the source code of the gadget
You can extract the gadget or modify the source code of the gadget yourself. After installing the gadget, the extracted sources can be found here:
%LOCALAPPDATA%\Microsoft\Windows Sidebar\Gadgets
After modifying the sources, the gadgets need to be switched off and on in order for the changes to apply. The main file is the gadget.html here. For example you could change the link “By Excel Services” to point on your SharePoint server. To do so, a simple change in the source is needed:
Before:
<tr><td id="dockedTitle" width="100%">
<a id="leftDockedTitleLink" href="http://blogs.msdn.com/cumgranosalis/pages/excel-services-windows-7-gadget.aspx">By Excel Services</a>
</td><td>
After:
<tr><td id="dockedTitle" width="100%">
<a id="leftDockedTitleLink" href="http://srv1/PowerPivot/Forms/AllItems.aspx">All reports</a>
</td><td>
I also changed the background color here, so this is how the result looks like:
As you can see, you can do a lot of interesting things with this simple but very powerful desktop gadget.
4. Use parameters
If you want to use the same Excel Services file for more than one user you may want to pass parameters from each individual instance of the desktop gadget to the Excel Services file. This is also possible, however there are some things to take care of.
The syntax for passing a parameter is
Ranges(‘cellname’)=value
Here, cellname is a named cell in Excel that we want to pass the value to. This is how it is entered in the desktop gadget, if our named cell is named ‘value1’ and we want to pass ‘xyz’:
Multiple parameters may be passed by separating them with an ampersand, for example
Ranges(‘value1′)=xyz&Ranges(‘value2′)=42&Ranges(‘value3′)=01
This could be our corresponding view in the desktop gadget:
I just added a field that concatenates all three parameters to show that the Excel Services sheet is recomputed based on the passed values.
As you can see, all parameters are passed to the gadget. However, numeric parameters are considered as numbers in Excel, so 01 was changed to 1. If you want to prevent this, add a ‘ in front of each text.
This is how the parameters should look like when you want to make sure, text is passed as text (and not converted to a number or date) in Excel:
Ranges(‘value1′)=‘xyz&Ranges(‘value2′)=42&Ranges(‘value3′)=‘01
You should also be aware, that this string is passed to the URL “as it is”. So you have to encode all characters that are not allowed in a URL.
For example, if you want to pass the year 2006 from AdventureWorks, the unique name of the date member would be:
[Date].[Calendar].[Calendar Year].&[2006]
In order to pass this to the gadget, you have to encode blanks, the square brackets and the ampersand. So the result would look like this:
Ranges(‘parDate’)=%5BDate%5D.%5BCalendar%5D.%5BCalendar%20Year%5D.%26%5B2006%5D
This is very difficult to read. Therefore I recommend just to pass the key (2006 in this case) as the parameter and to construct the unique name in Excel using a formula like
="[Date].[Calendar].[Calendar Year].&[" & parDate & "]"
Of course, there are a lot more things you can do with this simple, yet powerful Windows desktop gadget. Like with most other gadget you may also place more than one instance of the gadget on your desktop, so you can have different scorecards. You can also add links to your Excel sheet, so that you can jump directly to a dashboard for a specific key performance indicator. Just start playing with the gadget and see how powerful und useful it is.
