Малозаметный инструмент создания отчетов, полезный для всех
С момента выпуска служб отчетов 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, нужно найти запись Status. Обратите внимание на предупреждение об отсутствии у таблицы первичного ключа (экран 3). Это важно: если у какой-то таблицы нет первичного ключа, следует установить первичный ключ для всех нижележащих таблиц, так чтобы каждая строка данных однозначно определялась, когда запускается отчет в Report Builder. Можно установить логический первичный ключ, используя Data Source View Designer; просто выделите один или несколько столбцов и воспользуйтесь пунктом меню Data Source View-Set Logical Primary.
Как уже говорилось выше, на экране 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.
Выбираем источник данных и способ отображения отчета. Перед генерацией отчета необходимо выбрать источник данных и способ отображения отчета (таблица, матрица, диаграмма), поскольку Report Builder не поддерживает отчетов в свободной форме. Для первого образца отчета с распределением продаж по времени мы выбрали источник данных Example1 и способ отображения в виде матрицы. Затем нажимаем ОК. Список объектов модели отображается в окошке со списком с названием Entities в панели Explorer, показанной на экране 4. (Чтобы увидеть панель Explorer, нужно выбрать View, Explorer в инструментарии Report Builder.)
Выбираем в окошке со списком Entities объект Sales Territory (территория продаж). Обратите внимание, что окошко со списком Fields отображает поля, характерные именно для Sales Territory.
Далее следует выделить поле Group и перетащить его в секцию группировки строк в окно проектирования внешнего вида отчета, как показано на экране 4. Другой способ достижения того же результата - двойной щелчок на поле Group. Нужно иметь в виду, что после того, как вы поместите поле Group в окно проектирования внешнего вида отчета, Report Builder отфильтрует объекты в окошке Entities и отобразит только те из них, которые имеют отношение к Sales Territory. Такая фильтрация предназначена для того, чтобы упростить пользователю навигацию по модели отчетов и уберечь его от объединения объектов, не имеющих никакого отношения друг к другу. Теперь следует поместить поле Name справа от поля Group в секции группировки строк в окне проектирования внешнего вида отчета.
Далее в окошке со списком 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.
Возникает вопрос, что бы мы могли сделать в нашей модели, чтобы этот отчет стало писать еще проще? Например, начинающим такая мера, как включение поля Employee’s Name (Имя сотрудника) в объект Sales Person, помогла бы сэкономить время. Также неплохо было бы включить поле Full Name, чтобы было проще отображать полное имя сотрудника, не беспокоясь при этом о лишних полях промежуточных сумм. И наконец, хотя Report Builder позволяет конечным пользователям форматировать числовые значения, было бы лучше, если бы эти значения форматировал Report Builder.
Наверняка многие могут предложить и другие усовершенствования, но для начала хватит и этого. Чтобы продолжить изучение правильной организации и настройки объектов, использования атрибутов и изменения форматов.
Report Builder - это долгожданное дополнение к службам Reporting Services. И хотя, по-моему, маловероятно, что специалисты по информационным технологиям когда-либо перестанут заниматься созданием отчетов, Report Builder оказывает поддержку тем пользователям, которые имеют доступ к данным на бизнес-уровне, но не чувствуют себя достаточно уверенно с Visual Studio 2005, чтобы писать отчеты.
Способы создания моделей отчетов
Обычно для построения модели отчета используется 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 имеет много объектов. Можно упорядочить эти объекты, распределив их по папкам и упростив таким образом их поиск.
Чтобы создать новую папку, следует выделить корневой узел Model, нажать правую кнопку в области проектирования модели (см. экран А) и выбрать New, Folder. Озаглавим папку Product Details. Чтобы добавить объекты в папку Product Details, нужно снова выделить корневой узел Model и прокрутить окно до самого низа, пока не появится папка Product Details. В списке подробностей справа от дерева следует перетащить все объекты, которые начинаются со слова Product (кроме объекта Product), в папку Product Details. Продолжайте этот процесс, чтобы создать дополнительные папки, упорядочивающие оставшиеся объекты.
Настройка объекта 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.
Выберите на панели инструментов Report Builder пункты File, New. Затем выберите источник данных Example 2 и табличный внешний вид отчета. Далее следует найти и перетащить объект Sales Person в окно проектирования внешнего вида. Обратите внимание, что поля Full Name и Commission Pct - указанные нами атрибуты детализации по умолчанию - отображаются автоматически. Перетащите поля Total Sales Quota и Total Sales YTD (из панели Fields на экране В) так, чтобы они оказались справа от Commission Pct. Объяснение способа добавления ячейки со списком, чтобы фильтровать отчет по Sales Territory, приводится в разделе «Создаем отчет в виде таблицы» главной статьи. Надо сказать, что эти изменения могут кое-чему научить нас в смысле создания отчетов.