Геопространственные типы в отчетах SQL Server Reporting Services

Источник: habrahabr

alexejs

Типы Geometry и Geography имеются в SQL Server, начиная с версии 2008. Использующий их элемент управления Map на основе одноименного элемента управления компании Dundas появился в SQL Server Reporting Services 2008R2.

Открываем в SQL Server Data Tools (SSDT) проект типа Report Server Project:

Рис.1

На всякий случай напомню, что службы отчетности входят в состав бесплатной версии SQL Server Express (нужно брать вариант Express with Advanced Services). Об ограничениях бесплатного репортинга написано здесь. SSDT, по определению, бесплатны, берутся здесь. Вместо Report Server Project в SSDT желающие могут воспользоваться Report Builder - он тоже бесплатный.

В созданную в предыдущем посте таблицу с картами субъектов РФ я добавлю колонки, которые будут содержать численность населения согласно последней переписи, а также процент мужского населения. Прошу не усматривать в этом гендерного шовинизма, просто зачем хранить одновременно процент по обоим полам? Хорошо, давайте женского. В результате таблица приобретает следующий вид:

Рис.2

Добавим в репортинговый проект в SSDT новый источник данных

Рис.3

в котором зададим строку соединения с SQL Server. Папкой ниже создадим новый датасет на основе этого источника, который будет содержать результаты запроса SELECT ID, region, geom.Reduce(1000) as geom, Population, Female FROM regions2010. Сдвинемся еще ниже на папку Reports и создадим в проекте пустой отчет Add -> New Item -> Report. В панели Report Data, которая традиционно находится слева, кликнем правой кнопкой на папку Data Sources и добавим ссылку на созданный только что общий источник данных DataSource1. Аналогично добавим ссылку на общий датасет DataSet1. Увеличим рабочую поверхность отчета, раздвинув ее за правый нижний угол. Кликнем по ней правой кнопкой и выберем Insert -> Map:

Рис.4

Будет предложено выбрать источник для карты. Map Gallery - это карты, поставляющиеся вместе с Reporting Services. Они относятся к США и нам в данном случае без интереса. Вторым источником может быть shape-файл, но мы уже затянули его в прошлом посте его в таблицу SQL Server. Выбираем третий тип источника - SQL Server spatial query и указываем для него DataSet1. Далее будет предложено выбрать в нем поле типа Geometry/Geography. В данном случае такое поле в датасете одно, и оно уже автоматически проставлено в комбобоксе. Я планирую раскрасить полигоны в зависимости от значений другого поля из датасета, поэтому в качестве способа визуализации карты выбираю Color Analytical Map:

Рис.5

Если бы поле, на основе которого будет производиться раскраска, находилось в другом датасете, здесь же можно было бы связать его с первым, но в нашем случае все просто, поэтому Choose an existing dataset. На следующем экране предлагается выбрать цветовую гамму. Поле, по которому будет производиться раскраска - это численность населения (Population). Я хочу, чтобы цвет был гуще, чем больше населения, поэтому выбираю правило закрашивания Light - Dark.

Рис.6

Переходим на закладку Preview, чтобы посмотреть, что получилось. Получилось фигово - вместо карты пустота. Это происходит потому, что карта по умолчанию ожидает тип Geography, а в нашем случае колонка geom имеет тип Geometry. Возвращаемся на закладку Design, кликаем правой кнопкой по картинке, выбираем Viewport Properties:

Рис.7

и исправляем ожидание на Coordinate system = Planar (X, Y):

Рис.8

Теперь карта будет отображена нормально:

Рис.9

Возвращаемся на закладку Design. Свойства Viewport можно также поправить в панели Properties, выделив его непосредственно на рабочей поверхности отчета или выбрав как дочерний элемент элемента управления Map:

Рис.10

Удаляем справа вверху прямоугольник Title, чтобы освободить больше места под карту. Или прямоугольник со шкалой слева внизу - без разницы. На всякий случай - восстановить убиенных можно из контекстного меню Map: соответственно, Add Legend и Show Color Scale.

Рис.11

По умолчанию размер легенды установлен в Auto, что не позволяет изменить размер прямоугольника, только возить его по поверхности карты. Это поведение легко поправить, если задать Auto = False в свойстве Size. Графически ресайзить, потянув за угол, будет по-прежнему нельзя, зато появляется возможность в свойствах вбить Height и Width руками. Похожее поведение попадалось у индикаторов или еще каких-то унаследованных от Дундаса элементов. На общую длину прямоугольника влияет размер и формат шрифта подписей. Я убрал лишние знаки после запятой в свойстве LabelFormat:

Рис.12

В списке Map Layers, который сейчас состоит из одного слоя, выберем Polygon Color Rule в контекстном меню этого слоя:

Рис.13

В пункте General можно перевыбрать поле датасета, на основе которого производится раскраска, К примеру, к чему нам функция SUM(), которую подсунул на Рис.6 визард построения карты? Нечего агрегировать в таблице regions2010, там каждой строчке соответствует свой субъект. Еще бы я поделил поле Population на 1000, чтобы сделать Labels, а, следовательно, длину прямоугольника MapColorScale (см.Рис.12) покороче. Выражение приобретает вид =Fields!Population.Value / 1000. Можно также откорректировать начальный и конечный цвета.

