Sparkline и пользовательские агрегаты
Все хорошо, скажете вы, прочитав предыдущий пост. Однако описанный сценарий предполагает, что мы заносим в репортинг развернутую по обоим измерениям таблицу фактов, которую он сам группирует по времени и продуктам. Это логично, т.к. иначе как он построит график вдоль времени в каждой строчке? В то же время данный подход предполагает (коль скоро таблица фактов развернута), что агрегаты считаются внутри репортинга. Как быть, если в колонке "Деньги" на Рис.12 предыдущего поста по бизнес-логике требуется не сумма, а какой-нибудь более хитрый агрегат, реализовать который средствами репортинга проблематично? Предположим, имеется некоторый датасет, показывающий пользовательский агрегат вдоль измерения "Продукты". Например, последние ненулевые месячные продажи:
Скрипт 1 Наверное, пример можно было придумать лучше, т.к. подобный агрегат, как и сумму, тоже можно реализовать внутри репортинга, но не будем на это отвлекаться. Пусть имеется датасет вида "Категория продукта", "Подкатегория", "Некоторая величина", который бы мы хотели отобразить в отчете, а как эта величина посчитана - дело, по большому счету, десятое. Рис. 1 В отчете мы бы хотели добавить к датасету колонку со спарклайном, отражающим поведение некоторой (вообще говоря, другой) численной характеристики для каждой подкатегории продукта вдоль ортогонального измерения. Это означает, что нам нужно в датасете умножить измерение Продукт на это измерение и добавить к произведению новую меру. Пусть ортогональным произведением, которое протянется вдоль оси Х спарклайна, как и в предыдущем посте, остается Время, а численной характеристикой (ось Y) будут продажи. Добавляем продажи в разрезе по времени и подкатегориям в датасет Рис.1, сджойнив его с датасетом Скрипт 1 из предыдущего поста:
Скрипт 2 Рис. 2 Пользовательский агрегат внутри каждой подкатегории продукта является константой вдоль времени, т.к., по условию, он зависит только от измерения Продукт. Замечательно. Переносим датасет в отчет. Аналогично Рис.3-5 предыдущего поста, сделайте матрицу, сгруппированную по категории продукта (Род), подкатегории (Вид) и перенесите в ячейку с серой надписью Data поле ПользАгрегат. По умолчанию ему будет присвоена агрегатная функция Sum. Измените ее на
Скрипт 3 Рис. 3 Выражение означает, что вместо суммирования мы будем брать первое значение внутри каждой группы подкатегорий, которая у нас называется Вид. Как отмечалось выше (Рис.2), внутри подкатегорий (в разрезе по времени) ПользАгрегат не меняется, так что тут без разницы, первое значение брать в группе или последнее. Справа от колонки Вид добавляем в матрицу новую колонку в пределах этой группы: Рис. 4 и кладем в нее sparkline аналогично Рис.8-10 предыдущего поста. Рис. 5 По оси Y пускаем поле Деньги, по оси Х - сгруппированные по годам месяцы. Смотрим в Preview, что получилось: Рис. 6 По-моему, в аккурат то, что заказывали. Рассмотрим второй способ, который с точки зрения проектирования отчета практически ничем не будет отличаться от того, что мы только что проделали, т.к. состоит в том, чтобы считать пользовательские агрегаты не в реляционной базе, а в кубике. На основе реляционной базы AdventureWorksDW2008R2 имеется многомерная база, которую можно взять все там же. Напишем MDX-запрос, делающий идейно ровно то же, что и Скрипт 1 + Скрипт 2: он выдает подкатегории продуктов с некоторым пользовательским агрегатом LastNonEmptyMonth (это основа будущей матрицы в репортинге) и внутри каждой подкатегории разворачивает еще динамику продаж по месяцам года (это по чему будет строиться спарклайн в отдельной ячейке напротив каждой подкатегории):
Скрипт 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. Тогда отчет приобретает вид Рис.19. Автор: Алексей Шуленин |