SSRS 2008 R2 Как создать диаграмму, содержащую две серии диаграмм, но сгруппировать только одну из них?

После того, что казалось мне днями работы над этим, я наконец понял способ сделать это, которое будет работать с моими собственными данными. В зависимости от того, как организованы ваши данные, могут потребоваться дополнительные шаги в SQL-запросе.

Вот небольшой псевдо-образец данных, которые мне нужны для построения графика.

select Department, SUM(Scrapped * OperationCost) As ScrapValue, TransactionDate
from MonthlyScrap
where TransactionDate between '03-01-2013' and '03-31-2013'
group by Department, TransactionDate

Как и ожидалось, это дает выходной набор данных из трех полей с именами Department, ScrapValue и TransactionDate. Поместить это в составную диаграмму в SSRS очень просто. Просто перетащите TransactionDate в категории, отдел в группы серий и ScrapValue в поля значений в элементе управления данными диаграммы.

Однако OP хочет также отобразить ВСЕГО значений, в данном случае ScrapValues, на графике в виде одной линии. Из-за того, как данные сгруппированы в запросе и на диаграмме, это НЕ будет работать. Если вы снова добавите данные в качестве значений (обратите внимание, что они уже суммированы при первом добавлении из-за стиля диаграммы с накоплением), тогда он просто отображает строку для каждого отдельного отдела из-за того, как вы группируете ряды. Даже повторное суммирование не имеет никакого эффекта. Таким образом, чтобы выполнить эту работу, вы должны транспонировать (развернуть) свои данные в запросе SQL (спасибо Дейву;)), чтобы правильно сгруппировать их.

Вот новый запрос.

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

Теперь у вас есть набор данных с полями TransactionDate, Molding, Machining и Grinding, сгруппированный по дням (одна строка в день со значениями для каждого отдела), что упрощает добавление окончательного вычисляемого поля для итогов (= Fields! Grinding .Значение + Поля! Формование.Значение + Поля! Обработка.Значение). Теперь, когда вы создаете диаграмму с накоплением, вы перетаскиваете TransactionDate в категории, а все остальные поля в значения. Вам больше не нужна группировка серий, так как об этом позаботился запрос. Наконец, измените тип диаграммы вычисляемого поля на линейную, и теперь у вас есть столбчатая диаграмма с накоплением с единственной линией, представляющей итоги!

(Примечание: изображение отображает промежуточные итоги на вторичной оси Y, а не только итоги, но по-прежнему используется тот же базовый метод)

Изображение диаграммы

Вот мой последний запрос, в котором я добавил динамический SQL для получения значений диаграммы и превратил его в вызов хранимой процедуры, чтобы разрешить параметры datetime.

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