Рис.14

а в пункте Distribution - кол-во уровней градации:

Рис.15

Возможны 4 варианта: Equal Interval, когда диапазон от минимального до максимального значений Data Field будет разбит на Number of subranges равных отрезков; Equal Distribution, когда длины отрезков подбираются так, чтобы в каждый попадало приблизительно одинаковое число записей; Optimal, когда инструмент решает сам, и Custom, когда мы в явном виде задаем начальную и конечную границы каждого интервала.
Как и на Рис.10 в случае свойства Viewport, свойства слоя доступны из панели свойств либо через иерархию объектов, начиная с верхнеуровнего Map, в качестве свойства которого присутствует коллекция Layers:

Рис.16

либо, если хорошенько прицелиться, слой можно выделить непосредственно в составе карты на рабочей поверхности отчета:

Рис.17

Остается поправить расположение карты и ее размер. Это делается на Рис.8 в пункте Center and Zoom или в свойстве View объекта Viewport в панели свойств Рис.10. Подвигать Viewport внутри карты можно мышкой или при помощи синих стрелочек Map view или просто задав координаты центра CenterX/CenterY. Zoom меняет размер Viewport, не позволяя искажать соотношения ширина/длина несмотря на то, что внутри свойства Size есть подсвойства Height и Width. В панели свойств они отмечены как недоступные, но в документации про readonly ничего не сказано, поэтому я поменял их непосредственно в rdl. Ноль эффекта. По-видимому, зрелищностью растягивания картинки пожертвовали в угоду соблюдения пропорций в геометрии/географии.

Рис.18

В зависимости от выбранного распределения (Рис.15-17) демографическая ситуация в стране может выглядеть тускло (равноинтервальная шкала):

Рис.19

или в несколько более ярких тонах (равнораспределенная шкала):

Рис.20

Домашнее задание. Постройте аналогичный отчет, отобразив на карте плотность населения = численность населения в субъекте / площадь субъекта. Площадь считается как geom.STArea().

Я добавлю в отчет немного интерактивности, чтобы в зависимости от выбранного значения параметра (Муж., Жен., Общ.) на карте показывалась численность мужского населения, женского или вместе - то, что мы видим сейчас. В панели Report Data (слева - см.Рис.4) кликаем правой кнопкой по папке Parameters и говорим Add Parameter. В пункте General задается название и тип параметра. Опция Allow blank value отличается от Allow null value тем, что доступна только когда отмечено Allow multiple values и означает, что список может быть пустым. Скрытый параметр не показывается в интерфейсе пользователя, однако к нему можно обратиться при вызове отчета через URL или оформлении подписки. Внутренний параметр не засвечивается наружу никак.

Рис.21

В пункте Available Values указывается, будет ли пользователь вводить значение параметра вручную или выбирать из списка доступных величин. Список может быть задан в виде явного перечисления или датасета, который нужно создать аналогично тому, как мы создавали его для рисования карты. В данном случае доступных величин будет всего 3 заранее известных - их легко перечислить явно. Жмем три раза кнопку Add, вводим Label и Value. Пользователю показывается колонка Label, но в значение параметра попадает соответствующая величина из колонки Value. Порядок следования в случае явного задания можно менять при помощи находящихся в строке кнопок Add/Delete кнопок-синих стрелок.

Рис.22

В пункте Default Values задается значение параметра по умолчанию. В нашем случае оно будет равно 0, т.е. мужское и женское население в совокупности, как было на Рис.20. В качестве значения по умолчанию можно также указать колонку датасета - будет рассматриваться его первая запись.

Рис.23

Теперь возвращаемся к Рис.14 и корректируем аналитическое поле, на основе которого производится раскраска карты. Жмем кнопку со значком функции справа от поля ввода и задаем следующее выражение: =Fields!Population.Value * iif(Parameters!ReportParameter1.Value = 0, 1, iif(Parameters!ReportParameter1.Value = 1, 1 - Fields!Female.Value / 100, Fields!Female.Value / 100 ) )
Двойной клик по значению в списке Values вставляет его на позицию курсора в верхнем текстбоксе, где редактируется выражение.

Рис.24

Выражение означает, что если передан параметр со значением 0, нужно закрашивать карту на основе численности населения субъекта из колонки Population. Если 1 (мэ) - умножить численность населения на процент мужского населения = Population * (1 - доля женского), если 2 (жо) - умножить численность населения на процент женского населения. Запускаем отчет. Первоначально он ничего не спрашивает, т.к. выполняется со значением параметра по умолчанию (0). Затем можно выбрать пол из списка значений параметра и нажать кнопку View Report. Карта перерисуется с учетом численности населения отмеченного пола. Сильных изменений в покраске регионов не произойдет, т.к. мужчин и женщин везде примерно поровну, но смысл понятен.

Рис.25

Ссылки по теме