SharePoint 2010 - Connecting a KPI Scorecard to Display Charts all via Excel Services

Where I work, the executives receive a sales summary report that shows current sales totals, compares with the previous months sales totals, and also YTD totals.. some pretty standard stuff.  With our SharePoint implementation, I'm trying to web enable the report to provide some rich features to the report.  Once such feature is adding a chart.  The current report layout doesn't provide much space for performance charts (other than some Sparklines), but thankfully, the web is made for this stuff.  Currently, I already allow a user to view a sales performance KPI scorecard.  Additionally, my report is uploaded into SharePoint so I canus it as an Excel Services data source for my dashboard.

So my end goal here was pretty simple. What I wanted to add was the ability to choose a row - which contains my sales territories - and have the appropriate regional sales chart.  Sound's simple enough, right?  It is, but using Excel Services caused a few wrinkles, which we'll discuss in this blog post.
(This article assumes you already have a KPI scorecard published to your Dashboard.  If you don't, go ahead and do that first.  There's lots of stuff you can search for, but here's a good starting point: http://blogs.msdn.com/b/performancepoint/archive/2011/09/28/creating-simple-budget-kpis-from-sharepoint-lists.aspx)

Step 1 - Publish your data
The first step to this scenario is to make sure you have your chart's published.  In Excel, create your charts, and name them appropriately.  Then, save them to SharePoint.  You'll need to know your chart names for our step's down the road.
For more information on publishing data from Excel to SharePoint, the fine folks at Microsoft did a good job  explaining this:
http://blogs.office.com/b/microsoft-excel/archive/2009/11/23/uncovering-publish-to-excel-services-in-excel-2010.aspx

Step 2 - Create A List
OK, we need a list for this process.  Why?  Well, you'll see in the steps below that we need a way to MAP our sales territory name to a territory chart.  This list will provide that purpose.

• In SharePoint, create a new Custom List - call it whatever you want.  If you want to copy me, call it Chart Lookup. (Real creative, eh?  That's what you get for copying me). 
• Go into the List Settings, and change the  name of your TITLE column to "Territory". 
• Here's the cool part.  Now, add another column, and for type, choose Picture or Hyperlink.  Name it something (I called mine IMG), and for "Format URL", choose "Picture". 
• Why do this?  When the list renders, the URL to your image will render as well.. so you're essentially providing the source for an image tag.

Click OK, you're done editing your list.

Step 3 - Fill Your List with Data Goodness

What good is a list if it has nothing to list!?

• Add a new item to your list, for the Territory, give it your Sales Territory Name. 
• IMPORTANT: The name you provide here need's to match your Territory name from your KPI scorecard.  So if your Territory list's as "North West" on your Scorecard, enter North West.
• For the Image URL, use your Excel REST URL to the appropriate Territory chart.  (If you don't know what your REST URL is, start here: http://blogs.office.com/b/microsoft-excel/archive/2009/11/09/excel-services-in-sharepoint-2010-rest-api-examples.aspx) 
• As an example, my REST URL is http://intranet.company.org/BI/_vti_bin/excelrest.aspx/Excel%20Data%20Sources/Report.xlsx/model/Charts('Chart_Territory_NorthWest')?$format=image

You entered your Territory and a URL to your chart, that's all there is to it.  Just wash, rinse and repeat for every territory.

Step 4 - Create a view to view the charts you want to view
I want you to trust me on this... open SharePoint Designer.  I promise, this won't be that bad. 

• Once within SharePoint Designer, open your BI site, choose File, Add Item, and select More Pages. 
• Add an ASPX page, and give it a name (mine's regional charts)
• If the page doesn't automatically open in Edit Mode, choose to edit the file. 
• If you receive the message "This page does not contain any regions that are editable in safe mode.  Do you want to open this page in advanced mode?"  Click Yes.
• Click the Insert Tab, and choose Data View
• Under Data Sources, choose the List you created in Step 2

At this point, you should now see your chart's show up within SharePoint designer.  Now we have a view, linked to a page, within your BI site.  All we need to do now is filter the chart that you show.  The following step's were followed from Jason Burns, who did an excellent job demoing this feature at TechEd 2011 - http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI321?format=auto - start at 27:00, but really the whole presentation is worth watching.

Step 5 - Add a filter to the view to only view the chart you want to view

• Still within SharePoint Designer, choose Filter within the Ribbon and add a new filter.
• For Field Name, choose Territory
• Set Comparison to Equals
• Open up the drop down list for Value and choose Create a New Parameter
• Enter a name for your parameter, such as Territory
• Set Query String as your Parameter Source
• Important: For Query String Variable, enter the value EndPoint_URL.  By default, this is how SharePoint chooses to share data via query string.
• Click OK.

Now, we have a page that displays our territory sales charts, and it's filtered to only show one chart at a time, based on querystring.  Go ahead and give it a shot in your browser, browse to the page you just created, and add ?EndPoint_URL=North West (or whatever territory you want to show).  You should only see the chart for that particular territory.

Step 6 - Add your filtered page to your Dashboard.
We're in the home stretch, all we have to do now is wire our scorecard to our filtered page.

• Open up Dashboard Designer for your BI site. 
• Choose the create tab, and choose Other Reports.
• For the report template, choose Web Page
• Within Report Settings, enter the URL to the page you just created in SharePoint Designer, WITHOUT the EndPoint_URL parameter... as I said above, SharePoint add's that for you! 
• Save your "report"
• Find the Dashboard page with your KPI scorecard, and add the web page report to the page.. any zone is fine.

Step 7 - Connect the parts, fa-la-la-la
At this point, you have a KPI scorecard, and a report that displays a chart based on a querystring parameter.  Now we'll just add a connection from your scorecard to the report, so the appropriate report will show when a territory's row is clicked (or tapped, if we're using a tablet).

• On your dashboard page, hover over your scorecard and find the connection DISPLAY VALUE under ROW MEMBER - this is important, you want the Display Value for Row Member since your territory name is your trigger.
• Click and drag that connection to your Web Page report.  You won't have any choice here, the value will want to connect to the report's URL.

That's it, just click OK, and now you have a web part connection.  Save all your dashboard changes, publish your dashboard, and select a territory.  You should see your chart's change based on the territory selection.