Generating Live data for Chart WebPart

• Download source - 50.21 KB

Introduction

This post discusses how to create and generate a chart webpart using out of the box Sharepoint 2010 Enterprise edition features.

Background

I was given the Excel sheet with customer details like customer name, contact person and the call status such as Not Called, Not Interested, Demo Scheduled, Proposal Sent and Finalized. I was assigned to generate the Chart webpart using these data. As I didn't have any raw data[x and y axis figures] for generating the Chart WebPart, I followed these steps:

1. Creating the Calculated columns, each column will have to be calculated as per the Call status.
2. I used to assign the number 0 or 1 in the calculated columns.

If call status "Not Called" is selected by the marketing team member(s), "NCalled (calculated column) will have the value 1, remaining calculated columns will be set to "0". By doing this, now I will have categorised call status and its number of calls. When new calls or existing calls are updated or deleted, I need to update the data which will be the source for Chart webpart. Here I prepared the SPItemEventReceiver based solution on my development server which will attach into my "Import Excel Sheet" based SharePoint List. I don't want to put overhead on my actual source Sharepoint list as it is used by team members continuously. I decided to create the separate Sharepoint list to have the call status columns "Not Called, Not Interested, Demo Scheduled and Proposal Sent" and Number Of Calls. It looks like below. This will get updated as soon as user modifies/adds/deletes in the Sharepoint list.

Name of this Sharepoint list is "CallStatusDetails":

Using the Code

I created the two methods to retrieve which call types were updated and then calculate the number of calls available from the Prospects Sharepoint list for the selected call status type with updation.

public int retrieveRowCount(string strCallType, string ColName)
{        
try
{
site = new SPSite("http://mosstemp:5000"); 
web = site.OpenWeb(); 
SPList list = web.Lists["Prospects"]; 
SPQuery query = new SPQuery(); 
string camlQuery = "<where /><eq /><fieldref name="Status" /><value type="Choice" />" + strCallType+ "</value /></eq /></where />"; 
query.Query = camlQuery; 
DataTable dt = list.GetItems(query).GetDataTable(); 
int a = 0; int aSumNCalled = 0; 
foreach (DataRow row in dt.Rows)
 { 
	if(row["Status"].ToString() == Column)
	 { 
		aSumNCalled = aSumNCalled + Convert.ToInt32(row[ColName].ToString()); 
		a++; 
	 } 
}
 	SPList updateList = web.Lists["CallStatusDetails"]; 
	SPQuery UpdateDetailsSPQ = new SPQuery(); 
	string updateDetailQry = "<where /><eq /><fieldref name="CallStatus" /><value type="Text" />" + Column + "</value /></eq /></where />"; 
	UpdateDetailsSPQ.Query = updateDetailQry; 
	SPListItemCollection col = updateList.GetItems(UpdateDetailsSPQ);
	 foreach (SPListItem item in col)
	 { 
	 item["NumberOfCalls"] = aSumNCalled.ToString(); 
          item.Update();
	 }
 return aSumNCalled; 

} 
catch(Exception eX)
 { throw eX; } finally { web.Close(); web.Dispose(); site.Close(); site.Dispose(); } }

Method 2

//This will update the"CallStatusDetails" SPList
public void UpdateRecord(string Column, int rowCount)
 { 
	SPList updateList = web.Lists["CallStatusDetails"];
	SPQuery UpdateDetailsSPQ = new SPQuery();
	string updateDetailQry = "<where /><eq /><fieldref name="CallStatus" /><value type="Text" />" + Column + "</value /></eq /></where />"; 
	UpdateDetailsSPQ.Query = updateDetailQry;
	SPListItemCollection col = updateList.GetItems(UpdateDetailsSPQ); 
	foreach (SPListItem item in col)
	{
		 item["NumberOfCalls"] = rowCount.ToString();
		 item.Update();
	 } 
}

The final call is: call these methods into ItemAdded/ItemUpdated/ItemDeleted overrides methods.

int NCalledNumbers = retrieveRowCount("Not Called", "NCalled"););
UpdateRecord("Not Called", NCalledNumbers);

Points of Interest

While doing this, I found the Excel sheet takes some columns as "plain text and Multiline text". On Chart preparation time, my Calculated columns are not listing out in the dropdown list which references for chart data because of text type and learned "Calculated columns will not be used to sum up". Recently, I bought "Samsung Galaxy Tab" and enjoyed all the features. For my curiosity, I installed "Android" emulator on my PC and made some basic application. I am looking forward to integrate my Android application into my tab. I plan to develop for a very basic webservice based Sharepoint application and integrate into Android emulator.

History

I included the SharePoint List templates and .WSP package to test and get some ideas for your development experience.