Applies to: Nevron Chart for SharePoint (WSS3.0, SharePoint 2007/2010/2013)
How to count aggregated records and make trend lines with the Chart Web Part?
Let’s say that we have the sample data below, it represents “Created” and “Closed” projects in time. We can count the number of created and closed projects for each week, compare them as a clustered bar chart and display the difference as a line chart (Open).
We assume that projects that have been closed will have a closed date, and if they are not closed the date in the data is blank. We can also assume that Sunday is the first day of the week.
We will use the Combo Chart type and the Pivot Date Time Functions. The line chart has to keep a running total of opened projects over the life of the project. This will provide information about trending up or down on opened projects, a cumulative sum.
The result will be a combo chart providing information about:
- how many projects were created for each week
- how many projects were closed for each week
- the overall count of opened projects that have not been closed for the period (cumulative sum)
Following are step-by-step instructions to configure the chart:
1. Once you connect to your data (from the Data Source tab), go to the Chart tab and select the Combo chart type.
2. Go to the Pivot tab:
- from the Data Groupings >> Categories tab, create a new category.
In the Data Groupings >> Categories >> General tab >> Label field, type the following expression:
=FIRST(YEAR(Fields!Project_Created)) + "/" + FIRST(WEEK(Fields!Project_Created))
In the Data Groupings >> Categories >> General tab >> Group By, create two groupings and use the following expressions:
=YEAR(Fields!Project_Created)
=WEEK(Fields!Project_Created, DATETIME("1-1-" + FIRST(YEAR(Fields!Project_Created)), "en-us"))
In the Data Groupings >> Categories >> Sorting tab, create two fields and use the following expressions (direction Ascending):
=YEAR(Fields!Project_Created)
=WEEK(Fields!Project_Created, DATETIME("1-1-" + FIRST(YEAR(Fields!Project_Created)), "en-us"))
3. In the Data Groupings >> Values tab, you need to create 3 values for the Open, Created and Closed.
Value 0 - Open (Line series)
In the Data Groupings >> Values >> General tab, in the Label field type: Open
In the Data Groupings >> Values >> General tab, in the Series Type select: Line
In the Data Groupings >> Values >> General >> Data tab use the following expressions for the Value:
=COUNT(Fields!Project_Created, false) - COUNT(Fields!Project_Closed, false)
Value 1 - Created (Bar series)
In the Data Groupings >> Values >> General tab, in the Label field type: Created
In the Data Groupings >> Values >> General tab, in the Series Type select: Bar
In the Data Groupings >> Values >> General >> Data tab use the following expressions for the Value:
=COUNT(Fields!Project_Created, false)
Value 2 - Closed (Bar series)
In the Data Groupings >> Values >> General tab, in the Label field type: Closed
In the Data Groupings >> Values >> General tab, in the Series Type select: Bar
In the Data Groupings >> Values >> General >> Data tab use the following expressions for the Value:
=COUNT(Fields!Project_Closed, false)
4. In order to have the line chart displaying the overall count of opened projects that have not been closed for the period (cumulative sum), you need to use the following code in the Code tab:
[C#]
using System;
using System.Drawing;
using Nevron.GraphicsCore;
using Nevron.Chart;
using Nevron.ReportingServices;
namespace MyNamespace
{
///
/// Sample class
///
public class MyClass
{
///
/// Main entry point
///
///
public static void RSMain(NRSChartCodeContext context)
{
if (context.Document.Charts.Count == 0)
return;
// get the first chart in the document
NChart chart = context.Document.Charts[0];
chart.Series[0].InflateMargins = false;
if (chart.Series.Count == 0)
return;
// get the first line series in the chart
NSeries series = chart.Series[0] as NSeries;
if (series == null)
return;
// make the totals
int count = series.Values.Count;
double total = 0;
for (int i = 0; i < count; i++)
{
double value = (double)series.Values[i];
if (Double.IsNaN(value))
continue;
total = total + value;
series.Values[i] = total;
}
}
}
}
[VB.NET]
Imports System
Imports System.Drawing
Imports Nevron.GraphicsCore
Imports Nevron.Chart
Imports Nevron.ReportingServices
Namespace MyNamespace
'''
''' Sample class
'''
Public Class [MyClass]
'''
''' Main entry point
'''
'''
Public Shared Sub RSMain(context As NRSChartCodeContext)
If context.Document.Charts.Count = 0 Then
Return
End If
' get the first chart in the document
Dim chart As NChart = context.Document.Charts(0)
chart.Series(0).InflateMargins = False
If chart.Series.Count = 0 Then
Return
End If
' get the first line series in the chart
Dim series As NSeries = TryCast(chart.Series(0), NSeries)
If series Is Nothing Then
Return
End If
' make the totals
Dim count As Integer = series.Values.Count
Dim total As Double = 0
For i As Integer = 0 To count - 1
Dim value As Double = CDbl(series.Values(i))
If [Double].IsNaN(value) Then
Continue For
End If
total = total + value
series.Values(i) = total
Next
End Sub
End Class
End Namespace
Article ID: 198, Created On: 8/2/2011, Modified: 1/29/2013