Программное совершенствование отчетов SSRS

Службы SQL Server Reporting Services (SSRS) - платформа для подготовки отчетов корпоративного уровня, размещаемая над широко распространенными службами в серверных технологиях Windows, в том числе SQL Server, ASP.NET и Microsoft. NET Framework. Одно из самых важных качеств SSRS - возможность расширить функции подготовки отчетов с помощью пользовательского кода и программных модулей. В этой статье я поясню, насколько пользовательский код и модули практически применимы при формировании ежедневных отчетов, и расскажу о встроенных модулях SSRS и концепции настраиваемых сборок. Затем будут даны пошаговые инструкции по подготовке специализированной сборки с помощью Visual Studio и ее использования для построения набора данных динамического отчета.

Выбор инструмента

Программисты привыкли использовать инструменты с возможностями объектно-ориентированного проектирования (например, управление экземплярами объектов, интерфейсы, наследование классов) и моделями программирования, управляемыми событиями, для построения пользовательских приложений и компонентов приложений. Важно понимать, что для практического применения SSRS не поддерживает таких программных методов и моделей, и на то есть веские причины. SSRS - платформа подготовки отчетов, а не инструмент программирования.

Во время обсуждений методов подготовки отчетов в моем блоге по SQL Server BI и на других форумах мне иногда приходится встречать такие мнения программистов: "Мне не нужен такой инструмент подготовки отчетов, как SSRS. Я могу просто спроектировать специализированное веб-приложение, а затем самостоятельно управлять всем с помощью программных объектов. NET". Я уважаю это мнение и считаю такой подход обоснованным, если требуется управлять каждым элементом пользовательского интерфейса, поведением и взаимодействием. С помощью кода событий и пользовательских элементов управления можно добавить удобные функции, просто недоступные в SSRS. Но при этом необходимо предусмотреть, что произойдет, если объем данных в пользовательском отчете окажется в 100 раз больше ожидаемого или отчет будет запущен несколькими пользователями одновременно. И как удовлетворить потребность пользователя просмотреть отчет в разнообразных форматах? Такие вопросы успешно решаются в SSRS без дополнительных усилий со стороны программиста.

Основными компонентами отчета - соединениями данных, выполнением запросов, наборами результатов, кэшированием данных и т.д. - следует управлять с помощью встроенных функций, не вдаваясь в сложное программирование. Важно уметь различать задачи, успешно решенные в SSRS, чтобы не ломать напрасно голову над сложными приемами программирования.

Модули

В основе SSRS лежит расширяемая архитектура. Признаюсь, что начиная работать с SSRS в 2003 году, я был очарован расширяемостью служб и возможностью добавлять программный код в решения для отчетов. Как программист, работающий в. NET, я полагал, что отчеты будут естественным продолжением моих усилий как программиста. Но построив несколько сложных решений для отчетов и работая с другими программистами над архитектурой крупных решений в течение десяти лет, я понял, что огромному большинству решений SSRS не требуется дополнительное программирование. Лучшее применение библиотеки пользовательского кода - расширить встроенные возможности архитектуры SSRS, а не заменить удачные функции. Подготовка пользовательских модулей - непростая задача, и обычно она выходит за рамки практического применения для подготовки корпоративных отчетов.

Во внутренних механизмах SSRS используется набор сборок. NET, представляющий собой модули для работы с большинством основных функций. SQL Server версий Standard, Business Intelligence и Enterprise Editions автоматически устанавливает некоторые из этих модулей. Например, при экспорте отчета в Microsoft Excel применяется модуль подготовки отчетов Excel. При планировании подписки модуль доставки электронной почты или файловой системы используется вместо стандартного модуля доставки интерактивных отчетов.

В SQL Server версий Standard, Business Intelligence и Enterprise Editions службы SSRS обеспечивают расширение следующими способами.

