Last week I was creating a client dashboard for displaying some contact data for a handful of key clients in a particular business area. I thought it would be good to add a graph that displays the number of matters (legal cases) we've handled for each client over the last 10 years, so contact managers viewing the dashboard have a quick view of the trend in work we've received from each client. This turns out to be a whole lot easier than I thought it was going to be. This is what I did.
The solution is based on a series of connected webparts, with one webpart supplying all the other webparts with client information. One of these webparts creates a list of all the open matters (legal cases) and has the graph that shows the trend in the flow of matters over the previous 10 year period.
To create the graph;
1. Add a reference to the Microsoft.Web.UI.DataVisualization.dll (the .Net 3.5 version). I'm developing on a Win 7 x64 machine with SharePoint 2010 Enterprise installed, and I have the dll here: c:\program files (x86)\Microsoft Chart Controls\Assemblies.
2. Add the using statement.
using System.Web.UI.DataVisualization.Charting;
3. Add a chart to the webpart
private Chart chart;
4. Initialize the chart in the OnInit event
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
chart = new Chart();
chart.Visible = false;
...
}
5. Add the chart to the page in the CreateChildControls method
protected override void CreateChildControls()
{
EnsureChildControls();
Controls.Add(chart);
...
}
6. Populate the chart. I'm doing this in the OnPreRender method, because I need to wait for the client information to be passed to the webpart from a provider webpart.
The first thing I do is get the information from our accounts SQL database. All I'm returning is a datareader containing two columns, YearCount (the number of matters for that year), and Year (the year).
After getting the data, all I need to do is iterate through the datareader rows, and add x & y points for each row to a series. Then I pass the series back to the chart, create a chart area, add the series to the chart, and presto, all done. Here's what the code looks like (other code I'm using that's not relevant to the chart has been left omitted).
protected override void OnPreRender(EventArgs e)
{
base.OnPreRender(e);
try
{
...
PopulateChart(_selectedClientNumbers);
...
}
catch (Exception exception)
{
...
}
}
private void PopulateChart(string clientNumbers)
{
try
{
Series s = GetHistoricalMatterInfo(clientNumbers);
if(s==null)
{
return;
}
chart.Width = 315;
chart.Height = 150;
chart.AntiAliasing = AntiAliasingStyles.All;
chart.TextAntiAliasingQuality = TextAntiAliasingQuality.High;
ChartArea ca = new ChartArea();
ca.BackColor = Color.Gray;
ca.BackSecondaryColor = Color.DarkGray;
ca.BackGradientStyle = GradientStyle.TopBottom;
ca.AxisY.Title = "Matters Opened";
ca.AxisX.Title = "Matter Activity, Recent Years";
ca.AxisX.Interval = 2;
chart.ChartAreas.Add(ca);
chart.Series.Add(s);
chart.Visible = true;
}
catch
{
chart.Visible = false;
}
}
private Series GetHistoricalMatterInfo(string selectedClientNumbers)
{
Series series = new Series();
series.Color = Color.ForestGreen;
series.BackSecondaryColor = Color.GreenYellow;
series.BorderColor = Color.Firebrick;
series.BackGradientStyle = GradientStyle.TopBottom;
SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder();
cs.UserID = SqlUser;
cs.Password = SqlUserPassword;
cs.DataSource = SqlServer;
cs.InitialCatalog = SqlServerDatabase;
SqlConnection conn = new SqlConnection(cs.ConnectionString);
String[] clients = selectedClientNumbers.Split(',');
String clientsIn = String.Empty;
foreach (string s in clients)
{
clientsIn += String.Format("'{0}',", s.Trim());
}
clientsIn = clientsIn.TrimEnd(',');
try
{
conn.Open();
SqlCommand command = new SqlCommand((String.Format(GetClientsMatterHistory, clientsIn)), conn);
command.CommandType = CommandType.Text;
SqlDataReader r = command.ExecuteReader();
if (r == null)
{
chart.Visible = false;
return null;
}
if (r.HasRows)
{
while (r.Read())
{
var yearCountObj = (int)r["YearCount"];
var yearObj = (int)r["Year"];
var p = new DataPoint();
p.XValue = yearObj;
p.YValues = new double[] { Convert.ToDouble(yearCountObj) };
series.Points.Add(p);
}
}
r.Close();
return series;
}
catch
{
return null;
}
finally
{
conn.Close();
}
}
7. Update the web.config file for (each) SharePoint web application that will host the webpart,
adding the DataVisualization.Charting http handler. Add the Http Handler for the chart images:
<handlers>
<add name="ChartImageHandler" verb="*" path="ChartImg.axd" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
</handlers>
Add a new key to the <appSettings> section to configure the location (among other things) the image files are written to (see:
Image File Management (Chart Controls) for more information). Remember that your web applications application pool will need to have access to write to the directory.
<appSettings>
<add key="ChartImageHandler" value="storage=file;timeout=20;dir=c:\Temp\;" />
</appSettings>
For more information on options for storing the chart images generated (i.e. in memory), see the following MSDN article:
http://msdn.microsoft.com/en-us/library/dd456629.aspx.
8. Build and deploy!
Update: I just added this to the Microsoft TechNetWiki, with an example of using an SharePoint list as a data source, as well as an example of using Charts in Visual Webparts. You can see it here:
http://social.technet.microsoft.com/wiki/contents/articles/17614.adding-charts-to-standard-webparts-and-visual-webparts.aspx
