Share via

Creating simple budget KPIs from SharePoint Lists

  • Article

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.

clip_image001

I launch Dashboard Designer from the Business Intelligence site template homepage. First I create a new data connection using the SharePoint List template:

clip_image002

After the data source appears in Workspace Browser I give it a name and point it towards the specific list in the site:

clip_image003

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:

clip_image004

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:

clip_image005

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:

clip_image007

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:

clip_image008

To change these I need to edit the KPI. I select it in the Workspace Browser, and then change the number format and thresholds:

clip_image010

That's better!

clip_image011

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:

clip_image012

After I click Update in the Edit tab of the ribbon, here's the result:

clip_image013

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.html

  • Anonymous
    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, Venkata

  • Anonymous
    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 removed

  • Anonymous
    October 12, 2015
    The comment has been removed

  • Anonymous
    October 12, 2015
    The comment has been removed

  • Anonymous
    October 12, 2015
    The comment has been removed

  • Anonymous
    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 removed

  • Anonymous
    October 13, 2015
    The comment has been removed

  • Anonymous
    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.com

  • Anonymous
    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.