(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 и получаем следующую кросстаблицу в отчете:
 
 

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

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


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

Магазин программного обеспечения   WWW.ITSHOP.RU
SAP Crystal Reports 2008 INTL WIN NUL License
SAP® Crystal Dashboard Design Departmental 2016 WIN INTL NUL
SAP Crystal Reports XI R2 Dev 2006 INTL WIN NUL License (Version 11)
SAP® Crystal Reports 2016 WIN INTL NUL
SAP Crystal Server 2011 WIN INTL 5 CAL License
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование в AutoCAD
eManual - электронные книги и техническая документация
Новые материалы
Мир OLAP и Business Intelligence: новости, статьи, обзоры
Delphi - проблемы и решения
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Пишу программы на заказ для студентов (243)
Пишу для студентов на с, с++, паскаль в средах ms visual studio, qt, builder, borland c, delphi....
 
Разработка программ базы данных (54)
Написание прикладных компьютерных программ (базы данных) на заказ. Разработка корпоративных...
 
Ищу программиста для написания программы (58)
Ищу программиста ,владеющего Вижуал Бэйсик и программированием в Экселе, для написания...
 
Написание любых программ на заказ (16)
Напишу любую программу на заказ
 
Качество образования в российских школах сможет выйти на новый уровень благодаря Office 365 (2)
Мне кажется, что образование за границей все равно на много лучше. Наши вузы, а особенно школы...
 
 
 



    
rambler's top100 Rambler's Top100