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.  
Beverly PenneyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
0
Beverly PenneyAuthor 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
0
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.
0
Beverly PenneyAuthor 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.
0
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.