и выбрать закладку Formula, илиВ предыдущих статьях были рассмотрены общие приемы работы с Crystal Reports 6.0. В настоящей статье будут рассмотрены способы применения формул Crystal Reports, приемы работы с SQL/ODBC источниками и модуль Crystal Query Designer.
и выбрать закладку Formula, или |
Арифметические функции (Arithmetic) позволяют осуществить достаточно
сложную статистическую обработку данных. В эту группу входят функции суммирования,
осреднения, округления, вычисления статистических величин и т.д. Некоторые
функции, например, суммирования Sum() или вычисления среднего Average(),
могут иметь от одного до трех параметров. Первый параметр - это величина,
по которой производится суммирование, например, поле БД. Второй параметр
- признак группы, по которой производится суммирование. Третий параметр
- условие суммирования. Например, функция Sum ({Customer.Last Year's Sales},{Customer.City}
) будет подсчитывать сумму продаж по каждому городу, функция
Sum({orders.AMOUNT}, {orders.DATE}, "monthly") будет суммировать поле
orders.AMOUNT, предварительно сгруппировав его по месяцам по значению поля
orders.DATE.
Строковые функции (Strings) предназначены для обработки текстовых полей БД. Например, функция Trim(String) удаляет пробелы справа и слева от строки String, (Trim(`' абв `') возвращает `'абв''). Функция ToText(x,y,z) конвертирует число в строку, x- константа, переменная или текстовое поле, которое необходимо конвертировать, y- число десятичных знаков после запятой, z- строка-разделитель целой и дробной части. ToText(1666.00,0,'''') возвращает значение `'1666''. Функция Mid(str,start,length) выделяет подстроку из строки, str - строка - источник, start - позиция начала подстроки, length - длина подстроки. Функция Lengt(str) возвращает длину подстроки. Функция ToWords (Number) возвращает сумму прописью (по-английски).
Функции даты и времени (Date/Time) оперируют с переменными типа дата-время. Функция Date(year,month,day) возвращает дату, year, month, day - числа, соответствующие году, месяцу и дню даты. Функция DayOfWeek (Date) возвращает номер дня недели. Функция CurrentDate - текущую дату (соответствует дате, установленной в Windows). Имеется набор функций для конвертации переменных типа дата-время в числовые переменные и наоборот.
Функции диапазона дат (Date Ranges) возвращают значение истина или ложь, если аргумент входит в заданный диапазон дат. Приведем некоторые из них:
Функции времени выполнения (Evaluation Time). Crystal Reports является двухпроходным генератором отчетов, что означает, что данные обрабатываются дважды - во время чтения и во время печати. Все формулы, встречающиеся в отчете, выполняются в следующей последовательности:
1. Формулы, не содержащие других формул или полей БД, например 2*2 или
CurrentDate. Этот этап называется "Перед чтением записей" (BeforeReadingRecords).
2. Формулы, содержащие поля БД. Этот этап называется "Во время чтения
записей" (WhileReadingRecords).
3. Формулы, определяющие условия выборки (см. ниже).
4. Формулы, подсчитывающие суммирующие значения в группах.
5. Формулы, в которые входят суммарные и средние значения, проценты
по отношению к суммарным
значениям, специальные функции RecordNumber, GroupNumber, Previous,
Next и т.д. Этот этап называется "Во время печати записей" (WhilePrintingRecords).
Последовательность выполнения формул можно нарушить, явно указав в тексте формулы этап выполнения (BeforeReadingRecords, WhileReadingRecords, WhilePrintingRecords), либо указав имя формулы, после выполнения которой следует выполнить данную формулу (EvaluateAfter(FormulaName)). Можно "отложить" выполнение формулы, но нельзя выполнить ее раньше, чем это предписано стандартной процедурой выполнения. Например, если попытаться создать формулу
BeforeReadingRecords;
{Orders.Order ID}
она будет воспринята, как содержащая синтаксическую ошибку, поскольку формула "Перед чтением записей" не должна содержать полей БД.
В разделе Дополнительные функции (Additional Functions) содержится большой набор финансовых функций, функций конвертации данных из нестандартных форматов (например, из строки в дату-время) и просто полезных функций.
#include <Windows.h>
#include "UFDll.h"
#include "UFMain.h"
#include "UFUser.h"
#define PicturePlaceholder 'x'
ExternC UFError FAR _export PASCAL Picture
{
void TermForJob (UFTInt32u jobID)
{
}
static void copyUsingPicture (char *dest,
const char *source, const char *picture)
{
ExternC UFError FAR _export PASCAL Picture
If (FirstParam == NULL || SecondParam ==
NULL)
В дистрибутив Crystal Reports 6.0 входит директория UFL (не устанавливается при инсталляции !), в которой содержатся DLL - файлы с некоторыми полезными функциями и их описаниями. Эти файлы можно просто скопировать в директорию Seagate Crystal Reports, после чего использовать функции UDF в отчетах.
 |
 |
Использование функций для выборки данных. При установке условий
выборки записей в диалоге Select Expert результирующее условие выборки
формируется в виде формулы , возвращающей значение TRUE, если запись удовлетворяет
условию ( тогда она включается в отчет) и FALSE, если не удовлетворяет.
Эту формулу можно просмотреть, кликнув по кнопке Show Formula и отредактировать
при помощи редактора формул, кликнув по кнопке Formula Editor... По умолчанию
условия выборки объединяются логическим "и", но вручную условия можно объединять
любыми логическими операторами. Например, формула, которая выбирает записи,
соответствующие заданным при помощи параметров ?CoutryFilter и ?RegFilter
страну и регион, либо те записи, у которых значение поля {Orders.Order
Amount} больше 1000, выглядит так:
({Customer.Country} = {?CoutryFilter}
and
{Customer.Region} = {?RegFilter}) or {Orders.Order Amount} > 1000
Использование функций для форматирования по условию. Любое поле отчета можно отформатировать, кликнув по нему правой кнопкой мыши и выбрав из контекстного меню Format Field... В появившемся диалоге Format Editor содержится набор опций форматирования (на нескольких закладках).
 |
,
кликнув на которую можно задать формулу форматирования (при помощи того
же Formula Editor). Форматирование будет выполнятся, если формула будет
возвращать соответствующее значение.
if {Orders.Order Amount}>30000 then "Хорошо" else
if {Orders.Order Amount}<1000 then "Плохо"
и поместим ее рядом с полем {Orders.Order Amount}
2. Отформатируем формулу Comment1, кликнув правой кнопкой мыши и выбрав из контекстного меню Format Field... В диалоге Format Editor выберем закладку Font и кликнем по кнопке опции Color. В диалоге Format Formula Editor Создадим формулу:
if {Orders.Order Amount}>30000 then Red else
if {Orders.Order Amount}<1000 then Blue
Еще один пример. Предположим, необходимо сгенерировать текст писем для рассылки, причем содержимое письма должно зависеть от значения какого-либо поля БД (для определенности- {Orders.Order Amount}). Допустим, необходимо иметь 3 варианта текста-
1. Правой кнопкой кликнуть по заголовку секции Deteil , выбрать Format
Section и дважды кликнуть по кнопке New для вставки двух дополнительных
секций - Deteil b и Deteil c.
2. Выбрать в списке секцию Deteil a и кликнув по кнопке условного форматирования
опции Suppress (No Drill-Down), внести формулу
{Orders.Order Amount}<30000
Аналогично для секции Deteil b формулу
{Orders.Order Amount}>=30000 or {Orders.Order Amount}<=1000
И, для секции Deteil c
{Orders.Order Amount}>1000
3. Кликнув по кнопке OK, вернуться в режим Design и внести в секции Deteil a , Deteil a и Deteil a тексты писем Text1, Test2 и Text3 соответственно. Тексты могут включать различные поля БД и формулы. Отметим, что если необходимо расположить каждое письмо на отдельной странице, при форматировании секции следует выбрать опцию New Page After.
Вычисление кумулятивного значения. Допустим, отчет содержит поле суммы заказа {Orders.Order Amount}. Необходимо пронумеровать все заказы с суммой более 1000.
1.В секцию Report Header поместим формулу @Reset
WhilePrintingRecords;
numberVar Cnt :=0 ;
2. В секцию Deteil рядом с полем {Orders.Order Amount} поместим формулу @Calculate
WhilePrintingRecords;
numberVar Cnt;
if {Orders.Order Amount}>1000 then (Cnt := Cnt+1 ; Cnt)
3. Отформатируем формулу @Calculate, указав опцию Suppress if Zero и установив Decimal = 1
В результате отчет будет выглядеть примерно так:
Order Amount Calculate
5 060
1
187
50
2 215
2
29
14 872
3
0
10 259
4
1 142
5
Вычисление суммирующих значений в "пересекающихся" группах. Предположим, в БД имеются два поля - страна и население со следующим содержанием:
Испания 39
000 000
Великобритания 57 000 000
Колумбия 30 300
000
Аргентина 31 960 000
Бразилия 144 300 000
Необходимо подсчитать население испаноговорящих стран и население стран, расположенных в Южной Америке. Если сгруппировать страны по признаку континентов, можно внести в отчет поле Subtotal и подсчитать население стран, находящихся на различных континентах. Но при этом нельзя одновременно (используя Subtotal) подсчитать население испаноговорящих стран, поскольку Испания, Аргентина и Колумбия будут входить в разные группы. Решить задачу можно используя формулы.
1. Создадим отчет с полями {Country} и {Population}
2. В секцию Deteil поместим формулу @SAm
if {Country} in ["Колумбия ", " Аргентина", "Бразилия "] then {Population}
else 0
и формулу @SpainL
if {Country} in ["Колумбия ", " Аргентина", "Испания "] then {Population}
else 0
3. В секции Report Footer суммирующие поля Grand Total по формулам @SAm (население стран, расположенных в Южной Америке) и @SpainL (население испаноговорящих стран)
,
появится диалог Log On Server,
 |
1. По умолчанию Visual Liking Expert устанавливает связи по одинаковым именам колонок, что не имеет никакого отношения к реальным связям, определенным в РСУБД (по первичным и внешним ключам). Как правило, необходимо вручную редактировать установленные по умолчанию связи. В отличие от Data File - источников здесь связи можно устанавливать между любыми, не обязательно индексированными полями.
2. Помимо таблиц, в качестве источников данных можно использовать записанные процедуры. В настройке (меню File|Options, закладка SQL) нужно включить опцию Stored Procedures, после чего в диалоге Choose SQL Table в списке таблиц появятся имена записанных процедур. При включении процедуры в отчет необходимо указать входные параметры. Их можно поменять, выбрав в меню Database / Stored Procedure Parameters. Выходные параметры можно использовать так же, как поля БД.
3. Результирующий SQL - запрос можно просмотреть, выбрав из меню Database / Show SQL Query. В данной статье не рассматривается синтаксис языка SQL. Неопытным пользователям можно порекомендовать книгу Мартина Грабера "Введение в SQL" (Перевод, издательство "Лори", 1996 г.)
 |
 |
На закладке Tables следует указать таблицы, которые будут входить в
SQL- запрос. Для этого можно непосредственно указать ODBC-источник, либо
воспользоваться словарем (работа со словарями Crystal была описана в предыдущей
статье). При этом возникнет диалог Logon, в котором нужно указать имя пользователя
и пароль. Таблицы, указанные на закладке Tables, включаются в предложение
FROM.
На закладке Links необходимо указать связи между таблицами. Интерфейс
закладки аналогичен Visual Liking Expert модуля Crystal Reports Designer
(см. первую статью серии).
Закладка Fields выбора служит для указания полей, которые будут включены в запрос. В левой части закладки перечислены таблицы, которые предварительно были выбраны в закладке Tables. Дважды кликнув по названию таблицы, можно свернуть или развернуть список полей таблицы. В правой нижней части закладки расположен раскрывающийся список Total, который позволяет включить в запрос агрегативные функции языка SQL (не путать с функциями Crystal). Кликнув по кнопке Brose, можно просмотреть значения полей в БД. Будут показаны первые 100 неповторяющихся значений.
Закладки Sort и Select служат для установки условий сортировки (предложение ORDER BY) и выборки (предложение WHERE). Интерфейс закладок аналогичен диалогам Record Sort Order и Select Expert модуля Crystal Reports Designer.
После задания таблиц, полей, условий выборки и сортировки на закладке SQL, можно просмотреть результирующий запрос. Запрос можно выполнить и просмотреть результирующий набор данных, кликнув по кнопке Preview Query. Если результат не удовлетворителен, запрос можно отредактировать вручную, выбрав из меню Edit /Query... Появляется диалог Enter SQL Statement.
 |
Отметим одну особенность применения агрегативных функций. На рисунке видно, что Crystal Query Expert автоматически присвоил имя колонке, отображающей значение функции (Expr_1), что не всегда удобно. Для переименования результирующих колонок необходимо воспользоваться синонимами. Аналогичный приведенному на рисунке запрос, написанный вручную, выглядит так:
SELECT b.DESCRIPTION, AVG( a.LIST_PRICE) AS AVERAGES
FROM demo.PRICE a, demo.PRODUCT b
WHERE a.PRODUCT_ID = b.PRODUCT_ID
GROUP BY b.DESCRIPTION
Колонка с результатами осреднения получит имя AVERAGES.
Запросу можно присвоить имя , выбрав из меню Edit /Query Title... Сохранив
запрос (File / Save), можно использовать его при создании отчетов в Crystal
Reports Designer. Запрос сохраняется как файл с расширением qry. При создании
нового отчета с помощью Reports Expert необходимо просто указать в качестве
источника Query и выбрать нужный файл с сохраненным запросом.
В следующей статье будет рассмотрена архитектура доступа к данным и способы включения отчетов Crystal Reports в приложения, написанные на Borland Delphi 3.0 и SQLWindows (Centura).
Координаты автора:
Учебно-консалтинговый центр Interface Ltd.,
Тел. (095)135-55-00, 135-25-19, 135-77-81,
e-mail: mail@interface.ru
http://www.interface.ru