• Статья
Построение скоркарды средствами SQL Server Reporting Services 2008R2. Часть 2

Ссылка на 1-ю часть.

Поздравляем с Новым годом тружеников Редмондщины, до которых он, наконец, добрался. Пожелаем им (и нам заодно) в наступившем году удачного 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

Продолжение в следующем номере.

Автор: Алексей Шуленин