Sparkline и пользовательские агрегаты

Все хорошо, скажете вы, прочитав предыдущий пост. Однако описанный сценарий предполагает, что мы заносим в репортинг развернутую по обоим измерениям таблицу фактов, которую он сам группирует по времени и продуктам. Это логично, т.к. иначе как он построит график вдоль времени в каждой строчке? В то же время данный подход предполагает (коль скоро таблица фактов развернута), что агрегаты считаются внутри репортинга. Как быть, если в колонке "Деньги" на Рис.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.

Ссылки по теме

• Купить продукты Microsoft в интернет-магазине ITShop.ru
• Купить SQL Server 2008 в интернет-магазине ITShop.ru
• Курсы обучения компании Microsoft в интернет-магазине ITShop.ru
• Подписаться на рассылку "Вопросы и ответы по MS SQL Server"