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):