Creating simple budget KPIs from SharePoint Lists
In this post we're going to walk through how our favorite fictional company Litware uses PerformancePoint Services with SharePoint list data to do simple "under budget" KPIs.
In Litware's party planning committee we log how much we spend on events using a SharePoint list. Corporate's creating a corporate balanced scorecard using PerformancePoint Services, and they've asked us to contribute a KPI showing how we're doing. For now our KPI will be based on the data we have, and the rules will be >100% budget is warning, >120% budget is bad. Later we can swap in other sources of information, for example employee anticipation around the next golf scramble, into the KPI's definition to make it a more accurate or more predictive metric, and the changes will flow into the higher level scorecard that uses the KPI.
Here's a sample of our spending history list. Let's create the KPI and scorecard from this point.
I launch Dashboard Designer from the Business Intelligence site template homepage. First I create a new data connection using the SharePoint List template:
After the data source appears in Workspace Browser I give it a name and point it towards the specific list in the site:
Very important: based on the settings above, the unattended service account for PerformancePoint Services must have access to the list. I can find out if it does by clicking "Test Data Source."
I'm done with the data source, so I save the data source to SharePoint:
Now we're ready to build the KPI and a test scorecard. I create a scorecard in PerformancePoint Content using the SharePoint List template; this will create both our KPI and the test scorecard:
Next I select the data connection we created. Then on the next screen I add a KPI, select the columns representing Actual and Target, and select "Closer to Target is Better" for some flexibility later:
I click "Next" through the rest of the wizard to get a KPI and scorecard added to the workspace, and I see what I have so far as the scorecard updates. The exact rules haven't been set yet (remember we're after >100% budget is warning, >120% budget is bad) and I'm going to get a stern warning if I format dollars with this precision:
To change these I need to edit the KPI. I select it in the Workspace Browser, and then change the number format and thresholds:
That's better!
Although corporate probably won't care about this detail when they integrate the KPI into their scorecard, I can break the number down by the individual event titles, or any other column in the list, by dragging the field into the scorecard.
Tip: I use the "Named Sets" instead of "Dimensions" so that new rows in the SharePoint list will automatically show up in the scorecard:
After I click Update in the Edit tab of the ribbon, here's the result:
I save both the scorecard and KPI in the workspace, and now I can put it into SharePoint using the PerformancePoint Services Scorecard webpart. I think I'll touch up the test scorecard for use by our team, and I’ll tell corporate my work here is done; they can find the KPI for broader use at https://intranet.litware.com/corp/hr/partyplanning/Lists/PerformancePoint%20Content/
Wade Dorrell
Program Manager, Office BI
Comments
Anonymous
January 19, 2012
Thanks for sharing this...helped me really a lot....Anonymous
January 23, 2012
Good introduction! Thanks for sharing!Anonymous
June 06, 2012
When I pick my SharePoint list as KPI datasource "Select a measure" dropdown list does not display any column. What am I doing wrong ?Anonymous
June 26, 2012
What I have been fighting is the negative numbers in the Target field. If I am over I want negative but if I am under I would want positive numbers. I have used other calculations in the KPI and get totally obscurred numbers when using scoring patterns of Decreasing is Better or Increasing is Better. Is there a simple method when you have a target value that you are trying to meet for the year, and to determine if you are positive or negative that target? Like in this example are you above or below your target.Anonymous
November 07, 2013
Here one more article to explain how to create KPI's using dashbord designer in SharePoint 2013 www.dotnetsharepoint.com/.../how-to-create-kpis-in-sharepoint.htmlAnonymous
March 06, 2014
In the above article, i have done all the steps. but i am facing a issue in the "Named Sets" Tip: I use the "Named Sets" instead of "Dimensions" so that new rows in the SharePoint list will automatically show up in the scorecard: When i drag and drop for "Title All" to "Party Planning" and update it. But it is showing only one title. Please help me out on this Thanks, VenkataAnonymous
July 23, 2014
I was able to follow through the post but I want to have total three level drill down for budget vs actual. Any help would be appreciated.Anonymous
October 12, 2015
The comment has been removedAnonymous
October 12, 2015
The comment has been removedAnonymous
October 12, 2015
The comment has been removedAnonymous
October 12, 2015
The comment has been removedAnonymous
October 12, 2015
Get the fastest 3G service in Bangladesh from Grameenphone! Also, get more value for your money with unbeatable prepaid & postpaid deals in nation’s best network!Anonymous
October 12, 2015
You will gate here best service for telecommucication. to knoe more about it vesite our site.Anonymous
October 13, 2015
The comment has been removedAnonymous
October 13, 2015
The comment has been removedAnonymous
October 18, 2015
ONE NEWS - Worldwide Bangla News .1 news is a worldwide bangla news site.where you will get all the bangla news with most important world news.we assure that we will help you to get and view news to know the world information.<a href="https://www.1newsbd.com">1news</a>Anonymous
October 18, 2015
ONE NEWS is a Worldwide Bangla Online News site. Where you will get all the bangla news with most important world news.We asure that we will help to get and view news to konw the world information. https://www.1newsbd.comAnonymous
October 19, 2015
where to get backlins. Backlinks enable you to keep track of other pages on the web that link to your posts. For instance, suppose Alice writes a blog entry that Bob finds interesting. Bob then goes to his own blog and writes a post of his own about it, linking back to Alice’s original post. Now Alice’s post will automatically show that Bob has linked to it, and it will provide a short snippet of his text and a link to his post. What it all works out to is a way of expanding the comment feature such that related discussions on other sites can be included along with the regular comments on a post. Once everything is set up, you’ll see a new link marked ’Links to this post’ next to the comment link for each post. If you click that link you’ll be taken to the post page, where the backlinks are all listed beneath the comments. Clicking the triangles next to each link will display a snippet of text from the page linking to you, as well as some author and date information.