Skip to main content

Key Performance Indicators - Create KPI List and indicator programmatically

Scenario

We need to create KPI List and an Excel Workbook indicator programmatically.

Solution

1. Create an Excel Workbook:



2. Upload that workbook to the document library on our Web site:



3. Add trusted location to Excel Services settings:



4. Create a project in Visual Studio which will do the following:


SPListTemplate template = null;
foreach (SPListTemplate t in sweb.ListTemplates) // sweb - SPWeb object that we initialized before
{
if (t.Type.ToString() == "432") // this way we can pickup the KPI List list template
{
template = t;
break;
}
}

Guid gG = Guid.Empty;
SPList list = null;
string sListTitle = "Mighty KPI List";

try
{
gG = sweb.Lists.Add(sListTitle, sListTitle, template);
list = sweb.Lists[gG];
}
catch
{
// exists
list = sweb.Lists[sListTitle];
}

// in our case we need Indicator using data in Excel workbook content type
SPContentType ct = list.ContentTypes["Indicator using data in Excel workbook"];

SPListItem item = list.Items.Add();
item["ContentTypeId"] = ct.Id;

item.SystemUpdate();
item["Title"] = "Mighty Excel KPI";

item["AutoUpdate"] = true;
item["GoalFromWorkBook"] = true;
item["WarningFromWorkBook"] = true;

// data source - in our case it's URL to our mighty Excel workbook
SPFieldUrlValue value = new SPFieldUrlValue();
value.Description = "/Excel Reports/SampleBook.xlsx"; // site-relative URL for description
value.Url = "http://slavamoss:20004/Excel Reports/SampleBook.xlsx"; // full URL for link value
// I don't know why to be honest, but it only works that way :)
item["DataSource"] = value;

// cell addresses for indicator to work
item["ValueCell"] = "Sample!B2";
item["GoalCell"] = "Sample!A2";
item["WarningCell"] = "Sample!C2"

item.SystemUpdate();

5. Run the code and here we go:



Our KPI List is created with the Excel indicator in it!!

6. These are the properties:



Easy :)

UPDATED:

Wylde Solutions released VG Custom KPI View Web Part. This is a Web part that allows you to display key performance indicators using custom images.

Information about the Web part is located at the following link: VG Custom KPI View Web Part

If you have any questions in regard to implementing KPIs and other Business Intelligence features for your company or you need assistance contact us now for a consultation.

Comments

Popular posts from this blog

Setting up External Content Type for SQL Server database using SQL Server authentication - SharePoint 2010 Foundation

This post is a follow up on the issues that I have got setting up External Content Type (ECT) on SharePoint 2010 Foundation that was going to connect to remote SQL Server database for information. I cannot use my SharePoint user accounts to access SQL Server. According to the information I have discovered ECT and Business Connectivity Services are available in the SharePoint 2010 Foundation, but there are some issues if you want to use authentication methods in your external connections that are different from Windows Identity or Current User Identity. This is because there is no Secure Store Service in SharePoint 2010 Foundation which serves as an impersonation hub and is only available in SharePoint 2010 Server edition. The issues are coming from the fact that you can actually create ECT in SharePoint Designer 2010 providing just Secure Store ID and system would ask you for credentials and here you go, but when you try to use your ECT in External Lists or as a lookup columns you w...

SharePoint 2013 Development and Consulting - Laptop & Conferencing Experience with Lync

Have just jumped on a brand new SharePoint 2013 massive Intranet project and because of specific working conditions have found some items that required addressing almost immediately in order to continue the job. Maybe this will be interesting to someone else who is about to start SharePoint 2013 development to have an idea of what might be necessary. I started working with SharePoint using my own infrastructure in 2009. I used iMac with 320GB HDD and 4GB RAM running VMWare Fusion to virtualise Windows  environment ( SQL Server 2008, standalone SharePoint 2007, Visual Studio 2008, Office 2007). It was all-in-one virtual machine. A bit slow, but enough for any SharePoint work that I had at that time. For communication with colleagues and partners we used GoToMeeting . Great tool that worked (and still works) without any problems. Voice, video, screen-sharing - all worked well using built-in audio/video hardware. In 2010 I got a MacBook Pro  with 500GB HDD and 8GB ...

Document Sets - SharePoint 2010 - Part 1

Hi again, in this post I am going to demonstrate how set up and start using Document Sets in SharePoint 2010. In Beta version there was a little problem when working with Document Sets. You could see the discussion around it here: Document Set content type issue . Now it is fixed and I will show you how to set up Document Sets properly to also use Keywords. 1. Activate two site collection features - Document Sets and Document ID Service: 2. Select a document library settings where you want to implement Document Sets. In my case it is Shared Documents. When you have selected the settings go to Advanced settings and then allow content types management: 3. Add an existing content type called Document Set: 4. Now I want to create a new Document Set. I have a sales opportunity and I have two documents related to this sales opportunity. So first I select New Document -> Document Set command, then provide name and description and there it is: 5. To check if our Document ID...