Как сделать отчет в sql
Обновлено: 05.07.2024
Сводная таблица – один из самых популярных методов анализа табличных данных. Иногда мы анализируем большие таблицы — более 500 тыс. строк, но Excel обрабатывает такое количество данных достаточно долго, система постоянно зависает. Сегодня мы рассмотрим наиболее известные варианты построения сводной таблицы, доступные в SQL Server.
Предположим, у нас есть таблица с данными продаж нескольких видов продуктов (Product 1, 2, 3, 4) у разных операторов (A, B, C, D):
Из вышеуказанной таблицы мы хотим получить сводную таблицу вида:
Вариант 1: Использование оператора CASE
Не хватает итогов под таблицей. Для этого мы будем использовать оператор GROUP BY rollup:
Вариант 2: Использование оператора GROUP BY CUBE
Для быстрой группировки данных по операторам и вывода итоговых значений по каждому продукту без перечисления в коде всех операторов удобно использовать оператор GROUP BY CUBE:
Вариант 3: Использование оператора разворота таблиц PIVOT
Перед использованием этого оператора нам необходимо получить агрегированную таблицу. Для этого мы будем использовать ранее подготовленную с использованием оператора GROUP BY CUBE таблицу, используя предыдущий фрагмент кода как подзапрос (выделено цветом).
Для разворота таблицы в разрезе продуктов по каждому оператору меняем аргумент в операторе PIVOT.
Вариант 4: Динамический SQL
Для генерации строки, мы можем использовать оператор STUFF:
declare @colnames as nvarchar(max); select @colnames = stuff((select distinct ', ' + '"' + operator + '"' from [TB13_SANDBOX].[dbo].[Dr_operator] for xml path ('') ), 1, 1, '' ) + ', "total_sum"';
Delphi site: daily Delphi-news, documentation, articles, review, interview, computer humor.
Вы используете Business Intelligence Development Studio для создания отчета. Инструмент BI Development Studio, кратко рассмотренный в главе 23, является интегрированной средой разработки, созданной на Visual Studio и спроектированной для разработки систем бизнес-аналитики. Для запуска BI Development Studio щелкните по кнопке Start, выберите All Programs | Microsoft SQL Server 2008 | SQL Server Business Intelligence Development Studio.
Первым шагом в построении отчета является создание нового проекта, которому будет принадлежать отчет. Для создания проекта в меню File выберите команды New | Project. В диалоговом окне New Project в панели Project types выберите папку Business Intelligence Projects. Наберите имя проекта и его размещение в текстовых полях Name и Location соответственно. Проект в этом примере называется Reportl (рис. 25.2).
Рис. 25.2. Диалоговое окно New Project
В панели Templates диалогового окна New Project вы увидите три шаблона проектов, связанных с отчетами. Шаблон Report Server Project создает пустой отчет и оставляет вас в одиночестве, давая возможность сделать оставшуюся часть работы. Мастер Report Server Project Wizard проведет вас через фазы создания нового отчета. Шаблон Report Model Project создает модель данных для использования в Report Builder.
Обычно отчеты Reporting Services создаются либо с помощью Report Builder, либо посредством Report Designer. Report Builder позволяет создавать специальные отчеты, без знания чего бы-то ни было относительно структуры базы данных и создания запросов с использованием языков запросов, таких как SQL. Компонент Report Designer позволяет вам создавать отчет из рабочей области. Он предоставляет гораздо больше возможностей для создания отчетов, чем Report Builder.
В этом разделе вы будете использовать шаблон мастера Report Server Project Wizard, чтобы увидеть, как нужно создавать отчет. Поэтому выберите пиктограмму Report Server Project Wizard и щелкните по кнопке ОК.
Выберите файл с расширением .rdl на диске:
Теперь нужно изменить ссылку на источник данных. Для использования общего источника данных он должен быть заранее создан (создаётся один раз и далее просто используется в разных отчётах). Для этого проделайте действия как на картинке ниже:
С момента выпуска служб отчетов 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 спроектирован таким образом, чтобы пользователи, не имеющие большого опыта работы с базами данных, могли создавать отчеты, не вдаваясь в подробности написания запросов для извлечения данных.
Фаза 1. Создаем модель отчетов в Visual Studio 2005
Как и в случае с другими компонентами SQL Server 2005, сначала используется Visual Studio 2005, чтобы создать новый проект для модели отчетов. Выберите в качестве типа проекта Business Intelligence Projects и используйте Report Model Project в качестве шаблона.
Создаем представление источника данных. Как видно из названия, представление источника данных (Data Source View, DSV) — это способ выбрать подмножество таблиц и представлений из источника данных. Чтобы создать представление источника данных, нужно запустить мастер DSV, щелкнув правой кнопкой на папке Data Source Views в окне Solution Explorer и выбрав в контекстном меню пункт Add New Data Source View. Мастер позволяет отобрать нужные таблицы и представления из определенной базы данных. После завершения работы мастера можно будет добавлять вычисляемые столбцы к существующей таблице или представлению, а также создавать новые представления (которые называются Named Queries — именованные запросы, чтобы не путать с актуальными представлениями, уже существующими в базе данных). В данном случае я выбираю все таблицы и не делаю дополнительных настроек.
Модель отчетов — это описание источника данных и отношений между его объектами на уровне метаданных. Чтобы создать модель отчетов, необходимо запустить мастер Report Model Wizard, щелкая правой кнопкой на папке Report Models в окне Solution Explorer и выбирая в контекстном меню пункт Add New Report Model. При появлении страницы Select Data Source View нужно выбрать подходящее представление источника данных. Когда появится форма Select report model generation rules (правила генерации модели отчетов), следует оставить настройки по умолчанию, как показано на экране 1. Хотя мастер предоставляет описание каждого правила, необходимо принять во внимание несколько важных, на мой взгляд, замечаний.
Объекты и атрибуты. Следует различать объекты и атрибуты. Таблицы, представления и именованные запросы являются объектами в модели отчетов (и в клиентской части 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. Внедряем модель отчетов на сервер отчетов
Фаза 3. Пишем отчет
Выбираем источник данных и способ отображения отчета. Перед генерацией отчета необходимо выбрать источник данных и способ отображения отчета (таблица, матрица, диаграмма), поскольку 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.
Создаем отчет в виде таблицы
Возникает вопрос, что бы мы могли сделать в нашей модели, чтобы этот отчет стало писать еще проще? Например, начинающим такая мера, как включение поля 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, чтобы создавать и настраивать модели для большинства отчетов реляционных баз данных.
Тонкая настройка модели отчетов
Распределяем объекты по папкам. В окне готовых моделей отчетов можно увидеть, что корневой узел 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.
Установите для объектов атрибуты детализации по умолчанию. Если присмотреться к какому-либо объекту в окне готовых моделей отчетов, то можно увидеть на панели Properties несколько свойств, относящихся к атрибутам (например, DefaultDetailAttributes, IdentifyingAttributes, SortAttributes). Эти свойства используются, когда система генерирует отчеты с бесконечной детализацией и когда пользователь перетаскивает объект (а не поля объекта) в окно проектирования внешнего вида отчета в клиентской части Report Builder. Чтобы изменить атрибут Details, связанный с объектом Sales Person, нужно выделить объект Sales Person, выделить свойство DefaultDetailAttributes и нажать кнопку, которая появляется справа от свойства. Необходимо заменить элемент National ID Number на Full Name и переместить Full Name в верхнюю часть списка (поверх Commission Pct).
Данный материал является частной записью члена сообщества Club.CNews.
Редакция CNews не несет ответственности за его содержание.
- ведущий компьютерный учебный центр России с высочайшим уровнем качества обучения, сервиса, организации учебного процесса; в России, Центральной и Восточной Европе в 2011 г; в России;
- крупнейший авторизованный учебный центр ведущих IT-компаний мира.
В ассортименте Центра более 1000 обучающих курсов для специалистов и начинающих: компьютерные курсы, подготовка старшеклассников к ЕГЭ, популярные курсы 1С, курсы Unix (Linux / FreeBSD), обучение рисунку, обработке цифровых фотографий, курсы дизайна, курсы 3d max, бухгалтерские курсы, курсы windows server, занятия по основам программирования и многое другое. В нашем Центре Вы можете пройти компьютерные подготовительные курсы обучения и курсы повышения квалификации в очных группах (утром, днем, вечером, по выходным), дистанционно, индивидуально, в режиме вебинара. Центр имеет 8 учебных комплексов в Москве.
Приглашаем вас делиться комментариями о материалах CNews на наших страницах платформ Facebook, Telegram и Twitter.
Читайте также: