Link to home
Start Free TrialLog in
Avatar of Beverly Penney
Beverly PenneyFlag for Canada

asked on

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.  
Avatar of Megan Brooks
Megan Brooks
Flag of United States of America image

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?
Avatar of Beverly Penney

ASKER

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
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Megan Brooks
Megan Brooks
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial