Все хорошо, скажете вы, прочитав предыдущий пост. Однако описанный сценарий предполагает, что мы заносим в репортинг развернутую по обоим измерениям таблицу фактов, которую он сам группирует по времени и продуктам. Это логично, т.к. иначе как он построит график вдоль времени в каждой строчке? В то же время данный подход предполагает (коль скоро таблица фактов развернута), что агрегаты считаются внутри репортинга. Как быть, если в колонке "Деньги" на Рис.12 предыдущего поста по бизнес-логике требуется не сумма, а какой-нибудь более хитрый агрегат, реализовать который средствами репортинга проблематично?
Предположим, имеется некоторый датасет, показывающий пользовательский агрегат вдоль измерения "Продукты". Например, последние ненулевые месячные продажи:
if object_id('LastNonEmptyMonthBySubcategory', 'V') is not null drop view LastNonEmptyMonthBySubcategory
go
create view LastNonEmptyMonthBySubcategory as
with cte(Род, Вид, Год, Месяц, n, ПользАгрегат) as (
select pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName, d.CalendarYear, d.MonthNumberOfYear,
row_number() over (partition by pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName order by d.CalendarYear desc, d.MonthNumberOfYear desc),
sum(s.SalesAmount) from dbo.FactInternetSales s
join dbo.DimProduct p on s.ProductKey = p.ProductKey
join dbo.DimProductSubcategory psc on p.ProductSubcategoryKey = psc.ProductSubcategoryKey
join dbo.DimProductCategory pc on psc.ProductCategoryKey = pc.ProductCategoryKey
join dbo.DimDate d on s.OrderDateKey = d.DateKey
group by pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName, d.CalendarYear, d.MonthNumberOfYear
)
select Род, Вид, ПользАгрегат from cte where n = 1
go
select * from LastNonEmptyMonthBySubcategory
Скрипт 1
Наверное, пример можно было придумать лучше, т.к. подобный агрегат, как и сумму, тоже можно реализовать внутри репортинга, но не будем на это отвлекаться. Пусть имеется датасет вида "Категория продукта", "Подкатегория", "Некоторая величина", который бы мы хотели отобразить в отчете, а как эта величина посчитана - дело, по большому счету, десятое.
Рис.1
В отчете мы бы хотели добавить к датасету колонку со спарклайном, отражающим поведение некоторой (вообще говоря, другой) численной характеристики для каждой подкатегории продукта вдоль ортогонального измерения. Это означает, что нам нужно в датасете умножить измерение Продукт на это измерение и добавить к произведению новую меру. Пусть ортогональным произведением, которое протянется вдоль оси Х спарклайна, как и в предыдущем посте, остается Время, а численной характеристикой (ось Y) будут продажи. Добавляем продажи в разрезе по времени и подкатегориям в датасет Рис.1, сджойнив его с датасетом Скрипт 1 из предыдущего поста:
with ДатасетИзПредыдущегоПоста as (
select pc.EnglishProductCategoryName Род, psc.EnglishProductSubcategoryName Вид, d.CalendarYear Год, d.MonthNumberOfYear Месяц, sum(s.SalesAmount) Деньги from dbo.FactInternetSales s
join dbo.DimProduct p on s.ProductKey = p.ProductKey
join dbo.DimProductSubcategory psc on p.ProductSubcategoryKey = psc.ProductSubcategoryKey
join dbo.DimProductCategory pc on psc.ProductCategoryKey = pc.ProductCategoryKey
join dbo.DimDate d on s.OrderDateKey = d.DateKey
group by pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName, d.CalendarYear, d.MonthNumberOfYear
)
select t1.Род, t1.Вид, t2.Год, t2.Месяц, t1.ПользАгрегат, t2.Деньги from LastNonEmptyMonthBySubcategory t1
join ДатасетИзПредыдущегоПоста t2 on t1.Род = t2.Род and t1.Вид = t2.Вид
order by 1, 2, 3, 4
Скрипт 2
Рис.2
Пользовательский агрегат внутри каждой подкатегории продукта является константой вдоль времени, т.к., по условию, он зависит только от измерения Продукт. Замечательно. Переносим датасет в отчет. Аналогично Рис.3-5 предыдущего поста, сделайте матрицу, сгруппированную по категории продукта (Род), подкатегории (Вид) и перенесите в ячейку с серой надписью Data поле ПользАгрегат. По умолчанию ему будет присвоена агрегатная функция Sum. Измените ее на
=First(Fields!ПользАгрегат.Value, "Вид")
Скрипт 3
Рис.3
Выражение означает, что вместо суммирования мы будем брать первое значение внутри каждой группы подкатегорий, которая у нас называется Вид. Как отмечалось выше (Рис.2), внутри подкатегорий (в разрезе по времени) ПользАгрегат не меняется, так что тут без разницы, первое значение брать в группе или последнее.
Справа от колонки Вид добавляем в матрицу новую колонку в пределах этой группы:
Рис.4
и кладем в нее sparkline аналогично Рис.8-10 предыдущего поста.
Рис.5
По оси Y пускаем поле Деньги, по оси Х - сгруппированные по годам месяцы. Смотрим в Preview, что получилось:
Рис.6
По-моему, в аккурат то, что заказывали.
Рассмотрим второй способ, который с точки зрения проектирования отчета практически ничем не будет отличаться от того, что мы только что проделали, т.к. состоит в том, чтобы считать пользовательские агрегаты не в реляционной базе, а в кубике. На основе реляционной базы AdventureWorksDW2008R2 имеется многомерная база, которую можно взять все там же. Напишем MDX-запрос, делающий идейно ровно то же, что и Скрипт 1 + Скрипт 2: он выдает подкатегории продуктов с некоторым пользовательским агрегатом LastNonEmptyMonth (это основа будущей матрицы в репортинге) и внутри каждой подкатегории разворачивает еще динамику продаж по месяцам года (это по чему будет строиться спарклайн в отдельной ячейке напротив каждой подкатегории):
with member Measures.LastNonEmptyMonth as Tail(nonempty([Date].[Calendar].[Month].Members * [Measures].[Internet Sales Amount]), 1).Item(0)
select {[Measures].[Internet Sales Amount], Measures.LastNonEmptyMonth} on 0,
nonempty ([Product].[Product Categories].[Subcategory].Members * [Date].[Calendar].[Month].Members, [Measures].[Internet Sales Amount]) on 1
from [Adventure Works]
Скрипт 4
Рис.7
Аналогично, перетаскиваем этот датасет в отчет, предварительно заведя в отчете новый источник данных для Analysis Services:
Рис.8
Разработчики Reporting Services постарались максимально облегчить ввод MDX-запросов и создали специальный дизайнер для их построения, поэтому просто так текст запроса ввести нельзя. Можно нажать на значок функции, но тогда текст запроса будет считаться выражением, и список полей недоступен.
Рис.9
Приходится все-таки зайти в дизайнер запросов (кнопка Query Designer), отжать в строке меню значок Design Mode, после чего становится возможно ввести текст произвольного MDX-запроса и его выполнить:
Рис.10
Обратите внимание, что датасет получается полнее, чем мы видели на Рис.7. В нем присутствуют не только члены заказанных в запросе уровней измерений, например, Subcategory, но и автоматически подтягиваются колонки MEMBER_NAME с родительских уровней, например, Category.
Совершенно аналогично кидаем на отчет матрицу, натаскиваем в нее построчную группу из поля Category и дочернюю по отношению к ней из поля Subcategory, переносим в область Data поле LastNonEmptyMonth, символизирующее собой пользовательский агрегат по подкатегориям, заходим у него в Expression и убираем функцию Sum, которую автоматически норовит подставить Report Designer.
Рис.11
Добавляем справа еще одну колонку в пределах текущей группы:
Рис.12
в которую переносим Sparkline, пуская аналогично Рис.5 поле Internet Sales Amount вдоль вертикальной оси графика, а Calendar_Year и Month - вдоль горизонтальной.
Рис.13
Рассплитим аналогично Рис.11 предыдущего поста заголовочную ячейку Last Non Empty Month и вобьем над колонкой графиков заголовок "Динамика продаж". Отцентрируем и выделим жирным цветом заголовки, отформатируем числовую ячейку =Fields!LastNonEmptyMonth.Value:
Рис.14
Смотрим в Preview, что получилось:
Рис.15
Операция прошла хорошо, жаль только, что больной об этом не узнает. Мне кажется, внешний вид графиков не очень совпадает с тем, что мы видели на Рис.6. Например, там напротив Bike Racks тренд в конце шел на спад, а здесь, наоборот, радостно растет. По цифрам (см. Рис.2, колонки Год, Месяц, Деньги или Рис.7, Internet Sales Amount ) выходит, что в первом случае графики больше походили на правду, а здесь sparkline показывает какую-то лажу. Чтобы выяснить, в чем дело, можно проконвертировать sparkline в полноценный chart с подписями вдоль осей, как делалось в предыдущем посте, Рис.14. На этот раз мы поступим по-другому. Добавим в матрицу поля Calendar_Year и Month в виде колонок после Subcategory:
Рис.16
Все посмотрели на колонку Month и все поняли. По какой-то причине она решила месяцы упорядочить по алфавиту, хотя в датасете (Рис.10) все нормально.
Привычно отправляемся в свойства sparkline и выбираем свойства Category Group, соответствующей Month. Встаем на закладку Sorting, в ней - на строчку Sort by [Month] и жмем кнопку Delete.
Рис.17
Возвращаемся к матрице и удаляем из нее за ненадобностью колонки Calendar Year и Month вместе с ассоциированными с ними группами, т.к. мы уже разобрались, где тут собака порылась.
Рис.18
Смотрим в очередной раз Preview:
Рис.19
По-моему на этот раз все нормально.
Примечание. В моем случае после удаления сортировки спарклайны оставались выглядеть по-старому, и никакой refresh не помогал. Я списываю это на глюк СТР3. Пришлось выкинуть sparkline из ячейки, занести снова, повторить привязку полей Рис.13, после этого сразу удалить в нем сортировку для группы Month Рис.17 и только после этого сказать Preview.