В разработанный в предыдущей статье отчет я добавил таблицу с населением городов, которую взял здесь.

Рис.1

Я скопировал таблицу в Excel:

Рис.2

и перенес ее в базу данных SQL Server с помощью мастера импорта-экспорта:

Рис.3

Рис.4

Рис.5

Рис.6

Рис.7

Рис.8

Рис.9

Откроем структуру импортированной таблицы в SSMS (Design) и добавим первичный ключ по полю [№]:

Рис.10

Предварительно в настройках SSMS (SSMS -> Tools -> Options) следует убрать галку, по умолчанию запрещающую внесение в структуру таких изменений, что потребуют пересоздание таблицы:

Рис.11

Мастер импорта-экспорта переносит таблицы в схему dbo. Если требуется изменить схему, это можно сделать там же, в дизайнере таблицы (F4 — Properties). В T-SQL этому соответствует команда ALTER SCHEMA… TRANSFER.

Рис.12

Появившейся, по-моему, еще в SQL Server 2000, но слабо известной возможностью является аннотирование объектов (Properties из контекстного меню -> расширенные свойства). Можно создавать любые пользовательские свойства и задавать им скалярные значения. В T-SQL этому соответствуют хранимые процедуры sp_addextendedproperty, sp_updateextendedproperty, sp_dropextendedproperty, а также функция fn_listextendedproperty. Я сохраню ссылку, откуда импортировал таблицу:

Рис.13

Теперь таблицу нужно добавить в отчет, представив для наглядности численность населения по годам в виде графика-тренда в ячейке напротив каждого города. Элемент управления называется sparkline, мы его проходили в этой статье. Вначале таблицу CityPopulation

Рис.14

требуется нормализовать, превратив стоящие по колонкам годы 1897, 1926, ..., 2010 в отдельную колонку измерения Время, идущую параллельно измерению Город:

select Город, Год, Численность from CityPopulation unpivot (Численность for Год in ([1897], [1926], [1939], [1959], [1970], [1979], [1989], [2002], [2010])) t

Рис.15

В отчете наряду с трендом хотелось бы отображать численность населения согласно последней переписи 2010 г. Для этого из Рис.15 нужно отобрать строки за 2010 г. и сджойнить их с рекордсетом Рис.15 по колонке Город:

with cte as (select Город, Год, Численность from CityPopulation unpivot (Численность for Год in ([1897], [1926], [1939], [1959], [1970], [1979], [1989], [2002], [2010])) t)
select t1.Город, t1.Год, t1.Численность, t2.ЗаКрайнийГод from cte t1 join (select Город, Численность as ЗаКрайнийГод from cte where Год = 2010) t2 on t1.Город = t2.Город

Рис.16

То, что нужно. Идем в дизайнер отчетов в SSDT и добавляем новый датасет:

Рис.17

в котором прописываем источником данных запрос Рис.16. В качестве соединения, на котором будет выполняться данный запрос, указываем все тот же DataSource1, который использовался для рисования карты в предыдущей статье.

Рис.18

Рис.19

Добавляем на рабочую поверхность отчета элемент управления Matrix. Если не видна панель с полями созданных датасетов, ее можно включить из верхнего меню View -> Report Data (нижняя строчка). Перетащим поле Город в 1-ю ячейку 2-й строчки матрицы, а справа от него поместим поле ЗаКрайнийГод, которое содержит данные переписи 2010 г. Исправим агрегатную функцию Sum, которая подставляется по умолчанию, на агрегатную функцию First (Expression в свойствах ячейки). Или Last, или Min, или Max — непринципиально. Мера ЗаКрайнийГод не изменяется вдоль измерения Время. Она изменяется только вдоль измерения Город — Рис.16.

Рис.20

Сейчас датасет Рис.16 сгруппирован по измерению Город. Все остальные измерения (Время) свернуты. Кликнем правой кнопкой по ячейке в правой колонке и выберем из контекстного меню пункт Insert Column -> Inside Group — Right.

Рис.21

В качестве оси абсцисс (Category Croups) для тренда добавляем свернутое измерение Время (Год), а по оси ординат располагаем скрытую меру Численность, изменяющуюся вдоль обоих измерений. Функция Sum, которую Report Designer подставляет по умолчанию, означает, что если бы в таблице CityPopulation имелись другие измерения, свернутые в матрице, к ним бы применялась данная агрегатная функция. Но других измерений в нашем случае нет, поэтому что выбирать в качестве агрегата — без разницы.

Рис.22

Рассплитим заголовочную ячейку и поставим соответствующие заголовки колонок.

Рис.23

Откорректируем шрифт, цвет, выравнивание. Желающие могут навести дополнительные красоты. Я ограничусь тем, что уберу в отчете мягкое разбиение по страницам, чтобы при рендеринге в HTML вся таблица городов выводилась целиком. Для этого нужно в панели свойств выбрать в комбобоксе среди имеющихся элементов управления, собственно, Report, раскрыть свойство InteractiveSize и поставить Height = 0.

