本文介绍如何在SharePoint中创建并展示一个显示过去十年客户案件趋势的图表WebPart。通过一系列步骤，包括添加图表引用、初始化图表、从SQL数据库获取数据、填充图表等，最终实现了一个直观的数据展示组件。
Adding graphs to SharePoint webparts
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.
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
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 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. Without making further changes to your web.config, your web application will need to have access to the C:\TempImages directory as well.
adding the DataVisualization.Charting http handler. Without making further changes to your web.config, your web application will need to have access to the C:\TempImages directory as well.
<system.webServer> <handlers> ..... <add name="ChartImg" verb="*" path="ChartImg.axd" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" /> </handlers> </system.webServer>
8. Build and deploy!
<g_vml_:shape style="POSITION: absolute; WIDTH: 966px; HEIGHT: 4960px; TOP: 0px; LEFT: 0px" coordsize = "9650,49230" filled = "f" fillcolor = "black" stroked = "t" strokecolor = "black" strokeweight = "1.5pt" path = " m25,20 l25,49200 at25,49185,35,49195,-70,49140,-20,49190 m30,49195 l9620,49195 at9605,49185,9615,49195,9560,49190,9610,49140 m9615,49190 l9615,20 at9605,25,9615,35,9610,-20,9560,-70 m9610,25 l30,25 at25,25,35,35,-20,-70,-70,-18 e"><g_vml_:stroke opacity = "3932f" miterlimit = "10" joinstyle = "miter" endcap = "flat"><g_vml_:shape style="POSITION: absolute; WIDTH: 966px; HEIGHT: 4960px; TOP: 0px; LEFT: 0px" coordsize = "9650,49230" filled = "f" fillcolor = "black" stroked = "t" strokecolor = "black" strokeweight = "1.5pt" path = " m15,15 l15,49205 at15,49185,35,49205,-125,49145,-25,49245 m25,49205 l9625,49205 at9605,49185,9625,49205,9565,49245,9665,49145 m9625,49195 l9625,15 at9605,15,9625,35,9665,-25,9565,-125 m9615,15 l25,15 at15,15,35,35,-25,-125,-125,-20 e"><g_vml_:stroke opacity = "3145f" miterlimit = "10" joinstyle = "miter" endcap = "flat"><g_vml_:shape style="POSITION: absolute; WIDTH: 966px; HEIGHT: 4960px; TOP: 0px; LEFT: 0px" coordsize = "9650,49230" filled = "f" fillcolor = "black" stroked = "t" strokecolor = "black" strokeweight = "1.5pt" path = " m5,10 l5,49210 at5,49185,35,49215,-180,49150,-30,49300 m20,49215 l9630,49215 at9605,49185,9635,49215,9570,49300,9720,49150 m9635,49200 l9635,10 at9605,5,9635,35,9720,-30,9570,-180 m9620,5 l20,5 at5,5,35,35,-30,-180,-180,-23 e"><g_vml_:stroke opacity = "2516f" miterlimit = "10" joinstyle = "miter" endcap = "flat"><g_vml_:shape style="POSITION: absolute; WIDTH: 966px; HEIGHT: 4960px; TOP: 0px; LEFT: 0px" coordsize = "9650,49230" filled = "t" fillcolor = "black" stroked = "f" path = " m25,25 l25,49205 at25,49205,25,49205,-25,49155,-25,49155 l9625,49205 at9625,49205,9625,49205,9575,49155,9575,49155 l9625,25 at9625,25,9625,25,9575,-25,9575,-25 l25,25 at25,25,25,25,-25,-25,-25,-25 e"><g_vml_:fill src = "//www.blogblog.com/1kt/transparent/black50.png" type = "tile" opacity = "1" position = "0,0">
Wednesday, 8 February 2012
Adding graphs to SharePoint webparts
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.
3. Add a chart to the webpart
4. Initialize the chart in the OnInit event
5. Add the chart to the page in the CreateChildControls method
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).
adding the DataVisualization.Charting http handler. Without making further changes to your web.config, your web application will need to have access to the C:\TempImages directory as well.
8. Build and deploy!
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.
usingSystem.Web.UI.DataVisualization.Charting;
3. Add a chart to the webpart
privateChart chart;
4. Initialize the chart in the OnInit event
protectedoverridevoidOnInit(EventArgs e){base.OnInit(e); chart =newChart(); chart.Visible=false;...}
5. Add the chart to the page in the CreateChildControls method
protectedoverridevoidCreateChildControls(){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).
protectedoverridevoidOnPreRender(EventArgs e){base.OnPreRender(e);try{...PopulateChart(_selectedClientNumbers);...}catch(Exception exception){...}}privatevoidPopulateChart(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 =newChartArea(); 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; }}privateSeriesGetHistoricalMatterInfo(string selectedClientNumbers){Series series =newSeries(); series.Color=Color.ForestGreen; series.BackSecondaryColor=Color.GreenYellow; series.BorderColor=Color.Firebrick; series.BackGradientStyle=GradientStyle.TopBottom;SqlConnectionStringBuilder cs =newSqlConnectionStringBuilder(); cs.UserID=SqlUser; cs.Password=SqlUserPassword; cs.DataSource=SqlServer; cs.InitialCatalog=SqlServerDatabase;SqlConnection conn =newSqlConnection(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 =newSqlCommand((String.Format(GetClientsMatterHistory, clientsIn)), conn); command.CommandType=CommandType.Text;SqlDataReader r = command.ExecuteReader();if(r ==null){ chart.Visible=false;returnnull;}if(r.HasRows){while(r.Read()){ var yearCountObj =(int)r["YearCount"]; var yearObj =(int)r["Year"]; var p =newDataPoint(); p.XValue= yearObj; p.YValues=newdouble[]{Convert.ToDouble(yearCountObj)}; series.Points.Add(p);}} r.Close();return series;}catch{returnnull;}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. Without making further changes to your web.config, your web application will need to have access to the C:\TempImages directory as well.
<system.webServer><handlers> ..... <addname="ChartImg"verb="*"path="ChartImg.axd"type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/></handlers></system.webServer>
8. Build and deploy!
Subscribe to:
Post Comments (Atom)
About Me
-
Matthew Yarlett
- I'm a solutions architect primarily focused on delivering business solutions built on the SharePoint platform. I love getting technical, but I'm equally interested in the business end of IT and deriving value from IT systems.
Tags
- SharePoint (23)
- PowerShell (10)
- Managed Metadata (4)
- Webparts (4)
- Active Directory (3)
- Javascript (3)
- SPTaxonomyField (3)
- Analytics (2)
- Content Types (2)
- Custom Actions (2)
- SPListItem (2)
- Taxonomy (2)
- User Profile Service (2)
- Auditing (1)
- Automation (1)
- Charts (1)
- DOM (1)
- ECB (1)
- EWS (1)
- Email (1)
- Error 2587 (1)
- Exchange Web Services (1)
- Fields (1)
- HTML (1)
- IIS (1)
- LVWP (1)
- Log Parser (1)
- Logparser (1)
- ModalDialog (1)
- Office (1)
- Outlook (1)
- Page Lifecycle (1)
- PeopleEditor (1)
- Query (1)
- Reports (1)
- SP.UI.ModalDialog (1)
- SQL (1)
- Search (1)
- Search Box Webpart (1)
- SearchBoxEx (1)
- Solution (1)
- Taxonomy Field (1)
- Users (1)
- XSL (1)
- XSLT (1)
- jCrop (1)
- jQuery (1)
- showModalDialog (1)
扫一扫
15
到【灌水乐园】发言
No comments:
Post a Comment