Firstly, I would like to thank all those that came along to my sessions at the Australian and New Zealand SharePoint Conferences in Sydney and Auckland. I am a big fan of empowering end-users and wanted to show a realistic example of what was possible so the level of interest and number of relevant questions made it all worthwhile.
So, my scenario is that I have a country manager who wants to see a dashboard of his team performance against controlled targets. Given that I am in Australia I will stick with a local example so the team are state managers. The country manager is a competent Excel user and wants to keep as much control over the chart types etc as possible. The state managers should be able to input their results but not change their targets.
To start with we will create our Excel spread sheet – this will work fine with either 2010 or 2013, Excel or SharePoint in any combination.
Create a worksheet called Targets with headings for State, Q1, Q2, Q3 and Q4. Enter your data for each state. Eg QLD, 24, 28, 32, 26. I often use the randbetween() function to create dummy data quickly. Format as a table using your preferred style and name the table Targets.
Create another worksheet called Chart, in A1 type State and in B1 type one of your states eg QLD. Under the Formulas ribbon define a name for B1 called State.
From B2:E2 type Q1 to Q4. In A3 type Target and A4 type Actual. Now we want to get our target values from the Targets table depending on which state is entered into B1. Use a vlookup function in cell B3 as follows =vlookup(State, Targets, 2, FALSE). This will use the defined name State as the lookup value, search for it in the Targets table and return the second column (which is the target for Q1). FALSE means it needs an exact match. Copy this formula for Q2 to Q4 changing the column returned to be 3 to 5 accordingly.
Now enter some dummy data for your Actual values in row 4.
Last step is to create our chart. Select the data in cells A2:E4 and insert your preferred chart. Name the chart as Chart.
All done – you should now have something that looks like this:
If you change the state typed into B1 then the appropriate targets should automatically populate in row 3 and the chart redraw accordingly. If this is not the case, recap the previous steps.
Save your Excel file as States.xlsx and upload to your Docs library in SharePoint. Note that you will need Excel services to be running which also means you will need an Enterprise licence of SharePoint 2010 or 2013. If you are using Office 365 then all should be fine.
Now you have the file in SharePoint we can try out some Excel REST fun. Excel REST allows me to get data or objects from the Excel file using a URL. The URL is constructed as follows:
http:// – the server name
/_vti_bin/ExcelRest.aspx – the Excel REST page
/Docs/States.xlsx – the path to your spread sheet from the site you make the call
/model/Charts(‘Chart’) – the chart (or other object/model) we want to retrieve
The models available are:
Charts
Ranges
Tables
PivotTables
Try this out in your browser window. Type the URL using the elements as defined above:
Eg //server/site/_vti_bin/ExcelRest.aspx/Docs/States.xlsx/model/Charts('Chart')
You should now see the chart from your Excel file in your browser window. If not, then make sure you check you have the correct library name and file name and also the name of your chart in the Excel file. I have used everything from my example spread sheet above so if you have done anything differently then you will need to change the path or names to match.
The chart is returned as an image so this can be used in a variety of ways such as adding an image to a page and entering the URL as above.
So far so good, but we are only using the data within the spread sheet at the moment. To tell the spreadsheet to use different data we add parameters to our URL.
Eg //server/site/_vti_bin/ExcelRest.aspx/Docs/States.xlsx/model/Charts('Chart')?Ranges('Chart!B4')=40
Following on from the example above, this will set the value of the cell B4 in the spread sheet to 40 (the actual for Q1) and redraw the chart before returning the image.
Now if all you wanted was a chart from your list data then you could edit your page and insert a picture from address and use this as the URL. Easy. However, I did promise to mix this with some XSL to give you a dashboard so here goes.
I have created some custom XSL below which as you can see is pretty simple. It creates a table and then for each row put the Title in the first column and then an image in the second. The URL for the image is our REST call but with the values coming from our list columns.
<xsl:stylesheet version="1.0" exclude-result-prefixes="xsl" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row"/>
<table style="width:100%">
<xsl:for-each select="$Rows">
<xsl:call-template name="RowView" />
</xsl:for-each>
</table>
</xsl:template>
<xsl:template name="RowView">
<tr>
<td>
<xsl:value-of select="@Title" />
</td>
<td>
<img src="/sites/SPC2013/IV/_vti_bin/ExcelRest.aspx/Documents/States.xlsx/Model/Charts('Chart')?Ranges('Chart!B1')={@Title}&Ranges('Chart!B4')={@Quarter1}&Ranges('Chart!C4')={@Quarter2}&Ranges('Chart!D4')={@Quarter3}&Ranges('Chart!E4')={@Quarter4}" />
</td>
</tr>
</xsl:template>
</xsl:stylesheet>
Notice that the ampersands separating the URL parameters have to be escaped – ie &
In my example, I have saved this stylesheet to /Style Library/custom/Charts.xsl.
So far so good – now we need a SharePoint list to apply this to. Create a list called States. Rename the Title column to State and create number columns for Quarter1, Quarter2, Quarter3 and Quarter4.
Interesting thing to note here is that if you try to abbreviate your column names to Q1 or Qtr1 you will have trouble with your XSL. For some reason SharePoint does not like column names which are less than 5 characters and so will encode the first character. This makes it pretty horrible in XSL so I suggest you stick with the column names as above.
Add some data to your list and make sure you use the same names for the States.
Now for the fun bit. Create a new view on your States list and call it Dashboard. No need to apply filters but make sure all the input columns are showing. Edit the page in the view and now edit the properties of the web part. In the Miscellaneous section add the URL to your XSL file in the XSL Link box.
Finally the magic bit. Click OK and stop editing the page. You will now see the name of each state followed by a chart redrawn for the data from each item in your list.
Excel REST and XSL – what a great combination!
Download the blog post as PDF – Excel REST Meets XSLT
Download the spreadsheet example – States
Download the XSL stylesheet – Chart.xsl
Good luck and have fun.
