Генератор отчетов 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 . Таблица фактов (событий), произошедших за определенное время.

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

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

Решение.

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

Этот запрос вернет только те типы событий, что произошли в заданный период, а так же только тех сотрудников, которые фиксировали события за этот период. Для того отчета, который представлен выше, нам необходимы все сотрудники, а, значит, и внешнее соединение 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 и получаем следующую кросстаблицу в отчете:

 

 

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