By: Robert Christ
Introduction to Status Lists
Anyone who has poked around SharePoint 2010 knows that one of the most touted features of SharePoint is its ability to rapidly and easily display data in clear reporting forms.
If you aren’t of a mind to delve into Excel Services, SQL Service Analysis or PerformancePoint, you are not out of options. SharePoint 2010 also offers the Status List feature.
Figure 1 - Example of a Status List
Status lists are a relatively simple feature. In short, they allow a user, with the click of a few buttons, to query a list on a SharePoint site, and determine if the items in that list (hence force called the data list) meet a condition of one type or another.
For example, you can make a Status List Item (called a KPI, there are three shown in the picture above) to determine if >50% of all items in the data list have a column value > 2, or perhaps just report on whether the data list has greater than or less than a certain number of items in it at any one time.
If you’re particularly clever, you’ll realize that you can also create these KPIs to report based on views within the data list. By configuring a view to filter according to a certain set of values within the data list, you can use the Status List to report on filtered queries of the data list as opposed to reporting just on every single list item in the data list as a whole.
Programmatically Manipulating Status Lists
But what if I want to programmatically manipulate status lists? What if I want to make my own type of custom status list?
Unlike normal SPLists, Status Lists have many hidden columns, not all of which are easy to understand. As such, we’ll break them down a little bit here.
Columns
|
Indicator Goal Threshold |
|
|
Indicator Warning Threshold |
|
|
Value Expression |
If the Status List is set to “Number of List Items in the View”, this is null
If the Status List is set to “Percentage of List Items in the View”, this is the SPQuery.query string used to query an SPList.
If the Status List is set to “Calculation using all list items in the view”, this returns a string, such as:
Average:columnName:ColumnType
Total:columnName:ColumnType |
|
Lower values are better |
Returns a string of either the word True or False |
|
Data Source |
This is the url of the list which is being queried for the KPI |
Using the above 5 columns, one should be able to recreate the logic that occurs in an OOB Status List. For example, if we wanted to determine whether a KPI was Red, Yellow, or Green, we could theoretically do the following:
public Color DetermineKPIColor(SPWeb web, SPListItem item)
{
Color answer = Color.red;
int goalThreshold = Convert.ToInt32(item["Indicator Goal”
+ “ Threshold"].ToString());
int warningThreshold = Convert.ToInt32(item["Indicator Warning”
+ “Threshold"].ToString());
string dataSource = item["Data Source"].ToString();
dataSource = dataSource.Substring(0, dataSource.IndexOf(", "));
SPList dataList = web.GetList(dataSource);
bool allListItems = false;
bool percentExpression = false;
bool summationExpression = false;
string queryString = String.Empty;
if (item["Value Expression"] == null)
allListItems = true;
else
{
if (item["Value Expression"].ToString().Contains('<'))
{
percentExpression = true;
queryString = item["Value Expression"].ToString();
}
else
summationExpression = true;
}
if (percentExpression)
{
SPQuery query = new SPQuery();
query.Query = queryString;
SPListItemCollection coll = dataList.GetItems(query);
int total = dataList.Items.Count;
if (total == 0)
percent = 0;
else
{
int items = coll.Count;
percent = items / total;
percent = percent * 100; //100%
}
}
if (!lowerAreBetter)
{
if (result >= goalThreshold)
return Color.Green;
if (result >= warningThreshold)
return Color.Yellow;
return Color.Red;
}
else
{
if (result <= goalThreshold)
return Color.Green;
if (result <= warningThreshold)
return Color.Yellow;
return Color.Red;
}
Hopefully this is enough to get you started! Remember, the .Net reflector can be your friend!
By: Robert Christ