Генератор звітів 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 і отримуємо наступну кросстабліцу у звіті:
 

 

Додаємо оформлення, і наш звіт готовий.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*