?
Solved

Plot second series on SSRS line chart

Posted on 2012-03-16
5
Medium Priority
?
1,041 Views
Last Modified: 2012-07-11
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
0
Comment
Question by:JEClark1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 37732384
Hi,

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
0
 

Author Comment

by:JEClark1
ID: 37733029
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.
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 37734752
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
0
 

Accepted Solution

by:
JEClark1 earned 0 total points
ID: 37796482
Tim
Thanks for the detailedresponse.  I will have to try it to see the results.
0
 

Author Closing Comment

by:JEClark1
ID: 38174152
Well thought out repsonse.  Thank you!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question