asked on # Plot second series on SSRS line chart

I am creating a chart for reading measurements in elementary schools.

Please see the attachment.

The red line in the attachment does not exist on my chart. I drew it in with an editing program.

I would like to plot this secondary line from the student's first reading measurement for the year to the the end of the year goal, thus showing adequte progress for plots above and below the goal line.

Any ideas/assistance would be most appreciated.

ReadingChart.jpg

Please see the attachment.

The red line in the attachment does not exist on my chart. I drew it in with an editing program.

I would like to plot this secondary line from the student's first reading measurement for the year to the the end of the year goal, thus showing adequte progress for plots above and below the goal line.

Any ideas/assistance would be most appreciated.

ReadingChart.jpg

Microsoft SQL Server 2008SSRS

Tim

Thanks for the reply. Two problems are extrapolating and that I don't need a calculated arithmetc function, but instead need a straight line from the student start point to the spring end goal. Thanks again.

Thanks for the reply. Two problems are extrapolating and that I don't need a calculated arithmetc function, but instead need a straight line from the student start point to the spring end goal. Thanks again.

Hi,

Ok, I think you need to create a second dataset that provides fields to enable you to create a linear regression curve.

I followed the formulae in this link: http://people.hofstra.edu/stefan_waner/realworld/calctopic1/regression.html

I created a second dataset based on the first that simply returns the values to plug into the formulae. Key to this working well is getting your month as an ordinal, so the first month in your range maps to '1', the second to '2' etc.)

You then need a query that returns the following:

Sum(wcpm) SumX, Sum(monthAsOrdinal) SumY, Sum(wcpm * monthAsOrdinal) SumXY,

Sum(wcpm * wcpm) SumX2, Count(*) NumValues

which groups over your entire set of values.

You can then add a second table to your report that uses the new dataset (above any existing tables and your chart so it is evaluated first) and add the following expressions in two cells:

=Code.SetM((Fields!NumRows.Value * Fields!SumXY.Value - Fields!SumX.Value * Fields!SumY.Value) / ((Fields!NumRows.Value * Fields!SumX2.Value) - Fields!SumX2.Value))

and

=Code.SetB((Fields!SumY.Value - Code.GetM() * Fields!SumX.Value) / Fields!NumRows.Value)

Then in the Report Properties / Code section add the following:

private shared m as double

private shared b as double

Public Function SetM(value as double) as double

m = value

return m

End Function

Public Function GetM as double

return m

End Function

Public Function SetB(value as double) as double

b = value

Return b

End Function

Public Function GetB As double

Return b

End Function

This code enables you to store the values for M and B (gradient and intercept) for use in a new calculated series in your chart.

Your main dataset, which returns your month and wcpm values also needs to return the monthAsOrdinal value. You can then calculate the position on your straight line as

=Code.GetM() * Fields!monthAsOrdinal.value + Code.GetB()

This will then show an extra straight line correlation on your chart.

Tim

Ok, I think you need to create a second dataset that provides fields to enable you to create a linear regression curve.

I followed the formulae in this link: http://people.hofstra.edu/stefan_waner/realworld/calctopic1/regression.html

I created a second dataset based on the first that simply returns the values to plug into the formulae. Key to this working well is getting your month as an ordinal, so the first month in your range maps to '1', the second to '2' etc.)

You then need a query that returns the following:

Sum(wcpm) SumX, Sum(monthAsOrdinal) SumY, Sum(wcpm * monthAsOrdinal) SumXY,

Sum(wcpm * wcpm) SumX2, Count(*) NumValues

which groups over your entire set of values.

You can then add a second table to your report that uses the new dataset (above any existing tables and your chart so it is evaluated first) and add the following expressions in two cells:

=Code.SetM((Fields!NumRows

and

=Code.SetB((Fields!SumY.Va

Then in the Report Properties / Code section add the following:

private shared m as double

private shared b as double

Public Function SetM(value as double) as double

m = value

return m

End Function

Public Function GetM as double

return m

End Function

Public Function SetB(value as double) as double

b = value

Return b

End Function

Public Function GetB As double

Return b

End Function

This code enables you to store the values for M and B (gradient and intercept) for use in a new calculated series in your chart.

Your main dataset, which returns your month and wcpm values also needs to return the monthAsOrdinal value. You can then calculate the position on your straight line as

=Code.GetM() * Fields!monthAsOrdinal.valu

This will then show an extra straight line correlation on your chart.

Tim

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Well thought out repsonse. Thank you!

You might find this post useful:

http://blogs.msdn.com/b/robertbruckner/archive/2008/11/09/trendlines-calculated-series-in-reporting-services-charts.aspx

Not sure if you c an extrapolate the line beyond your data series, though.

Tim