In SSRS 2008 R2 I need to create a stacked bar chart with a trend line.
I have a series group that breaks out the stacked bar chart as needed, but it also breaks out the trend line as shown in the above picture. I don't want it to group the chart series that is driving the trend line, I only want the chart to display 1 trend line. Is this possible to do?
After what seemed like days of working on this I finally realized a way to do it that would work for my own data. Depending on how your data is arranged, additional steps in the SQL query may be needed.
Here is a small pseudo sample of the data I needed to chart.
select Department, SUM(Scrapped * OperationCost) As ScrapValue, TransactionDate
from MonthlyScrap
where TransactionDate between '03-01-2013' and '03-31-2013'
group by Department, TransactionDate
As expected this gives an output dataset of three fields named Department, ScrapValue, and TransactionDate. Putting this into a stacked chart in SSRS is simple. Just drag TransactionDate into the Categories, Department into the Series Groups, and ScrapValue into the Values fields on the Chart Data control.
However the OP wants to also graph the TOTAL of the Values, in this case ScrapValues onto the graph as a single line. Because of the way the data is grouped in the query and in the chart, this will NOT work. If you add the data in as Values again (note it is already SUMmed when adding the first time due to stacked chart style) then it simply displays a line for each individual Department due to the way you are grouping the Series. Even SUMming it again has no effect. Thus in order to make this work you have to transpose(pivot) your data in the SQL query (thanks Dave ;) ) in order to group it properly.
Here is the new query.
select *
from (select Department As Dept, SUM(Scrapped * OperationCost) As ScrapValue, TransactionDate
from MonthlyScrap
where TransactionDate between '03-01-2013' and '03-31-2013'
Group by Department, TransactionDate) As Datatable
PIVOT
(
SUM(ScrapValue)
FOR Dept IN ([Molding], [Machining], [Grinding])
) AS p Order by p.TransactionDate
Now you have a dataset with the fields TransactionDate, Molding, Machining, and Grinding that is grouped by day (one row per day with values under each department) that makes it easy to add a final calculated field for the Totals (=Fields!Grinding.Value + Fields!Molding.Value + Fields!Machining.Value). Now when you create the stacked chart you drag the TransactionDate into Categories, and all the other fields into Values. You no longer need a Series Grouping as the query has taken care of that. Finally, change the chart type of the calculated field to a line chart and you now have a stacked column chart with a single line representing the totals!
(Note: image displays running totals on a secondary y-axis rather than just the totals, but it still uses the same underlying method)
Here is my final query where I added some dynamic SQL to get the chart values and turned it into a stored procedure call to allow datetime parameters.
DECLARE @columns NVARCHAR(4000)
SELECT @columns = COALESCE(@columns + ',[' + cast( [Department] as varchar) + ']',
'[' + cast([Department] as varchar)+ ']')
FROM MonthlyScrap
GROUP BY [Department]
DECLARE @query NVARCHAR(4000)
DECLARE @startdt DATETIME
DECLARE @enddt DATETIME
SET @startdt = '20130301'
SET @enddt = '20130331'
SET @query = '
select *
from ( select [Department] As Dept, SUM(Scrapped * OperationCost) As ScrapValue, [TransactionDate]
from MonthlyScrap
where TransactionDate between @startdt and @enddt
Group by [Department], [TransactionDate]) As Datatable
PIVOT
(
SUM(ScrapValue)
FOR Dept IN (' + @columns + ')
) AS p Order by p.[TransactionDate]'
EXECUTE sp_ExecuteSQL @query,
N'@startdt datetime, @enddt Datetime',
@startdt, @enddt