Рис.24

Чтобы цифры в средней колонке не примыкали к графику, в свойствах ячейки надо увеличить Padding -> Right. Но это не главное. Сейчас города выдаются в виде плоского списка, упорядоченного по алфавиту. Наверное, будет более красиво выстроить их в соответствии с территориальной иерархией. Для этого на таблице CityPopulation следует завести иерархию parent-child. К сожалению, в исходной таблице Рис.1 она отсутствовала, поэтому ее придется заводить руками. Создадим новую колонку под названием ParentN и поставим ее равной значению [№] для «родительского» города. В целях экономии времени я проделаю это для подмосковных городов, т.к. идея понятна.

with cte as (select [№], Город, 0 as level, cast(str([№], 4) as varchar(max)) as ord from CityPopulation where ParentN is null 
union all select t.[№], t.Город, level + 1, ord +  str(t.[№], 4) from CityPopulation t join cte on t.ParentN = cte.[№])
select replicate(' ', level * 2) + Город from cte order by ord

Рис.25

Досадно, что функции LAG()/LEAD() не работают в рекурсивном СТЕ. Вместо строки в качестве поля ord можно было бы использовать HierarchyID, решив задачу преобразования parent-child таблицы к иерархии, что называется, влет:

with cte as (select [№], Город, HierarchyID::GetRoot().GetDescendant(null, null) as hid from CityPopulation where ParentN is null
union all select t.[№], t.Город, cte.hid.GetDescendant(lag(t.hid, 1) over (partition by t.ParentN order by t.[№]), null) from CityPopulation t join cte on t.ParentN = cte.[№])
select * from cte order by hid

Но увы — не воспринимается t.hid, поэтому Рис.25 — по старинке. Возвращаемся к отчету. Измените запрос в DataSet2 на

WITH cte AS (SELECT Город, Год, Численность
                          FROM   CityPopulation UNPIVOT (Численность FOR Год IN ([1897], [1926], [1939], [1959], [1970], [1979], [1989], [2002], [2010])) t)
    SELECT cp.[№] N, t1.Город, t1.Год, t1.Численность, t2.ЗаКрайнийГод, cp.ParentN
    FROM  cte t1 JOIN
                       (SELECT Город, Численность AS ЗаКрайнийГод
                        FROM   cte
                        WHERE Год = 2010) t2 ON t1.Город = t2.Город
join CityPopulation cp on t1.Город = cp.Город

чтобы добавить в датасет поле ParentN

Рис.26

Нажмите кнопку Refresh Fields, чтобы обновить список полей датасета в Report Data. Кнопка Refresh Fields имеется также, если в Report Data сказать Edit…

Рис.27

Откройте свойства группы строк Город:

Рис.28

Измените критерий группирования с поля Город на поле N:

Рис.29

Встаньте на Advanced слева. Выберите в качестве Recursive parent поле ParentN, т.к. таблица CityPopulation связана сама на себя по полям № >- ParentN. N мы указали только что на Рис.29 в качестве, грубо говоря, РК группы. Остается задать внешний ключ связывания, т.е. поле ParentN:

Рис.30.

Переходим на закладку Visibility. Говорим, что вначале все должно быть в свернутом состтоянии (Hide), и у ячейки по имени «Город» (см.Рис.20, Properties) должен стоять плюсик, по нажатию которого должны раскрываться дочерние элементы (Dispaly can be toggled by this report item = Город):

Рис.31

Осталось только подчеркнуть иерархию дочерних элементов отступами. Три вещи для этого надо вспомнить. Первое – отступы регулируются при помощи свойства Padding (Left), второе – значения свойств могут включать формулы (Expression), тогда они оцениваются динамически на лету, и третье – уровень вложенности в RS определяется функцией Level(<масштаб>). Масштаб – это то группирование, относительно которого мы хотим получить уровень, т.е. в данном случае связанная сама на себя иерархией parent-child группа Город. Встаем на ячейку (Textbox) Город, находим в панели Properties свойство Padding -> Left

Рис.32

и задаем выражение для левого отступа =Level(«Город») * 15 & «pt»:

Рис.33

Запускаем отчет. Обращаем внимание, что теперь подмосковные города не показываются в общем списке, зато возле г.Москва появляется значок +, по нажатии на который он раскрывает дочерние элементы. Пустые тренды по некоторым городам означают, что в исходных данных Рис.1 для них присутствовали только результаты последней переписи.

Рис.34

В данном примере использовался SQL Server 2012 Developer Edition, потому что так сложилось исторически. Cлужбы отчетности входят в редакцию SQL Server Express with Advanced Services, т.е. на бесплатной редакции это все тоже. по идее, должно работать. Eсли кто-нибудь сочтет возможным проверить, буду признателен.

Добавление в отчет колонки с трендами