Add a forecast/trend line in SSRS

How do I add a forecast line in an SSRS 2008 reporting Services line graph.

I have a query that returns the following values
Date      Product         Target    Sales      Salesperson
201003      B           200       250            Joe
201003          C                       200         150            Bill
201004           B                     100        75            Joe
201004          c                      100        75            Bill
201005          B                       250                  Joe
201005          c                      350                  Bill

The report should be for each product and show the sales total (bar graph) and then
I need the target values as the trend line and then the x-axis would show the time period 200103,201004,201005.  
Rhonda CarrollAsked:
Who is Participating?
Megan BrooksSQL Server ConsultantCommented:
You can start with a bar chart (or in this case I think it would be a column chart so that the date can appear on the X axis), add your data series of sales and target, and then change the drawing type of just the target series to draw lines, if that is the series that you want to appear as a line.
The option is in the property sheet for each series. You would display properties (F4), click the chart so as to show the property sheet for the "Target" series, and then change the "Type" property in the property sheet to "Line." The browser for this property actually brings up the "Chart Type" dialog, but the change will apply only to this one series.
The query will still have to supply all of the series data, but this is how you display a combined bar chart and line chart. Is that what you were looking for?
Megan BrooksSQL Server ConsultantCommented:
I am not aware of any direct way of doing this with SSRS charts. I am going to need to do it at some point myself, though, and I had already been thinking about it. If you want a linear regression then all you really need to do is calculate the slope and intercept for each product, and then use that to calculate a point on the trend line for each record.
I don't have an example of this yet, but it doesn't sound too hard. If there were a single trend line then all that would be needed is to store the slope and intercept (in SSRS 2008 a variable could be used) and then calculate the point for each record using a calculated field in the dataset.
You could pre-calculate a collection of slopes and intercepts, one for each product, and make the collection available through custom code. If you do this, make sure that the calculations are repeated if the Code object is dropped and re-initialized by SSRS, since SSRS 2008 seems to want to do this for every page when the report is interactive.
You could also do the caculations in a stored procedure that supplies the data for the dataset. I think I might want to try this first.
This isn't really an answer, just "hints." Does someone else have a solution already worked out?
Rhonda CarrollAuthor Commented:
that might work for a trend line however my line will  be a forecast and the bar part of the graph will be the results ... the plan is to show the visual of the bar exceeding the forecast line ... if i find out anything i will post
Megan BrooksSQL Server ConsultantCommented:
I may still have a lot to learn about this, but for now it seems like it is mainly a problem of having the dataset deliver the right data. The bar chart should then be able to plot it, whether it is actual or forecast data, and it should be able to change the appearance of the forecast data if you want it to do that.
Rhonda CarrollAuthor Commented:
I am returning the correct data ... I have joined the forecast table to the sales table and have that piece working.  I just need to get the visual peice.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.