We can create a column chart in SSRS and sometimes we also need add line over bar chart to display average. In this post, we will see how we can add trend line. I have used SSRS 2008.
We will create column chart which will display the sales amount for each month for year 2004.
Setp-1:- Create DataSet Query
SELECT MonthNumber = dt.MonthNumberOfYear
, SalesAmount = SUM(fs.SalesAmount)
FROM dbo.FactInternetSales FS
INNER JOIN dbo.DimTime dt ON FS.OrderDateKey = dt.TimeKey
WHERE dt.FiscalYear = 2004
GROUP BY dt.MonthNumberOfYear
ORDER BY dt.MonthNumberOfYear
Note:- Here there is no need to compute SUM, we can do this in SSRS. But, always good to do computation in SQL.
Setp-2:-Create a Bar chart
Now, we need to start Report Builder or BIDS and add column chart to this. We need to put “SalesAmount” in Data field and “MonthNumber ” in category filed.
Also, to display Month name we need to change label property of category group property to “MonthName(Fields!MonthNumber.Value,True)”.
Setp-3:- Now we need to add moving average line to this chart, so that analyst can see the trend.
3.1) Click on sum(salesAmount) and select add calculated series.
3.2) Select Moving average from the drop down box
3.3) Change the Legend of calculated series. If we want to change color then we can also change Fill property to change color of line.
This way we can add trend line to bar/column chart.
Updated:- To see how to add simple average line please visit next post http://beyondrelational.com/blogs/chintak/archive/2010/12/02/add-an-average-trend-line-to-column-bar-chart-in-ssrs.aspx.