Filter data and display cumulative sum results with the SharePoint Chart

Applies to: Nevron Chart for SharePoint (WSS3.0, SharePoint 2007/2010/2013)

How to filter data and display cumulative sum results with the SharePoint Chart?

Let’s say that we have the sample data below. It represents the actual (Actual) and targeted (Target) data for a specified time period. The “Date” column is of type Date and Time. The target may include data from the future, while the actual only has data for the past and current month (in this example August and we don’t have data for the current month).

We can easily display the monthly comparison between the target and actual results for the whole time period as a clustered bar chart. However, displaying comparison for the future will not be informative. This is why we will use the Chart Web Part Pivot to filter the records and only display data for the past and current months.



We may also have the requirement to show cumulative sum, displaying the value for each month accumulated with the previous month value. We are going to use Bar chart type, sub type: Clustered. The following example will explain how are we going to filter the data and display the cumulative sum results:

1. From the Pivot tab, for the Data Groupings >> Categories, we will create a new category:
Label: =FORMAT(FIRST(Fields!Date), "MMM", "en-us")
Group By: =Fields!Date



2. For the Data Groupings >> Values, we have two values for Actual and Target:
Value: =SUM(Fields!Actual)
Value: =SUM(Fields!Target)

3. To filter the data and display only the past and the current months, we will create a new global filter and use the following exression:
=AND(MONTH(Fields!Date) <= MONTH(NOW()), YEAR(Fields!Date) <= YEAR(NOW()))



4. To display the cumulative sum (the value for each month accumulated with the previous month value), we will use the following custom 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;
 
            // Calculate comulative sum
            for (int i = 0; i < chart.Series.Count; i++)
            {
                NSeries series = chart.Series[i] as NSeries;
 
                if (series == null)
                    continue;
 
                for (int j = 1; j < series.Values.Count; j++)
                {
                    double prevValue = (double)series.Values[j - 1];
                    double curValue = (double)series.Values[j];
 
                    if (double.IsNaN(prevValue) || double.IsNaN(curValue))
                        continue;
 
                    series.Values[j] = curValue + prevValue;
                }
            }
        }
    }
}

[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
 
            ' Calculate comulative sum
            For i As Integer = 0 To chart.Series.Count - 1
                Dim series As NSeries = TryCast(chart.Series(i), NSeries)
 
                If series Is Nothing Then
                    Continue For
                End If
 
                For j As Integer = 1 To series.Values.Count - 1
                    Dim prevValue As Double = CDbl(series.Values(j - 1))
                    Dim curValue As Double = CDbl(series.Values(j))
 
                    If Double.IsNaN(prevValue) OrElse Double.IsNaN(curValue) Then
                        Continue For
                    End If
 
                    series.Values(j) = curValue + prevValue
                Next
            Next
        End Sub
    End Class
End Namespace

That way we will skip the data from the future and have comparison of the accumulated data, generating the following chart:


If we only need to display the current month, we will have to use only custom code because the code is executed after the pivot filtering. We will remove the Pivot filter (step 3) and use the following code:

[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;
 
            // Calculat comulative sum
            for (int i = 0; i < chart.Series.Count; i++)
            {
                NSeries series = chart.Series[i] as NSeries;
 
                if (series == null)
                    continue;
 
                for (int j = 1; j < series.Values.Count; j++)
                {
                    double prevValue = (double)series.Values[j - 1];
                    double curValue = (double)series.Values[j];
 
                    if (double.IsNaN(prevValue) || double.IsNaN(curValue))
                        continue;
 
                    series.Values[j] = curValue + prevValue;
                }
            }
 
            // Remove all months except the current one
            string month = DateTime.Now.ToString("MMM");
            NOrdinalScaleConfigurator scale = chart.Axis(StandardAxis.PrimaryX).ScaleConfigurator as NOrdinalScaleConfigurator;
 
            for (int monthIndex = scale.Labels.Count - 1; monthIndex >= 0; monthIndex--)
            {
                if (month == (string)scale.Labels[monthIndex])
                {
                    continue;
                }
 
                scale.Labels.RemoveAt(monthIndex);
 
                for (int i = 0; i < chart.Series.Count; i++)
                {
                    NXYScatterSeries series = chart.Series[i] as NXYScatterSeries;
 
                    if (series == null)
                        continue;
 
                    series.Values.RemoveAt(monthIndex);
                }
            }
        }
    }
}

[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
 
            ' Calculat comulative sum
            For i As Integer = 0 To chart.Series.Count - 1
                Dim series As NSeries = TryCast(chart.Series(i), NSeries)
 
                If series Is Nothing Then
                    Continue For
                End If
 
                For j As Integer = 1 To series.Values.Count - 1
                    Dim prevValue As Double = CDbl(series.Values(j - 1))
                    Dim curValue As Double = CDbl(series.Values(j))
 
                    If Double.IsNaN(prevValue) OrElse Double.IsNaN(curValue) Then
                        Continue For
                    End If
 
                    series.Values(j) = curValue + prevValue
                Next
            Next
 
            ' Remove all months except the current one
            Dim month As String = DateTime.Now.ToString("MMM")
            Dim scale As NOrdinalScaleConfigurator = TryCast(chart.Axis(StandardAxis.PrimaryX).ScaleConfigurator, NOrdinalScaleConfigurator)
 
            For monthIndex As Integer = scale.Labels.Count - 1 To 0 Step -1
                If month = DirectCast(scale.Labels(monthIndex), String) Then
                    Continue For
                End If
 
                scale.Labels.RemoveAt(monthIndex)
 
                For i As Integer = 0 To chart.Series.Count - 1
                    Dim series As NXYScatterSeries = TryCast(chart.Series(i), NXYScatterSeries)
 
                    If series Is Nothing Then
                        Continue For
                    End If
 
                    series.Values.RemoveAt(monthIndex)
                Next
            Next
        End Sub
    End Class
End Namespace

That way we will display the accumulated data only for the current month (August):

Article ID: 200, Created On: 8/5/2011, Modified: 1/29/2013