Построение скоркарды средствами SQL Server Reporting Services 2008R2. Часть 2.
Поздравляем с Новым годом тружеников Редмондщины, до которых он, наконец, добрался. Пожелаем им (и нам заодно) в наступившем году удачного SQL11.
Праздник завершает опоясывать планету, а мы тем временем продолжаем. Перетащим на рабочую поверхность отчета элемент управления табликс в ипостаси table. В данном примере набор колонок заранее известен и фиксирован, так что матрица здесь не понадобится.
Рис.1
Раскроем список полей DataSet1 и перетащим поочередно поля Subcategory, Sales_Amount, ProductShare, Gross_Profit_Margin, Gross_Profit_Margin_Status, Gross_Profit_Margin_Trend в ячейки области Data.
Рис.2
Перейдем в Report Builder к панели Row Groups внизу и добавим к детальным данным (Details) родительскую группу по полю Category датасета DataSet1, который является источником для данной области (таблицы):
Рис.3
Колонки, соответствующие группировочным полям, автоматически вставляются в начало таблицы. Поэтому их необязательно было перетаскивать вместе с остальными полями на этапе Рис.2.
Вставим выше строки с данными еще строку в пределах группы.
Рис.4
Выделим числовые ячейки в детальной строке и скопируем их в добавленную строку. Добавленная строка - группировочная, численные данные должны в нее входить с какой-либо агрегатной функцией. Когда никакой функции не указано, это воспринимается как функция First, т.е., допустим, взять [Sales Amount] из первой подкатегории внутри данной категории продуктов. Нас это не устраивает, т.к., в частности, для [Sales Amount] по категории требуется сумма входящих в нее подкатегорий. В Reporting Services имеется агрегатная функция Sum, но зачем суммировать, если все агрегаты давно посчитаны в Analysis Services, и Reporting Services достаточно просто попросить их оттуда. Для этого используется агрегатная функция Aggregate. Поставим ее вокруг численных данных в группировочной строке:
Рис.5
Запускаем отчет и видим, что все поистине замечательно, кроме того, что Reporting Services отказывается вытягивать OLAPовские агрегаты.
Рис.6
То ли сказывается Новый год, то ли ее OLAPовский data extension не понимает, че по чем агрегируется после выражения, которое у нас стоит по оси rows (1) в Скрипте 6 предыдущего поста. В предыдущем посте (Скрипт 2) отмечалось, что Reporting Services - штука очень умная, и, вместо того, чтобы просто отображать результаты Descendants, разработчики Reporting Services решили облегчить жизнь пользователям. Но не до такой же степени! Идем в DataSet1 и меняем nonempty(Product.[Product Categories].Product.Members, Measures.[Sales Amount]) на тупо NON EMPTY {[Product].[Product Categories].[Category].ALLMEMBERS, ([Product].[Product Categories].[Subcategory].ALLMEMBERS ) } .
В результате чего запрос DataSet1 приобретает вид:
with member Measures.ProductShare as (Product.[Product Categories].CurrentMember, Measures.[Sales Amount]) / (Product.[Product Categories].CurrentMember.Parent, Measures.[Sales Amount])
select
{Measures.[Sales Amount], Measures.ProductShare, KPIValue("Product Gross Profit Margin"), KPIStatus("Product Gross Profit Margin"), KPITrend("Product Gross Profit Margin")} on 0
, NON EMPTY {[Product].[Product Categories].[Category].ALLMEMBERS, ([Product].[Product Categories].[Subcategory].ALLMEMBERS ) } on 1
from [Adventure Works]
where StrToMember(@TimePeriod)
Скрипт 1
и функция Aggregate, наконец, начинает работать:
Рис.7
К сожалению, из-за того, что функцию nonempty в запросе пришлось заменить на NON EMPTY, в подкатегориях продуктов появились пустоты. NON EMPTY отсекает целиком пустые строки/столбцы, а статус и тренд данного KPI устроены так, что будут давать непустое значение даже при отсутствии фактических продаж. Попытка отфильтровать пустоты средствами Reporting Services дает ошибку, т.к. фильтр по деталям несовместим с функцией Aggregate. Вспоминается анекдот про хирурга со скальпелем: да что ж у меня сегодня ничего не получается-то!
Рис.8
Сведем категорию и подкатегорию товара в одну колонку, для чего перенесем категорию в правую ячейку, а оставшуюся слева колонку удалим.
Рис.9
Добавим отступ в дочерний уровень подкатегории - свойство ячейки Indent\LeftIndent. Если панель свойств не видна, в пункте меню View поставить галку чекбокс у Properties аналогично Рис.2 предыдущего поста.
Рис.10
Отформатируем численные значения. Форматирование выставляется в свойстве ячейки Format. Это обычные .NETовские форматные строки.
Рис.11
Откорректируем ширины колонок, размер шрифта. Переделаем заголовок колонки, в которой сейчас находятся категории и подкатегории продукта, в Продукт. Переименуем остальные колонки. Зададим заголовок отчета. Удалим за ненадобностью подвал.
Рис.12
В результате отчет приобретает следующий вид:
Рис.13
Сделаем раскрытие / свертку продуктовой иерархии. Встанем на ячейку с категорией и увидим в свойствах, что она называется Category1. Запомним этот полезный факт.
Рис.14
Встанем внизу на группу Details (Рис.8) и раскроем ее свойства на закладке Visibility. Скажем, что видимость детальной группы переключается текстбоксом Category1 и что начальное ее положение - скрыта:
Рис.15
Внешний вид отчета приобретает следующий вид:
Рис.16
При раскрытии категории появляются подкатегории:
Рис.17
Продолжение в следующем номере.
Алексей Шуленин