С момента выпуска служб отчетов SQL Server 2000 Reporting Services в январе 2004 г. я имел возможность провести более 100 демонстраций этого продукта. Одна из часто повторявшихся просьб, которые мне приходилось слышать, состояла в том, что хорошо бы в Reporting Services иметь средство отчетов для конечных пользователей. Специалисты Microsoft прислушались к этому пожеланию и создали в SQL Server 2005 Reporting Services новый, специально приспособленный для данной ситуации инструмент Report Builder. Сначала вы используете Business Intelligence Development Studio из пакета Visual Studio 2005 — это набор типов проектов в Visual Studio 2005, — чтобы создать и настроить модель отчетов (лежащий в основании уровень метаданных, который позволяет конечным пользователям создавать отчеты). Как только модель установлена, пользователи могут задействовать Report Builder, чтобы составлять собственные отчеты. Report Builder спроектирован таким образом, чтобы пользователи, не имеющие большого опыта работы с базами данных, могли создавать отчеты, не вдаваясь в подробности написания запросов для извлечения данных.
Я разделил процесс создания отчета на три фазы: создание модели отчетов в Visual Studio 2005 (что подразумевает настройку источника данных, настройку визуального представления источника данных и настройку модели отчетов), внедрение модели отчетов на сервере отчетов и собственно написание отчета. Нам понадобятся следующие инструменты: Visual Studio 2005, SQL Server 2005 Reporting Services и .NET Framework 2.0.
Фаза 1. Создаем модель отчетов в Visual Studio 2005
Как и в случае с другими компонентами SQL Server 2005, сначала используется Visual Studio 2005, чтобы создать новый проект для модели отчетов. Выберите в качестве типа проекта Business Intelligence Projects и используйте Report Model Project в качестве шаблона.
Создаем источник данных. Приступив к созданию нового проекта, на следующем шаге необходимо определить в Report Model Project один или несколько источников данных. Report Builder на сегодня поддерживает в качестве источника данных SQL Server версии 7.0 и выше. Analysis Services 2005 также может служить источником данных (хотя вы не увидите этого варианта в Report Model Project — дополнительные сведения можно найти во врезке «Способы создания моделей отчетов»). Чтобы создать источник данных, следует запустить мастер Data Source Wizard, щелкнув правой кнопкой на папке Data Source в окне Solution Explorer. В нашем примере используется образец реляционной базы данных AdventureWorks, которая поставляется вместе с SQL Server 2005.
Создаем представление источника данных. Как видно из названия, представление источника данных (Data Source View, DSV) — это способ выбрать подмножество таблиц и представлений из источника данных. Чтобы создать представление источника данных, нужно запустить мастер DSV, щелкнув правой кнопкой на папке Data Source Views в окне Solution Explorer и выбрав в контекстном меню пункт Add New Data Source View. Мастер позволяет отобрать нужные таблицы и представления из определенной базы данных. После завершения работы мастера можно будет добавлять вычисляемые столбцы к существующей таблице или представлению, а также создавать новые представления (которые называются Named Queries — именованные запросы, чтобы не путать с актуальными представлениями, уже существующими в базе данных). В данном случае я выбираю все таблицы и не делаю дополнительных настроек.
Объекты и атрибуты. Следует различать объекты и атрибуты. Таблицы, представления и именованные запросы являются объектами в модели отчетов (и в клиентской части Report Builder). Например, выбор варианта в двух первых правилах определяет то, как мастер будет создавать объект на основе представления Data Source View. Столбцы таблиц являются атрибутами.
Вычисления. По умолчанию мастер Report Model Wizard будет автоматически создавать сумму, минимум, среднее значение и максимум для числовых атрибутов. Кроме того, мастер может создать поля для вычислений с датами и счетчики числа уникальных экземпляров чего-либо в рамках одного объекта. Эти вычисления дают дополнительную информацию, полезную с аналитической точки зрения и с точки зрения ведения отчетов (например, число заказов, которое было в прошлом месяце).
Роли. Роль в модели отчетов касается отношений между объектами, и не следует путать понятие роли в модели отчетов с понятием роли в системе безопасности. Когда конечный пользователь создает отчет, Report Builder использует эти роли, чтобы автоматически фильтровать объекты, что упрощает пользователю навигацию и выбор нужных элементов в модели отчетов.
Далее, после того как были выбраны правила, мастер Report Model Wizard предлагает собрать статистику модели (Model Statistics). Мастер использует Model Statistics, чтобы генерировать и устанавливать в модели отчетов свойства по умолчанию. Процесс генерации модели данных хранит эти статистические вычисления в представлении источника данных. Например, в процессе создания модели отчетов Report Model Designer считает число уникальных экземпляров каждого объекта и использует эти данные, чтобы определить, когда при вводе параметра должен отображаться список доступных значений, а когда сам пользователь должен подыскивать значение.
Наконец, на странице завершения работы мастера (Completing the Wizard) нужно ввести имя модели отчетов и нажать Run, чтобы завершить работу. После этого мастер создает модель, что займет несколько минут (в зависимости от размера модели отчетов). Прежде чем отобразить результаты создания модели отчетов, мастер делает два прохода к базе данных — источнику. На первом проходе он обрабатывает правила, ответственные за создание объектов, атрибутов, разновидностей данных, полей вычислений и ролей. Второй проход восходит к дополнительной и более сложной обработке правил.
Как уже говорилось выше, на экране 2 показано окно готовых моделей отчетов. Объекты расположены в левой панели, а атрибуты и роли — в правой. Недоступное непосредственному восприятию внутреннее устройство моделей отчетов использует XML-язык, который называется Semantic Model Definition Language (SMDL), поэтому файлы моделей отчетов имеют расширение .smdl.
Фаза 2. Внедряем модель отчетов на сервер отчетов
После того как мы выбрали источник данных, создали представление источника данных и модель отчетов, самое время настроить некоторые параметры внедрения и загрузить файлы источника данных и модели отчетов на сервер отчетов. Чтобы установить параметры внедрения, нужно щелкнуть в Solution Explorer правой кнопкой на элементе Project и ввести URL целевого сервера в адресную строку сервера отчетов (например, http://localhost/ReportServer). Если вы раньше использовали SQL Server 2000 Reporting Services, то увидите новый параметр внедрения — папку TargetDataSourceFolder, которая упрощает централизованную работу и повторное использование источников данных в нескольких проектах.
Фаза 3. Пишем отчет
Теперь администратор имеет возможность примерить на себя роль конечного пользователя. Для этого он должен запустить в своем браузере менеджер отчетов Report Manager путем ввода адреса компьютера, на котором работает Reporting Services (например, http://localhost/Reports). Затем, нажимая новую кнопку Report Builder в менеджере отчетов, следует запустить клиентскую часть Report Builder. Составитель отчетов Report Builder опирается на новую модель внедрения Visual Studio 2005 ClickOnce, что означает его автоматическую установку на компьютер конечного пользователя. Следует иметь в виду, что для установки Report Builder на клиентском компьютере нужна библиотека.NET Framework 2.0.
Выбираем в окошке со списком Entities объект Sales Territory (территория продаж). Обратите внимание, что окошко со списком Fields отображает поля, характерные именно для Sales Territory.
Далее в окошке со списком Entities под объектом SalesTerritory нужно выделить роль Sales Order Header, как показано на экране 5. В окошке со списком Fields следует раскрыть поле Ship Date и перетащить атрибут Ship Year в секцию группировки столбцов, которая расположена на правой стороне окна проектирования внешнего вида отчета. Наконец, мы должны добавить численные данные в секцию вычисления сумм в окно проектирования внешнего вида отчета. Необходимо в окошке Fields выбрать поле Total Sub Total и перетащить его в секцию вычисления сумм, как показано на экране 5. Численные данные можно форматировать путем выделения всех шести числовых полей одновременно, нажатия правой кнопки мыши и выбора Format. Введите заголовок отчета. Примерный внешний вид отчета показан на экране 5.
Сохраняем отчет. Чтобы сохранить отчет на сервере отчетов, выберем в меню Report Builder пункт File, затем Save. После того как отчет сохранен, его можно просматривать, настраивать и защищать точно так же, как если бы мы создавали этот отчет, используя Visual Studio 2005 Report Designer.
Создаем отчет в виде таблицы
Теперь воспользуемся отображением в виде таблицы, чтобы создать второй отчет, параметры которого на этот раз будут возвращать список лиц Sales Persons по полю Territory для заданной группы Territory Group. Мы выбрали этот отчет для того, чтобы обратить внимание читателей на несколько важных аспектов первоначальной модели отчетов Report Model. Выберите на панели инструментов Report Builder пункты File, New. Укажите источник данных Example1 и табличный внешний вид отчета. Найдите и выделите объект Sales Person в окошке Entities, затем перетащите поле # Commission Pct в секцию столбцов в окно проектирования внешнего вида отчета. (Если вы не видите этого поля, раскройте группу Total Commission Pct.) Как и в предыдущем случае, Report Builder отфильтровывает список в окошке Entities, чтобы отображать только те объекты, которые имеют отношение к Sales Person. Перетащим поля Total Sales Quota и Total Sales YTD в окно проектирования внешнего вида отчета правее Commission Pct.
Далее в окошке Entities необходимо выделить наименование Sales Person в списке объекта Sales Person — эта роль указывает на объект Employee, но мастер Report Model Wizard именует данную роль Sales Person. Под наименованием Sales Person раскроется другой список ролей. Выделим роль Contact и перетащим поля объектов Last Name и First Name (Имя и Фамилия) в окно проектирования внешнего вида отчета слева от поля Total Commission Pct. В окне проектирования следует щелкнуть правой кнопкой на каждой вкладке столбцов серого цвета и отменить выбор параметра Show Group Sub Totals («Показывать промежуточные суммы по группам»), чтобы с полями First Name и Commission Pct не связывалось поле вычислений промежуточной суммы. Напоследок добавим одну ячейку со списком, чтобы можно было фильтровать отчет по территориальному признаку (Sales Territory). Нажмите пиктограмму Filter на панели инструментов Report Builder (или выберите в меню Report Builder пункты Report, Filter). Появится окно Filter Data. В окошке Entities нужно выделить роль Territory (в списке объектов Sales Person) и перетащить поле Name на панель Filter. Выделите в ячейке со списком пункт Northwest, щелкните на панели фильтра правой кнопкой Territory и выделите пункт меню Prompt. Рядом со словом Territory появится зеленый знак вопроса, который показывает, что допускается настройка этого фильтра при просмотре отчета. Теперь отчет должен выглядеть примерно так, как показано на экране 6.
Наверняка многие могут предложить и другие усовершенствования, но для начала хватит и этого. Чтобы продолжить изучение правильной организации и настройки объектов, использования атрибутов и изменения форматов.
Report Builder — это долгожданное дополнение к службам Reporting Services. И хотя, по-моему, маловероятно, что специалисты по информационным технологиям когда-либо перестанут заниматься созданием отчетов, Report Builder оказывает поддержку тем пользователям, которые имеют доступ к данным на бизнес-уровне, но не чувствуют себя достаточно уверенно с Visual Studio 2005, чтобы писать отчеты.
Джон Пол Кук — архитектор систем и баз данных из Хьюстона, Техас. Специализируется на помощи крупным компаниям и пользователям в работе с SQL Server, Oracle и .NET Framework. Имеет несколько сертификатов Microsoft и Oracle. johnpaulcook@email.com
Тайлер Чесман — специалист по технологиям в South Central District компании Microsoft, отвечает за адаптацию у клиентов платформ Database и Business Intelligence. До прихода в Microsoft несколько лет работал консультантом по вопросам развертывания финансовых, аналитических и торговых приложений. tylerc@microsoft.com
Способы создания моделей отчетов
Обычно для построения модели отчета используется Report Model Project в Visual Studio 2005. Но модель отчетов можно сгенерировать и непосредственно из источника данных, который уже внедрен на сервере отчетов. Например, если вы используете SQL Server Management Studio (SSMS), то можете подключиться к экземпляру Reporting Services 2005, раскрыть папку Data Sources, нажатием правой кнопки выбрать источник данных и отметить в меню пункт Generate Model. Аналогично в Report Manager на вкладке Properties любого источника данных можно увидеть кнопку Generate Model, которая выполняет ту же функцию. Небезынтересно отметить, что использование источника данных для создания модели отчетов — единственный способ создать модель в случае базы данных Analysis Services 2005. Смысл этого становится ясен, если учесть, что куб сам по себе уже является моделью данных. Но оставим кубы в стороне — ведь, скорее всего, администраторы предпочтут потратить время на использование проекта Report Model Project, чтобы создавать и настраивать модели для большинства отчетов реляционных баз данных.
Тонкая настройка модели отчетов
Report Builder — это отличный инструмент для написания простых отчетов. Но когда мы создавали второй отчет в разделе «Создаем отчет в виде таблицы», у вас наверняка возникла мысль, что, хотя и нет необходимости в глубоком понимании терминологии баз данных, для того чтобы создать отчет, должен существовать более простой способ извлечь данные в подходящем формате. Давайте вернемся к той модели отчетов и внесем несколько изменений. Начнем с открытия проекта Report Model Project в Visual Studio 2005.
Распределяем объекты по папкам. В окне готовых моделей отчетов можно увидеть, что корневой узел Model имеет много объектов. Можно упорядочить эти объекты, распределив их по папкам и упростив таким образом их поиск.
Настройка объекта Sales Person. Напомним: для создания источника данных мы использовали образец реляционной базы данных AdventureWorks. В базе данных AdventureWorks все люди, имеющие отношение к продажам, — сотрудники. И было бы хорошо, помимо прочего, иметь возможность видеть свойства Employee, когда мы смотрим на объект Sales Person. Также многие детали, относящиеся к какой-либо записи Employee (имя человека), расположены в объектах Contact. Чтобы сделать свойство Full Name частью объекта Sales Person, требуется выполнить две дополнительные настройки.
В окне готовых моделей отчетов нужно щелкнуть на объекте Employee. Нажмите правой кнопкой в области проектирования модели и выберите New, Expression. Установите значение выражения в (First Name & « «) & Last Name (см. экран В), установите свойство Nullable в значение True и переименуйте выражение в Full Name.
В окне готовых моделей отчетов следует щелкнуть на объекте Sales Person. Найдите на панели Properties свойство Inheritance (наследование свойств). Затем выделите атрибут InheritsFrom (наследует от) и выберите в ячейке со списком вариант Employee. Далее необходимо выделить атрибут Binding (под свойством InheritsFrom) и выбрать в ячейке со списком вариант FK_SalesPerson_Employee_SalesPersonID — это отношение, которое мы будем использовать, чтобы объединить объекты Sales Person и Employee.
Переформатируйте атрибут Commission Percentage. В окне готовых моделей отчетов нужно нажать объект Sales Person, выделить атрибут # Commission Pct и изменить свойство Format на P (т. е. проценты), как показано на экране А. Установите значение свойства DefaultAggregateAttribute в None, поскольку комиссионные не являются аддитивными (мы хотим, чтобы значением по умолчанию была величина комиссии, а не какой-то результат суммирования). При желании можно продолжить и изменить свойство Format атрибутов Sales (таких, как Sales YTD Sales Quota), установив их в значение C (валюта), чтобы отображать эти значения как валюту. Также можно изменить у этих атрибутов свойство Width, чтобы автоматически подстраиваться под большие суммы.
Установите для объектов атрибуты детализации по умолчанию. Если присмотреться к какому-либо объекту в окне готовых моделей отчетов, то можно увидеть на панели Properties несколько свойств, относящихся к атрибутам (например, DefaultDetailAttributes, IdentifyingAttributes, SortAttributes). Эти свойства используются, когда система генерирует отчеты с бесконечной детализацией и когда пользователь перетаскивает объект (а не поля объекта) в окно проектирования внешнего вида отчета в клиентской части Report Builder. Чтобы изменить атрибут Details, связанный с объектом Sales Person, нужно выделить объект Sales Person, выделить свойство DefaultDetailAttributes и нажать кнопку, которая появляется справа от свойства. Необходимо заменить элемент National ID Number на Full Name и переместить Full Name в верхнюю часть списка (поверх Commission Pct).
Пишем отчет. Можно было бы сделать и больше, но давайте посмотрим на внесенные только что изменения. Чтобы увидеть изменения, нужно повторить внедрение модели отчетов (см. раздел «Фаза 2. Внедряем модель отчетов на сервер отчетов» в главной статье) и запустить Report Builder.
