Beverly Penney
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.
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.
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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?