Thursday, March 11, 2010

How to get SharePoint KPI value in C#

It's been a while since I had anything smart to say, and then I had to go and play with KPIs...

KPIs in SharePoint 2007 are pretty cool, they allow you to work in several ways basically: Either you enter the KPI info manually, or you connect it to some sort of a data source like a list, excel file or other providers.

in the end you find your self with a nice list item that has some KPI fields to hold the goal, warning and value levels (in C#: double).

the problem is that when you try to access these fields in code you find that only the manual KPI actually holds the information for you to display:

value = (double)item[list.Fields.GetFieldByInternalName(MobileConstants.KPIHelper.Field_Value).Id];
goal = (double)item[list.Fields.GetFieldByInternalName(MobileConstants.KPIHelper.Field_Goal).Id];
warning = (double)item[list.Fields.GetFieldByInternalName(MobileConstants.KPIHelper.Field_Warning).Id];


Meaning, if you have a KPI that loads from a SharePoint list or an excel file... don't hope to get anything from these fields...

Well, after hours of diggin into SharePoint dll's, I have found the solution finally.

It appears that the entire KPI API ( :) ) is either internal or private to these DLL's, which means of course - reflection!

So I dug in and found some cool examples in MS code that loads KPIs and display them, which in turn exposed some key classes and methods that would save the day.

First one - is the KpiFactory. This guy will in turn get us a Kpi object based on a KPI list item (yes, simple SPList.Item object).

Once we got the Kpi - it is safe to call GetKpiData with no parametes to get another object named KpiData, which finally holds a Value, Goal and Warning properties!

So the final code should look like so:
Assembly asm = System.Reflection.Assembly.Load("Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c");
Type t = asm.GetType("Microsoft.SharePoint.Portal.WebControls.KpiFactory");
MethodInfo mi = t.GetMethod("GetKpi", BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static, null, new Type[] { item.GetType() }, null);
object kpi = mi.Invoke(null, new object[] { item });
mi = kpi.GetType().GetMethod("GetKpiData", BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance, null, new Type[] { }, null);
object kpidata = mi.Invoke(kpi, new object[] { });

value = (double)ReflectionUtility.GetPropertyValue(kpidata, "Value");//double
warning = (double)ReflectionUtility.GetPropertyValue(kpidata, "Warning");//double
goal = (double)ReflectionUtility.GetPropertyValue(kpidata, "Goal");//double


Of course there are some unclear parameters that have to do with filter and other things, I will update as soon as i figure out what they are for... no idea for now - but at least our mobile solution will have nice KPI's to show after all!

6 comments:

Jack said...

Awesome Find Shai!.
That iPhone app you're working on looks pretty sweet too!

I looked in the sidebar, but I don't see any iPhone tags, do you have any posts on it's development?

Shai Petel said...

Hi Jack,

Thanks!

If you want to be a part of the mobile extensions beta group you can email the product manager: nimrod at kwizcom.com

We currently have full support for Blackberry and iPhone.

For other mobile devices, functionality works but UI needs some touchups still.

Thanks, Shai.

Anonymous said...

Dear Shai,

Nice post on KPI reports. Is it possible to create 1 row X multiple columns?

I have used the OOTB features to create a 1 row X 1 column kind of standard KPI dashboard

Cheers

Anonymous said...

Dear Shai,

Nice post on KPI reports. Is it possible to create 1 row X multiple columns?

I have used the OOTB features to create a 1 row X 1 column kind of standard KPI dashboard

Cheers

Lance Wilson said...

When I try running your code, this line:
object kpidata = mi.Invoke(kpi, new object[] { });

gives me the following error:

TargetInvocationException

I literally cut and pasted your code so I'm not sure what I'm doing wrong here.

Shai Petel said...

Hi,

Need more information. What version of SharePoint are you running on? Does the DLL we are loading in reflection exist in the GAC folder?

Perhaps the list is not of KPI type, the item is not a KPI type (except for static type).

Good luck!