• Модули обработки данных. Эти модули предоставляют доступ к различным типам данных с использованием согласованной программной модели. Среди модулей для обработки данных - модули для SQL Server, SQL Server Analysis Services (SSAS), SQL Server Parallel Data Warehouse, SQL Azure, списков Microsoft SharePoint, моделей отчетов, ODBC, OLE DB, Hyperion Essbase, Oracle, SAP NetWeaver Business Intelligence, Teradata и XML. Кроме того, компания Microsoft предоставляет модули для обработки данных для SAP Relational DB и DB2.
• Модули доставки. Эти модули взаимодействуют с каким-либо механизмом доставки для отправки отчетов пользователям. Среди них модули для интерактивных отчетов, электронной почты, файловой системы и поставщика NULL; они позволяют воспроизводить отчеты без сохранения вывода.
• Модули подготовки отчетов. Эти модули управляют типом документа или носителя, создаваемого при выполнении отчета. Компания Microsoft предоставляет несколько модулей, охватывающих широко распространенные и полезные типы документов, в том числе HTML, веб-архив, PDF, Excel, Microsoft Word, изображения, XML и CSV (с разделением запятыми).
• Модули безопасности. Они обеспечивают проверку подлинности и авторизацию пользователей для запуска отчетов на сервере отчетов. Предоставляемые модули поддерживают встроенную в Windows защиту и проверку подлинности ASP.NET на основе форм. Можно реализовать и собственную модель безопасности на основе ролей с любым критерием проверки подлинности.
• Модули обработки отчетов. Эти модули обеспечивают создание пользовательских элементов отчетов, управляемых механизмом обработки отчетов. С их помощью можно расширить стандарт языка определения отчетов (RDL), дополнив его функциональностью, изначально отсутствующей в RDL, в частности пользовательскими картами MapPoint и горизонтальными списками. Также можно расширить имеющиеся элементы отчета, добавив альтернативные версии, более подходящие для конкретного применения.
• Модули настройки определения отчета предоставляют выход к предварительной обработке определения отчета. Можно подключить пользовательский код, который изменяет поток определения отчета перед его обработкой. Это удобно, если необходимо изменить структуру отчета в зависимости от культурных или местных особенностей, либо личности пользователя, указанного в запросе отчета.

При необходимости создать пользовательские модули можно учиться на опыте других разработчиков. Я работал с компаниями, в которых SSRS применяется как составная часть набора служб, и некоторые из них создали собственные модули. Одна компания построила специальный модуль безопасности, чтобы пользователи могли выполнять регистрацию и проходить проверку подлинности в ее программе, не прибегая к Windows. Другие компании со сложными уровнями данных и прикладного программирования строят специальные модули обработки данных, которые используются вместо стандартных поставщиков данных в SSRS. Например, одна компания построила модуль для обработки данных, применив его к сложным бизнес-правилам и логике фильтров, специфичным для отрасли. В каждом случае группам программистов потребовались месяцы, чтобы создать продукт, устранить ошибки, протестировать и внедрить специализированные модули в производство.

Настраиваемые сборки

Сборка - модуль повторно используемого программного кода, обычно подготовленный с помощью языка программирования. NET в среде Visual Studio и часто скомпилированный в DLL-файл, содержащий повторно используемые функции и другую программную логику. Специализированную сборку можно применить, чтобы расширить функциональность отчетов на многие отчеты на одном сервере. Например, если нужно обработать параметрические бизнес-правила, распространяющиеся за рамки возможностей языка запросов, такого как T-SQL или MDX, можно задействовать специализированную сборку для управления запросом к набору данных отчета и связанным поведением отчета. SQL - мощный язык, с помощью которого можно обработать достаточно громоздкую бизнес-логику. При возможности можно задействовать язык запросов - и особенно устойчивые объекты базы данных, такие как хранимые процедуры - для инкапсуляции сложных правил. Лучшие примеры наиболее эффективного применения этого метода гораздо сложнее, чем я привожу в данной статье. Однако показанные приемы представляют интерес и, в некоторых случаях, могут оказаться необходимыми.

Создание специализированной сборки

В следующем примере показано, как построить специализированную сборку, которая собирает инструкцию T-SQL на основе нескольких параметризованных аргументов. Сборка проектируется в среде Visual Studio 2010 в проекте библиотеки классов, который может быть частью того же решения, что и проект отчета. После тестирования сборка развертывается в доверенных папках, где ее можно использовать в SQL Server Data Tools (SSDT) или конструкторе отчетов Business Intelligence Development Studio (BIDS) на сервере отчетов. Проект выполнен в Visual Basic. NET, но с тем же успехом можно использовать C#. Рабочий экземпляр отчета и образцовый программный код проекта можно загрузить, нажав кнопку Download.

Создание представления и проекта библиотеки классов

Первая задача - создать представление vSalesByTimeAndTerritory в базе данных AdventureWorksDW2012. Убедившись, что вы имеете разрешение на создание объектов в этой базе данных, откройте окно запросов SQL Server Management Studio (SSMS) и выполните сценарий листинга 1, чтобы создать представление.

Затем необходимо создать проект библиотеки классов в среде Visual Studio, выполнив следующие действия.

