(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Генератор отчетов CrystalReports. Кросстаб. Готовим данные.

Наталия Пригодина

В данной статье предлагается разбор некоторых особенностей построения sql-запросов для кросстаба.

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

Итак:

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

2.    Сотрудники могут не быть на работе или не фиксировать события. В отчете должен быть отражен весь список сотрудников.

3.    В отчет необходимо передать два значения - начало периода и окончание периода. В примере, повторюсь, рассмотрим недельный отчет.

Нам необходим отчет вида

Таблица 1. Форма отчета.

Как мы видим, у нас 3 столбца и 1 строка, содержащих только 0. Нам необходимо видеть их в отчете, поэтому необходимо добиться, чтобы в запросе они тоже были.

Мы не рассматриваем построение запроса через графический построитель эксперта баз данных Crystal Reports, а пользуемся прямым вводом запроса, Командой, (в английской версии Add Command). В этом случае мы имеем в своем распоряжении широкие возможности SQL-диалекта выбранной базы данных (T-SQL, PL/SQL), ограниченные, пожалуй, только нашими знаниями.

Для каждой конкретной задачи источник данных будет свой. Мы рассмотрим для T-SQL и PL/SQL. Пример был проверен на MS SQL Server 2005 и Oracle 10. Таблички Action, TypeAction, Employee - часть некоторой базы данных, содержащие необходимые нам данные. Связи между табличками осуществляются с помощью внешних ключей TypeAction.TypeActionId - Action.TypeActionId и Employee.EmployeeId - Employee.EmployeeId.

Данные.

Таблица 2. Action . Таблица фактов (событий), произошедших за определенное время.

DateAction

Дата события

TypeActionId

Идентификатор типа события

EmployeeId

Идентификатор сотрудника

Description

Некое описание

Таблица 3. TypeAction . Таблица Типов действия, или Типов событий

TypeActionId

Идентификатор типа события

TypeActionName

Наименование типа события

Таблица 4. Employee . Таблица Сотрудников, которые фиксируют факт совершения События

EmployeeId

Идентификатор сотрудника

EmployeeName

ФИО сотрудника (упростим для примера)

DepartmentId

Идентификатор отдела

 

Решение.

Для начала соберем все таблички в один общий запрос.

Этот запрос вернет только те типы событий, что произошли в заданный период, а так же только тех сотрудников, которые фиксировали события за этот период. Для того отчета, который представлен выше, нам необходимы все сотрудники, а, значит, и внешнее соединение OUTER JOIN. Запрос приобретает такой вид:

 

Но также нужно учесть, что условие WHERE не отработает в случае значений NULL в столбце DateAction (для строк тех сотрудников, событий для которых не было за этот период). Поэтому необходима следующая обработка:

 

Или так:

 

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

Таблица 5. Результат запроса Код 5.

Создадим отчет на основе данного запроса. Кросстаб выглядит таким образом:

Сразу замечаем, что задача по включению в отчет всего списка сотрудников нами выполнена. Далее обращаем внимание на столбец с пустой датой - именно так наша кросстаблица реагирует на NULL в результате запроса Код 5.

Существует прием, позволяющий "скрыть" этот столбец, искусственно отнеся его к любому другому столбцу, например, к дате начала периода. Заменим этот столбец выражением:
 

Столбец сразу "сливается" со столбцом, относящимся к DataParam1, или, в нашем случае, с Пн, 28июня. Так как там содержатся только 0, результаты у нас будут верные.

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

Наиболее распространенное решение - добавить в запрос таблицу или подзапрос, содержащий последовательное перечисление дат заданного параметрами периода. Здесь варианты решения для T-SQL и PL/SQL различны.

Для T-SQL возможно:

1.    Создание функции, возвращающей таблицу, состоящую из одного столбца, который содержит последовательные даты.

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

3.    А также, в качестве примера, таким образом:

Соединяем. Не забываем обрабатывать строки "пустых" сотрудников.

В данных запросах (так же в следующем) параметры уже подставлены.
 
Отдельно нужно обратить внимание на строку:
 

Это сделано для того, чтобы из строк с одинаковым T.EmployeeId "выше" по списку шел T.EmployeeName с непустым значением. Как мы помним, мы заменили пустое значение в столбце T.EmployeeId на любое гарантированно существующее.

Таблица 6. Результат запроса Код 9.

 
Если этого не сделать, при установке Наименования группы как T.EmployeeName (рис.4) в группировке данных кросстаба в самом отчете по T.EmployeeId (рис.5) и мы получим такую картинку:
 
Поэтому пользуемся запросом Код 9 и получаем следующую кросстаблицу в отчете:
 
 

Добавляем оформление, и наш отчет готов.

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


 Распечатать »
 Правила публикации »
  Написать редактору 
 Рекомендовать » Дата публикации: 26.11.2010 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
SAP CRYSTAL Reports 2013 WIN INTL NUL
SAP CRYSTAL Server 2013 WIN INTL 5 CAL License
SAP® Crystal Presentation Design 2016 WIN INTL NUL
SAP Crystal Reports 2008 INTL WIN NUL License
SAP® Crystal Dashboard Design Departmental 2016 WIN INTL NUL
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
OS Linux для начинающих. Новости + статьи + обзоры + ссылки
Новые материалы
Мир OLAP и Business Intelligence: новости, статьи, обзоры
Delphi - проблемы и решения
Краткие описания программ и ссылки на них
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
 



    
rambler's top100 Rambler's Top100