Рекурсивный отчет

С завидным постоянством продолжают поступать вопросы по сабж. Самый свежий был задан на Самарской User Group SQL Server. До этого спрашивали в Новосибе на TechDays, на форуме sqlclub.ru и т.д. Там же на форуме я в свое время опубликовал данный ответ. Но поскольку форум в настоящее время работает ненадежно, дублирую его здесь.

Под рекурсивным будем понимать отчет по parent-child таблице, т.е. которая связана сама на себя. Типичным примером является оргструктура, где начальники также являются сотрудниками, и связь строится по полям ID начальника сотрудника >- ID сотрудника. Мы уже разбирали пример построения такого отчета с помощью Report Designer в SQL Server 2005 в мае прошлого года в теме "Комплексная вводная дема по BI", п.8.2 (https://sqlclub.ru/forum/viewtopic.php?f=36&t=1123#p1754). Хотя на дворе уже давно SQL Server 2008, делается это идейно ровно так же. Любой, кто прочитал майский пост про 2005-й, без труда сумеет сделать в 2008-м и Report Builder 2.0. Тем не менее, для очистки совести.

Из пререквизитов требуется общее знание Reporting Services. По поводу инструмента Report Builder 2.0 можно прочитать тему "Знакомство с Reporting Services 2008. Ч.II" (https://sqlclub.ru/forum/viewtopic.php?f=36&t=1584).

Откройте Report Builder 2.0. Создайте Data Source на модельную базу AdventureWorksDW. Создайте на его основе DataSet, использовав в качестве запроса

select EmployeeKey, ParentEmployeeKey, FirstName, LastName, Title, DepartmentName, EmailAddress, Phone from AdventureWorksDW..DimEmployee

Вставьте элемент Table (в действительности это будет ипостась табликса) на поверхность отчета. Мышкой перетащите поля FirstName, LastName, Title, DepartmentName, EmailAddress, Phone датасета в ячейки второй строки таблицы. Новые колонки можете не добавлять. Просто кидайте поле в правую границу – они будут добавляться автоматически. Вот, что должно получиться у нас в итоге:

рис.1

В таблице на данный момент имеется только одна неявная сама собою получившаяся группа – детальные строки (Details). Ее можно видеть в левой нижней панели Row Groups. Откройте ее свойства.

На странице General нажмите кнопку Add и добавьте EmployeeKey из списка полей датасета. Это РК таблицы DimEmployee. По нему будет происходить группирование внутри группы Details.

рис.2

Перейдите на страницу Advanced и добавьте поле ParentEmployeeKey в качестве Recursive Parent для группы. Это поле является FK в parent-child связи таблицы самое на себя.

рис.3

Если необходимо добавить графическую функциональность развертки / свертки групп, перейдите на страницу Visibility и отметьте, что в момент начального запуска группы должны быть свернуты (Hide) и их развертываемость / свертываемость, т.е. видимость / невидимость будет переключаться текстбоксом FirstName (эта ячейка таблицы выделена на рис.4). Возле нее появится значок плюса/минуса.

рис.4

В завершении сделаем отступ в текстбоксе FirstName в зависимости от уровня иерархии. Перейдем в его свойство Padding и установим левый отступ равный выражению

=Level("Details") * 10 & "pt"

рис.5

Запускаем отчет:

рис.6

Идейно где-то так. Далее можно красоту наводить.