1. Из меню File в Visual Studio выберите New и пункт Project.
2. В окне Add New Project ("Добавление нового проекта") выберите Visual Basic в области Installed Templates ("Установленные шаблоны").
3. На центральной панели выберите Class Library ("Библиотека классов") и назовите проект Report_Class_Lib.
4. В обозревателе решений измените имя класса файлов по умолчанию на ReportLib.cls.
5. Откройте ReportLib.cls в редакторе кода Visual Basic. Работая с этим редактором, рекомендуется включить функции завершения и отладки, чтобы упростить работу. Например, если представлен список объектов, можно выбрать нужный объект с помощью клавиш со стрелками, а затем добавить свой код нажатием клавиши Tab. Можно также нажать клавишу Enter, чтобы выбрать объект и добавить новую строку кода.
6. После объявления Public Class ReportLib введите исходный текст из листинга 2. Он создает функцию SalesQuery, которая строит запрос T-SQL на основе условной логики в программном коде. Обратите внимание, что символы продолжения строки необязательны и приведены только для того, чтобы исходный текст уместился на странице. При желании можно пропустить символы подчеркивания (_) и возвраты каретки в каждой строке, завершающейся символом подчеркивания.
7. Выполните проверку на наличие ошибок и предупреждений с красными или зелеными извилистыми линиями под ключевыми словами.
8. В обозревателе решений щелкните правой клавишей мыши проект Report_Class_Lib и выберите пункт Build ("Сборка").

Развертывание библиотеки классов

Прежде чем продолжить, отметим два важных обстоятельства. Необходимо найти папки для сборок, которым доверяет Visual Studio и сервер отчетов SSRS. В различных версиях продукта эти пути похожи, но зависят от параметров, выбранных при установке и настройке. После того, как пути найдены, скопируйте их и вставьте в программу Notepad.

В этом примере используется путь C:Program Files (x86)Microsoft Visual Studio 10.0Common7IDEPrivateAssemblies для папки сборок Visual Studio 2012. Для сервера отчетов SSRS применяется путь отчетов по умолчанию в SSRS 2012 - C:Program FilesMicrosoft SQL ServerMSRS11.MSSQLSERVERReporting ServicesReportServerbin. Обратите внимание, что если используется SSRS 2012 в SharePoint, то путь по умолчанию - C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14WebServicesReportingbin.

После того, как пути внесены в Notepad, выполните следующие действия.

1. Откройте свойства проекта. В разделе Compile ("Компилировать") выберите Build Events ("События построения").

2. В текстовом поле Post-build event command line ("Командная строка события после построения") введите следующие команды, указывая пути, скопированные в Notepad:

Команды показаны разбитыми на несколько строк, но в действительности их нужно вводить в одной строке. В режиме интеграции с SharePoint необходимо также скопировать сборку в общую папку для расширений веб-сервера. Для этого добавьте следующую строку:

3. Настройте событие после построения для запуска после успешного завершения сборки, как показано на экране 1.

Экран 1. Настройка события для запуска после успешного завершения сборки

4. Нажмите кнопку OK, чтобы сохранить изменения.

5. Постройте проект заново.

С помощью проводника Windows убедитесь, что файл Report_Class_Lib.dll был скопирован куда нужно и имеет недавнюю дату изменения.

Создание отчета

Теперь можно создать отчет. К нему предстоит добавить ссылку на сборку (экран 2), параметры и выражение. Выполните следующие действия.

Экран 2. Добавление ссылки на сборку в отчет

1. Создайте новый отчет с именем Generated Query.

2. Выберите Report Properties ("Свойства отчета") из меню Report ("Отчет").

3. В диалоговом окне Report Properties выберите страницу References ("Ссылки").

4. В разделе Add or remove assemblies ("Добавить или удалить сборки") нажмите кнопку Add ("Добавить").

5. Нажмите кнопку с многоточием (...) справа от новой строки.

6. В диалоговом окне Add Reference ("Добавление ссылки") перейдите на вкладку Browse ("Обзор") и найдите файл Report_ClassLib.dll в папке доверенных сборок.

7. Выделите файл Report_ClassLib.dll и нажмите кнопку OK, чтобы добавить reference.

8. В разделе Add or remove classes ("Добавить или удалить классы") страницы References нажмите кнопку Add.

9. В поле под столбцом Class Name ("Имя класса") введите Report_Class_Lib.ReportLib.

10. В поле под столбцом Instance Name ("Имя экземпляра") введите m_ReportLib. Это псевдоним имени класса, который будет использоваться в отчете.

11. В окне Report Data ("Данные отчета") в левой стороне конструктора отчетов щелкните правой кнопкой мыши узел Parameters, чтобы добавить новый параметр.

