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.