В разработанный в предыдущей статье отчет я добавил таблицу с населением городов, которую взял здесь.
Рис.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 в отдельную колонку измерения Время, идущую параллельно измерению Город:
Рис.15
В отчете наряду с трендом хотелось бы отображать численность населения согласно последней переписи 2010 г. Для этого из Рис.15 нужно отобрать строки за 2010 г. и сджойнить их с рекордсетом Рис.15 по колонке Город:
Рис.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 и поставим ее равной значению [№] для «родительского» города. В целях экономии времени я проделаю это для подмосковных городов, т.к. идея понятна.
Рис.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 на
чтобы добавить в датасет поле 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сли кто-нибудь сочтет возможным проверить, буду признателен.
Рис.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сли кто-нибудь сочтет возможным проверить, буду признателен.