13. Воспользуйтесь окном Report Parameters ("Параметры отчета"), чтобы добавить каждый параметр. Используйте информацию из таблицы, чтобы задать имя, приглашение, тип и значение каждого параметра по умолчанию.

14. Для параметра SalesQuery установите видимость Hidden ("Скрытый") на странице General ("Общие") в диалоговом окне Report Parameters ("Параметры отчета"), а затем перейдите на страницу Default Value ("Значение по умолчанию").

15. Выберите Specific values ("Определенные значения"), чтобы добавить значение по умолчанию.

16. Нажмите кнопку Expression ("Выражение") (fx) справа от раскрывающегося списка.

17. Введите следующий текст, а затем поместите курсор между скобками:

18. Выберите Parameters ("Параметры") из списка Category ("Категория") и дважды щелкните параметр YearFrom, чтобы добавить reference в выражение. Введите после этого текста запятую с последующим пробелом.

19. Используйте ту же процедуру для ввода дополнительных параметров, необходимых для построения следующего выражения (без возвратов каретки):

20. Нажмите кнопку OK, чтобы сохранить выражение.

21. Добавьте текстовое поле к отчету.

22. Щелкните правой кнопкой мыши текстовое поле и выберите пункт Expression ("Выражение").

23. Используйте Expression Builder ("Построитель выражений"), чтобы присвоить выражению текстового поля следующее значение:

24. Просмотрите сформированный запрос. Он будет показан в текстовом поле, как на экране 3.

Экран 3. Предпросмотр сгенерированного отчета

25. Убедитесь в правильности синтаксиса сформированного запроса. Он должен быть следующим:

• ВЕРХНЕЕ значение должно быть задано параметром MaxRows;
• столбец Freight должен войти в список столбцов, так как параметру IncludeFreight присвоено значение True;
• значения YearFrom и YearTo в предложении WHERE должны соответствовать значениям параметров YearFrom и YearTo, соответственно;
• предложение WHERE не должно содержать столбца SalesTerritoryCountry, так как параметру Country присвоено значение All.

26. На вкладке предварительного просмотра измените значения параметров и повторно запустите отчет, чтобы увидеть последствия сформированного запроса.

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

Использование сформированного запроса для создания набора данных отчета

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

1. Разверните отчет на сервере отчетов и запустите отчет.
2. Пока отчет выполняется в браузере, выделите все содержимое текста параметра SalesQuery, показанное в верхней части отчета, и скопируйте сценарий SQL в буфер обмена.
3. В конструкторе отчетов перейдите в представление конструирования.
4. Добавьте источник данных, чтобы подключиться к базе данных AdventureWorksDW2012. Можно использовать встроенный или общий источник данных.
5. Добавьте новый встроенный набор данных к отчету и назначьте набору данных имя ReportData.
6. В диалоговом окне Properties набора данных вставьте скопированный сценарий SQL в поле Query ("Запрос").
7. Нажмите кнопку OK. Обратите внимание, что конструктор отчетов выполняет запрос, чтобы сформировать список полей набора данных.
8. Разверните список полей набора данных на панели Report Data.
9. Добавьте таблицу в текст отчета.
10. Переместите с помощью мыши поля CalendarYear и MonthNumberOfYear на панель Row Groups ("Группы строк") в нижней части конструктора.
11. Измените ячейку заголовка строки MonthNumberOfYear в таблице, чтобы показать поле MonthName.
12. Переместите с помощью мыши поле SalesTerritoryCountry в первую открытую ячейку сведений в таблице.
13. Отредактируйте группу Details на панели Row Groups. Добавьте группу с использованием поля SalesTerritoryCountry.
14. Переместите с помощью мыши поля SalesAmt и Freight в ячейки сведений таблицы.
15. В целях отладки измените значение параметра SalesQuery на Visible, но обязательно скройте его после завершения отладки. В противном случае отчет не будет выполняться корректно.

Функционально отчет завершен, но в нем есть ряд уязвимых мест. Разработчики служб SSRS предусмотрели несколько мер безопасности, но не играйте с огнем. В этот пример не нужно вносить изменений, а при внедрении в производство рекомендуется изучить материал раздела "Безопасность - в первую очередь", чтобы исключить возможность атаки путем внедрения кода SQL.

Динамическое скрытие столбца Freight

Возможность выборочно показывать или скрывать различные столбцы - первый шаг к тому, чтобы возложить на один отчет функции многих отчетов. Чтобы показать, как это делается, динамически скроем столбец Freight. Выполните следующие действия в конструкторе отчетов.

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

2. Щелкните правой кнопкой мыши серый заголовок для столбца Freight и выберите пункт Column Visibility.

3. В диалоговом окне Column Visibility выберите переключатель с меткой Show or hide based on an expression ("Показать или скрыть в зависимости от выражения").

4. Откройте диалоговое окно Expression Builder ("Построитель выражений") и введите выражение

5. Нажмите кнопки OK, чтобы сохранить и закрыть диалоговые окна Expression Builder и Column Visibility ("Видимость столбца").

Изменение набора данных для использования сформированного кода

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

1. На панели Report Data дважды щелкните ReportDatadataset.

2. В диалоговом окне Dataset Properties ("Свойства набора данных") нажмите кнопку Expression ("Выражение") (fx) рядом с полем Query ("Запрос").

3. В диалоговом окне Expression Builder замените текст команды ссылкой на параметр SalesQuery:

4. Нажмите кнопку OK, чтобы сохранить и закрыть диалоговые окна Expression Builder и Dataset Properties.

5. Убедитесь, что параметр SalesQuery имеет значение Hidden.

6. Измените свойство Hidden, чтобы скрыть текстовое поле, в котором показано значение параметра SalesQuery.

7. Просмотрите отчет и поэкспериментируйте с различными значениями параметра. Образец отчета показан на экране 4.

Экран 4. Предпросмотр отчета

Отладка запроса

Такое решение, как правило, требуется отладить. Полезно составить короткий список пунктов отладки и выполнять их каждый раз, когда необходимо изменить проект или программный код.

Если вас не устраивают полученные результаты, следует сначала нажать кнопку Refresh ("Обновить") на инструментальной панели отчетов, чтобы очистить кэш выполнения и перезапустить запрос. SSRS устроен так, что прошлые результаты сохраняются в кэше.

Если правильных результатов получить все же не удается или параметры работают некорректно, то, скорее всего, набор данных не использует сформированный код. Для отладки отмените скрытие параметра SalesQuery и покажите текстовое поле. Выполняя отчет, проверьте значения, чтобы убедиться в правильности синтаксиса запроса. Если он неверный, значит, ошибка в программном коде. Если параметр правильно сформировал синтаксис запроса в текстовом поле, но не изменил вывод отчета, то остается только проверить выражение, использованное для сопоставления набора данных параметру.

Безопасность - в первую очередь

Всегда, когда запрос формируется из данных пользовательского ввода, необходимо обратить внимание на безопасность. Внедрение кода SQL - метод, используемый хакерами для несанкционированного доступа к данным и компонентам сервера базы данных. Службы SSRS менее уязвимы для атак путем внедрения кода SQL благодаря ряду встроенных мер безопасности, однако важно проявлять осторожность при использовании параметров, открытых для таких запросов.

Необходимо убедиться, что учетная запись пользователя, применяемая источником данных, имеет доступ только для чтения и только к данным, необходимым для отчета. Также нужно быть аккуратным с параметрами с текстовым типом данных. Числовые, логические данные и даты обычно не представляют угрозы, так как вместо их значений нельзя подставить сценарий. При возможности скрывайте параметры с текстовыми данными. Для параметров, которые должны быть видимыми и требуют текстовых значений, используйте специализированную функцию, чтобы проверять текстовые значения. Разрешайте только определенные значения и отсекайте строки, ограничивая их максимальную длину.

Используйте политики исключений безопасности

Любой пользовательский код сборки, который управляет строками, форматированием или подготовкой динамических запросов, как в данном примере, будет прекрасно выполняться и не потребует специальной настройки. Если этим ограничивается назначение сборки, то можно начинать действовать.

Если же сборка содержит ссылки на пространства имен, требующие дополнительных разрешений, для доступа к внешним ресурсам, таким как файловая система, то по умолчанию сборка не будет доверенной. Таким образом, необходимо настроить явные доверительные отношения между сервером отчетов и сборкой. Сделать это несложно, но необходимо строго следовать инструкциям.

Реализация политики исключений безопасности - задача управления, которую требуется тщательно продумать перед внедрением в производство. Прежде чем просить администратора корпоративных серверов о реализации политик исключений безопасности для компонента, следует четко разъяснить, для каких целей может использоваться компонент и почему исключение - обязательная часть решения. В статьях MSDN "Code Access Security in Reporting Services" и "Using Reporting Services Security Policy Files" подробно объясняются критерии и показано, как создать необходимые группы кода и именованные наборы разрешений.

Полезный метод

Настраиваемые сборки можно использовать в отчетах для различных целей. Например, централизованно управляемый компонент поможет единообразно применять стили ко всем отчетам. Изменив одно значение базы данных или выбирая параметр, можно изменить логотип компании или применять различные стили, рамки и шрифты к каждому отчету.

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