Фильтрация данных в отчете
Записи в отчетах, так же как и в формах, могут фильтроваться. Для этого существует свойство отчета
Фильтр
(Filter). Значением этого свойства является логическое выражение WHERE инструкции SELECT. Применение фильтра приводит к тому, что, когда для конкретной записи заданное в качестве фильтра логическое выражение принимает значение Истина (True), эта запись отображается, в противном случае — нет. Пример такого выражения представлен на рис. 10.29 (задан интервал дат, в котором должно лежать значение поля "ДатаИсполнения" (ShippedDate)). Для того чтобы фильтр, введенный в диалоговом окне свойств отчета, был применен к записям в отчете, требуется еще установить значение свойства
Фильтр включен
(Filter On) равным Да (Yes). В противном случае он сохраняется, загружается при выполнении отчета, но не применяется. Свойство можно установить либо в диалоговом окне свойств, как это показано на рис. 10.29, либо в макросе или процедуре VBA.
Настройка печати групп отчета
Кроме тех свойств групп отчета, которые обсуждались выше, существует еще несколько свойств разделов отчета, которые влияют на представление групп в отчетах. Теперь мы опишем эти свойства.
CompEbook.ru Железо, дизайн, обучение и другие
Нумерация записей и подсчет количества записей в отчете
Свойство
Сумма с накоплением
(Running Sum), которое мы рассматривали выше, может быть использовано еще одним, несколько неожиданным способом. Оно используется для нумерации записей (строк) в отчете. Если форма отчета требует, чтобы строки отчета были пронумерованы, для этого придется создать вычисляемое поле, свойство
Данные
(Control Source) которого определяется выражением: =1, а свойство
Сумма с накоплением
(Running Sum) нужно установить равным
Для всего
(Over All) или
Для группы
(Over Group), если требуется нумерация записей в пределах группы. Тогда при печати отчета значение этого поля будет увеличиваться на единицу для каждой новой записи. Пример нумерации записей описанным образом вы можете увидеть в отчете "Продажи по годам" (Sales by Year) базы данных "Борей" (Northwind).
Иногда в отчете требуется сосчитать количество записей, содержащихся в каждой группе, например количество заказов у каждого клиента или количество счетов, выписанных сотрудником отдела продаж. Такое значение так же, как и итоговая сумма, обычно выводится в примечании группы. Для того чтобы его отобразить, нужно в области данных создать вычисляемое поле, которое будет содержать номер строки (как это описано выше), и сделать это поле невидимым, т. е. его свойству
Вывод на экран
(Visible) присвоить значение Нет (No). Затем в области примечания группы нужно создать вычисляемое поле, свойство
Данные
(Control Source) которого должно ссылаться на это невидимое поле. Примеры таких полей приводятся на рис. 10.26.
Объединение текстовых данных
и печать условий отбора записей в отчете
В отчетах чаще, чем в формах, применяются вычисляемые поля, которые представляют собой комбинацию данных из различных полей таблиц или базовых запросов. Типичным примером такого объединения является поле, содержащее фамилию и инициалы человека, хотя в базе данных часто фамилия, имя и отчество хранятся в разных полях. Второй, не менее распространенный случай, когда в одном столбце отчета требуется вывести адрес, который обычно тоже хранится в нескольких отдельных полях таблицы. Часто в одном столбце отчета объединяют данные из нескольких полей таблицы в целях уменьшения ширины отчета. Примеры создания текстовых выражений такого типа приводились ранее
(см. гл. 4, 6).
Еще один типичный случай объединения текстовых полей использовался нами в отчете "Заказы клиентов" (см. рис. 10.21). Это поля, которые выглядят в отчете как надписи ("Итого по заказу <номер>:", "Итого по клиенту <имя>:"), хотя на самом деле представляют собой комбинацию текстовых строк и значений полей базового запроса.
Иногда в отчетах нужно вывести условия отбора записей. Для этого также применяют вычисляемые поля, содержащие комбинацию текста и параметров отбора. Пример такого выражения показан на рис. 10.27: в заголовке отчета создано поле, которое вычисляется с помощью, выражения, содержащего ссылки на параметры базового запроса [Начальная дата] и [Конечная дата]. В этом же отчете используются и другие вычисляемые поля, объединяющие текстовые данные из разных полей.
Аналогично можно написать выражение, которое будет ссылаться на значения полей открытой формы, часто используемой для ввода критериев отбора записей. Примером такой формы, представляющей собой диалоговое окно, может быть форма "Продажи по годам" (Sales by Year Dialog), представленная на рис. 10.28.
Для того чтобы в заголовке отчета написать интервал дат, заданных в форме "Продажи по годам", необходимо для источника данных вычисляемого поля использовать выражение: ="Продажи с " & Forms![Продажи по годам]![НачальнаяДата] & " по " & Forms![Продажи по годам]![КонечнаяДата] для английской версии: ="Продажи с " & Forms![Sales by Year Dialog]![BeginningDate] & " no " & Forms![Sales by Year Dialog]![EndingDate]
Замечание
Для того чтобы значения таких выражений были правильными, необходимо, чтобы форма, на которую они ссылаются (в данном случае "Продажи по годам"), была открыта в момент загрузки отчета. В противном случае в этих вычисляемых полях будет отображен код ошибки.
Определение содержимого отчета "Заказы клиентов"
После того как мы создали структуру отчета, можно начать помещать поля в разделы отчета:
Выберите команду
Файл, Параметры страницы
(File, Page Setup) и установите в диалоговом окне поля отчета: верхнее, нижнее и левое — размером 2,5 см, правое — 1 см (в полях диалогового окна эти значения задаются в миллиметрах). Затем закройте окно
Параметры страницы
(Page Setup) и перетащите правую границу области данных вправо, установив ширину отчета 17 см.
В разделе
Заголовок отчета
(Report Header) создайте надпись Заказы клиентов. Установите шрифт надписи Times New Roman Cyr размером 21 пункт. Над надписью добавьте горизонтальную линию шириной 2 пункта от левого до правого края отчета. Высота раздела заголовка отчета в нашем примере составляет 1,6 см.
Замечание
Если раздел Заголовок отчета (Report Header) отсутствует в окне Конструктора, выберите команду Вид, Заголовок/примечание отчета (View, Report Header/Footer).
Раздел
Верхний колонтитул
(Page Header) оставьте пустым и устанавливаем его высоту равной 0.
Оформим раздел заголовка группы "Название" (CompanyName Header):
Откройте список полей и перетащите из него в раздел заголовка группы следующие поля: "Название" (CompanyName), "Страна" (Country) и "Город" (City).
Удалите присоединенные к полям надписи и установите для всех полей шрифт Arial Cyr размером 14 пунктов и прозрачный фон.
Выделите область этого раздела, разместив элемент
Прямоугольник
(Rectangle), имеющий цвет фона серый и ширину границы 2 пункта, позади текстовых полей (рис. 10.20).
Сократите высоту раздела так, чтобы его нижний край совпадал с нижней границей прямоугольника.
Прежде чем размещать остальные поля, определим некоторые стандартные свойства этих полей (то есть свойства, которые будут задаваться по умолчанию для каждого нового создаваемого в отчете текстового поля). Для этого нажмите кнопку
Поле
(Text Box) на панели элементов (Toolbox) и нажмите кнопку
Свойства
(Properties) на панели инструментов
Конструктор отчетов
(Report Design). Откроется окно
"Поле" — стандартные свойства
(Default Text Box). Задайте следующие значения свойств:
Шрифт
(Font Name) — Arial Cyr,
Размер шрифта
(Font Size) — 9,
Насыщенность
(Font Weight) —
обычный
(Normal),
Добавление подписи
(Auto Label) — Да (Yes),
Подписи с двоеточием
(Add Colon) — Пет (No). Закройте окно свойств поля. Затем нажмите кнопку
Надпись
(Label) и установите такие же значения для свойств, определяющих шрифт надписи.
Перетащите из списка полей в раздел заголовка группы "КодЗаказа" (OrderlD Header) следующие поля: "КодЗаказа" (OrderlD), "ДатаРазмещения" (OrderDate), "ДатаНазначения" (RequiredDate) и "ДатаИсполнения" (ShippedDate). Измените надписи для полей так, как это показано на рис. 10.20. Выделите эти поля, разместив позади них прямоугольник, имеющий ширину границы 2 пункта и тип фона
Прозрачный
(Transparent).
Определение условий сортировки и группировки
Теперь определим структуру отчета, задавая условия группировки и сортировки записей. Эти условия задаются в диалоговом окне
Сортировка и группировка
(Sorting and Grouping), которое появляется при нажатии кнопки с таким же названием на панели инструментов.
Чтобы создать группу, нужно задать поле отчета, по которому будут группироваться записи, и установить значения свойств
Заголовок группы
(Group Header) и
Примечание группы
(Group Footer) равными Да (Yes). Первый уровень группировки в нашем примере будет определяться полем "Название Клиента" (Company-Name). Выберите это поле из раскрывающегося списка в первой строке окна
Сортировка и группировка
(Sorting and Grouping). Поле справа в этой строке, определяющее порядок сортировки, будет по умолчанию иметь значение
По возрастанию
(Ascending). Установите значения свойств
Заголовок группы
(Group Header) и
Примечание группы
(Group Footer) в Да (Yes). После этого в области выделения первой строки (в окне) появится символ группировки, а в отчете будут отображены два раздела:
Заголовок группы 'Название'
(CompanyName Header) и
Примечание группы 'Название'
(CompanyName Footer).
Второй уровень группировки будет организован по полю "КодЗаказа" (OrderlD). Заполните аналогичным образом его свойства в окне
Сортировка и группировка
(Sorting and Grouping).
Кроме того, что мы задали два уровня группировки для нашего отчета, можно еще определить порядок сортировки в пределах самой внутренней группы. В третьей строке окна
Сортировка и группировка
(Sorting and Grouping) выберите поле "Марка" (ProductName), чтобы отсортировать товары в заказе по алфавиту. После этого отчет примет вид, показанный на рис. 10.19.
Уровень группировки по полю "КодЗаказа" оказался вложенным. И таких уровней вложенности в Access 2002 может быть до 10.
Для каждой группы, кроме свойств
Заголовок группы
(Group Header) и
Примечание группы
(Group Footer), которые позволяют вставлять в отчет дополнительные разделы, можно задать еще три свойства:
Group Interval) связаны между собой
Группировка
(Group On),
Интервал
(Group Interval) и
Не разрывать
(Keep Together).
Свойства
Группировка
(Group On) и
Интервал
( Group Interval) связаны между собой и в совокупности определяют, каким образом мы хотим группировать данные. Значения, которые может принимать свойство
Группировка
(Group On), зависят от типа данных группирующего поля. Обычно данные группируются по каждому значению поля, т. е. каждому значению поля должна соответствовать отдельная группа. Тогда свойство
Группировка
(Group On) должно иметь значение
По полному значению
(Each Value), а значение свойства
Интервал
(Group Interval) должно быть равным 1. В нашем примере для поля "Название" установите эти свойства именно так.
При группировке по полю с текстовым содержимым допускается начинать новую группу всякий раз, когда изменится первый или несколько первых символов значения поля. В этом случае свойство
Группировка
(Group On) должно иметь значение
По первым знакам
(Prefix Characters), а свойство
Интервал
(Group Interval) будет определять, сколько первых символен должно использоваться для образования новой группы. Если это значение равно 1, то для каждой буквы алфавита будет создана Сдельная группа — это позволяет создавать отчеты типа Предметного указателя в книгах. Примером такого отчета является отчет "Список товаров" (Alphabetical List of Products) в базе данных "Борей" (Northwind).
Печать отчета в несколько колонок
Иногда возникает необходимость отобразить данные на листе бумаги в несколько колонок. Это может потребоваться для компактного представления данных в отчете. Пример такого отчета приведен на рис. 10.16 — это отчет "Товары по типам" (Products by Category) из базы данных "Борей" (Northwind). В нем содержатся данные о количестве товаров на складе. Данные о товарах группируются по категориям товаров и отображаются в три колонки.
Чтобы получить такой отчет, нужно задать определенным образом параметры печати отчета. Эти параметры задаются в диалоговом окне
Параметры страницы
(Page Setup) (рис. 10.17). Для того чтобы вывести это окно, в режиме Конструктора отчета, выберите команду меню
Файл, Параметры страницы
(File, Page Setup). На вкладке
Поля
(Margins) определите ширину полей страницы. Затем раскройте вкладку
Столбцы
(Columns).
Группа полей
Параметры сетки
(Grid Setting) позволяет определить количество колонок на листе (поле
число столбцов
(Number of Columns)) и расстояние между колонками (поле
столбцов
(Column Spacing)). Поле
интервал строк
(Row Spacing) используется при печати наклеек на конверты.
Группа полей
Размер столбца
(Column Size) позволяет определить ширину колонки. Если установить флажок
по размеру данных
(Same As Detail), то поля
ширина
(Width) и
высота
(Height) будут установлены равными ширине и высоте раздела
Область данных
(Detail) отчета. Если этот флажок сброшен, то значение поля
ширина
(Width) будет определять ширину колонки (поле
высота
(Height) используется для определения размера наклеек).
Переключатели в группе
Макет столбца
(Column Layout) определяют ориентацию колонок. В данном случае выбран переключатель
сверху вниз
(Down, then Across), который обеспечивает печать данных в виде газетных полос.
Перекрестные отчеты
Перекрестные отчеты строятся на основе перекрестных запросов и содержат итоговые данные
(см. также гл. 8).
В предыдущем разделе мы использовали один из таких перекрестных запросов для создания подчиненного отчета "Квартальные обороты клиентов по товарам". Аналогично создается и обычный отчет на базе перекрестного запроса. Пример такого отчета приведен на рис. 10.13. Он создан на базе того же самого запроса "Квартальные обороты клиентов по товарам", но отображает данные в другом разрезе: проданные товары группируются по наименованию, затем по клиентам. Разработать такой отчет не сложно:
Создайте новый отчет в режиме Конструктора и выберите для него в качестве источника данных запрос "Квартальные обороты клиентов по товарам".
Разместите надписи, содержащие заголовки столбцов отчета, в разделе
Верхний колонтитул
(Page Header), а все текстовые поля со значениями — в области данных. (Еще проще создать такой отчет с помощью автоотчета ленточный.)
Определите в окне
Сортировка и группировка
(Sorting and Grouping) группировку по полям "Марка" (ProductName) и "КодКлиента" (CustomerlD). Для поля "Марка" отобразите заголовок группировки, выбрав значение Да (Yes) в раскрывающемся списке
Заголовок группы
(Group Header). Закройте окно.
Перенесите текстовое поле "Марка" в отображенный заголовок группы.
Замечание
В данном запросе, когда он был создан для примера с подчиненным отчетом, не был задан параметр для выбора только относящихся к указанному отчетному году записей, т. к. записи выбирались на основании связанных данных из главного отчета. Поэтому, чтобы в создаваемом теперь отчете отображались только записи за указанный год, определите выражение с параметром для фильтрации данных в новом отчете: ГодЗака-за = [Отчетный год:] (для английской версии используйте английское имя поля OrderYear) (как задать фильтр, рассказано в разд. "Фильтрация данных в отчете" этой главы).
Отчет готов. Такой отчет называется
статическим
перекрестным отчетом, т. к. хотя его источник данных — перекрестный запрос, число столбцов в нем всегда одинаково, заголовки столбцов являются постоянными. Он создается так же, как и обычный отчет, на базе готового запроса.
Подготовка составных отчетов
Одной из важнейших функций, которые обычно предоставляют настольные СУБД, является возможность создания различных отчетов — документов, содержащих дан--ные из таблиц базы данных. Отличительной особенностью Microsoft Access является наличие очень мощного генератора отчетов, который позволяет создавать отчеты высокого качества. Наличие мастеров, генерирующих автоматически или при помощи диалога с пользователем самые разнообразные отчеты, значительно облегчает трудоемкий процесс их создания. В
гл. 6
мы уже рассматривали основные вопросы, касающиеся создания отчетов в Access. В этой главе мы поговорим о них более подробно, ориентируясь на профессиональное использование средств Access 2002. В частности, покажем, как с помощью Мастера отчетов или в режиме Конструктора создавать достаточно сложные по структуре отчеты, включающие подчиненные отчеты или имеющие несколько уровней группировки записей. А также обсудим здесь многие другие вопросы о работе с отчетами, не вошедшие
в гл. 6.
Мы не будем затрагивать здесь тему программирования на VBA или использования макросов при создании отчетов, оставив ее для рассмотрения в последующих главах
(гл. 11 и 13),
и покажем, как много можно сделать, -применяя только интерактивные средства, предоставляемые Access. В качестве примеров будут использованы уже готовые отчеты, которые содержатся в учебной базе данных "Борей" (Northwind), или отчеты, созданные на основе таблиц этой базы данных.
Microsoft Access 2000 и 2002 имеют существенные отличия и преимущества по сравнению с другими наиболее известными СУБД: это главная черта продуктов Microsoft — удобство использования и одновременно мощность продукта — в сочетании с возможностью построения комплексных решений на базе современных технологий, а именно: интеграция баз данных с SQL Server, с другими приложениями Office и публикация интерактивных данных в Web. В Access 2002 средства публикации в Web значительно расширились. Это касается и отчетов: теперь они могут быть преобразованы в формат XML и в интерактивные страницы доступа к данным. Однако это отдельная тема, которая освещена
в гл. 12.
В настоящей главе рассмотрены следующие вопросы:
Создание отчетов, включающих подчиненные отчеты
Создание отчетов на базе перекрестных запросов
Печать отчета в несколько колонок — в стиле газетных полос
Способы сортировки и группировки записей в отчете
Вычисления в отчетах
Фильтрация данных в отчете
CompEbook.ru Железо, дизайн, обучение и другие
Повторение заголовка группы на каждой странице или колонке
Если группа занимает при печати больше одной страницы или переносится па следующую страницу, можно заставить Access печатать заголовок группы на каждой странице. Для этого используется свойство
Повторение раздела
(Repeat Section) заголовка группы. По умолчанию это свойство имеет значение
Нет
(No).
CompEbook.ru Железо, дизайн, обучение и другие
Применение подчиненных отчетов
По аналогии с составными формами, включающими подчиненные формы, можно предположить, что составные отчеты используются также и для печати данных из таблиц, связанных отношением "один-ко-многим". Однако это не совсем так. В большинстве случаев для отображения данных из связанных таблиц не требуется внедрять подчиненные отчеты
(пример такого отчета см. в разд. "Создание отчета с помощью Мастера отчетов"гл. 6).
Например, если нужно создать отчет по товарам с разбивкой по категориям товаров, это можно сделать двумя способами:
в основном отчете выводятся данные о категории товаров, а в подчиненном — о товарах, входящих в категории. И это будет аналогично созданию формы "Категории товаров", в которую включается подчиненная форма, отображающая данные из таблицы товаров;
то же самое может быть выполнено без использования подчиненного отчета с помощью группировки товаров по категориям. Отчет в этом случае должен строиться на базе запроса, включающего данные из таблиц "Категории" и "Товары".
Тем не менее бывают ситуации, когда использование подчиненного отчета необходимо или предпочтительно. Например:
Главный отчет представляет собой контейнер, в который включаются один или более подчиненных отчетов из несвязанных таблиц. В этом случае главный отчет может вообще не быть связанным с таблицей или запросом и содержать только надписи и вычисляемые элементы.
Иногда итоговые данные требуется представить в документе в виде отдельной таблицы наряду с детальными данными. В этом случае просто группировкой записей с итоговыми строками не обойтись, нужно ввести подчиненный отчет, который выведет итоговые данные в требуемом виде.
Группировка в отчетах Access является очень мощным механизмом, который позволяет получить самые разные способы обработки данных отчета. Однако только при использовании подчиненных отчетов можно создать группы, которые содержат две и более подгруппы данных.
Главный отчет может содержать, во-первых, несколько подчиненных отчетов и, во-вторых, два уровня вложенности подчиненных отчетов. И, кроме того, на любом уровне вложенности наряду с подчиненным отчетом можно вставить в главный отчет подчиненную форму.
Основное достоинство подчиненных отчетов состоит в том, что с их помощью можно разместить на странице отчета самую разнообразную информацию в удобном структурированном виде, чего невозможно добиться, если просто создать сложный запрос, включив в него данные из многих таблиц.
Примеры использования подчиненных отчетов можно найти в списке отчетов учебной базы данных "Борей" (Northwind). На рис. 10.1 представлен отчет "Продажи по типам" (Sales by Category), который содержит один подчиненный отчет и внедренную диаграмму.
Проведение вычислений
В
гл. 6
и в предыдущих разделах данной главы мы создавали вычисляемые поля в отчетах главным образом для вывода итоговых значений. Сейчас мы покажем еще несколько типов вычисляемых полей, которые применяются для решения типовых задач.
CompEbook.ru Железо, дизайн, обучение и другие
Расположение групп в отчетах, имеющих несколько колонок
При печати отчета в несколько колонок (полос) на любом уровне группировки можно указать, чтобы каждая группа этого уровня всегда начиналась в новой колонке. Примером такого отчета является отчет "Товары по типам" (Products by Category) демонстрационной базы данных "Борей".
Для регулировки расположения групп на странице такого отчета используется свойство
Новая строка или столбец
(New Row or Col) раздела заголовка или примечания группы отчета. Установив это свойство равным До
раздела
(Before Section) для заголовка группы, можно заставить Access печатать каждую группу этого уровня группировки в новой колонке или новой полосе. Если значение свойства
Новая строка или столбец
(New Row or Col) установить равным
После раздела
(After Section), то данный раздел отчета печатается в текущей колонке или полосе, а следующий начинается в новой. И наконец, если значение этого свойства для заголовка группы установить равным
До и после раздела
(Before & After), то заголовок группы будет печататься в отдельной колонке или полосе, т. к. следующий за ним раздел данных тоже начнет печататься в новой колонке или полосе.
CompEbook.ru Железо, дизайн, обучение и другие
Отчет, содержащий подчиненный отчет
Откройте отчет "Продажи по типам" (Sales by Category) в режиме Конструктора. Вы увидите, что подчиненный отчет размещен в разделе заголовка группы "Категория" (CategoryName) (рис. 10.2). Оба отчета, и главный и подчиненный, базируются на одном и том же запросе "Продажи по типам" (Sales by Category), но в главном отчете выводятся данные о категориях — имя категории, а в подчиненном — о товарах, входящих в эту категорию (наименование товара и его цена).
Выделите элемент управления
Подчиненная форма/отчет
(Subform/siibreport) и посмотрите свойства этого элемента. Вы увидите, что главный и подчиненный отчеты связываются по полю "Категория" (CategoryName). Поскольку и в формах, и в отчетах используется один и тот же элемент управления
Подчиненная форма/отчет,
свойства для связанных полей те же, что и в форме
(см. гл. 9).
Обратите внимание, что в макете подчиненного отчета нет элемента управления, связанного с полем "Категория", однако, как и при связывании форм, в качестве полей для связи можно использовать поля базового запроса.
Отчет "Продажи по типам" в режиме Конструктора
Отчет "Продажи по годам" (Sales by Year) является примером отчета, в котором подчиненный отчет используется для вывода итоговых данных о продажах по кварталам года (рис. 10.3).
Использование подчиненного отчета для вывода итогов
Оба отчета, основной и подчиненный, базируются на запросе "Продажи по годам" (Sales by Year), который позволяет выбрать все заказы за указанный период времени (проверяется поле "ДатаИсполнения" (ShippedDate). Запрос включает вычисляемое поле "Год" (Year), которое рассчитывается на основе даты заказа и используется при группировке в отчете, и поле, содержащее итоговую сумму каждого заказа "ПромежуточнаяСумма" (Subtotal).
Подчиненный отчет "Подчиненный для продаж по годам" (Sales by Year Subreport) содержит несколько вычисляемых элементов:
ГодНадпись" (YearLabel), который отображается в верхнем левом углу, вычисляется с помощью выражения:
= "Итог за " & [Год] & "
г." (или=[Year] & " Summary");
"Квартал" (Quarter), который в качестве источника данных содержит выражение:
=Format([ДатаИсполнения];"q") (ИЛИ =Format([ShippedDate];"q"));
"ПодсчетПромежуточнойСуммы" (CountSubtotal) — количество исполненных заказов, которое вычисляется с помощью статистической функции Count:
=Count([КодЗаказа])
(или
=Count([OrderlD]));
"КвПромежуточнаяСумма" (QtrSubtotal) — итоговая сумма за квартал, которая вычисляется с помощью выражения:
=Sum([ПромежуточнаяСумма]) (или =Sum([Subtotal])).
Группировка записей выполняется по году и по кварталу. Обратите внимание, что раздел
Область данных
(Detail) отчета пуст, т. е. в отчет выводятся только итоговые значения (рис. 10.4).
Детальные данные, т. е. все записи из базового запроса, включены в главный отчет.
Отчет "Подчиненный для продаж по годам" в режиме Конструктора
CompEbook.ru Железо, дизайн, обучение и другие
Базовый запрос для отчета "Квартальные обороты клиентов"
Теперь создадим отчет на основе только что созданного запроса. Для этого можно воспользоваться Мастером отчетов (Report Wizard)
(см. гл. 6).
Выберите для помещения в отчет поля "Название" (CompanyName), "Город" (City) и "Страна" (Country), отсортируйте записи по полю "Название", выберите тип отчета
в столбец
(Column) и один из предложенных стилей отчета. В результате вы получите отчет примерно такой, как изображен на рис. 10.6.
Для того чтобы построить подчиненный отчет, содержащий квартальные обороты клиентов, воспользуемся уже готовым запросом "Квартальные обороты по товарам" (Quarterly Orders by Product), слегка его изменив. А именно, нужно удалить из запроса последнее поле "ДатаРазмещения" (OrderDate), для которого задано лишнее в нашем примере условие выборки. Тогда запрос будет выглядеть так, как это представлено на рис. 10.7. Сохраните его с именем "Квартальные обороты клиентов по товарам".
Базовый запрос для подчиненного отчета
Чтобы создать подчиненный отчет, воспользуемся Мастером подчиненных отчетов. Для этого:
Откройте отчет "Квартальные обороты клиентов" в режиме Конструктора.
Выберите элемент управления
Подчиненная форма/отчет
(Subform/Subreport) на
Панели элементов
(Toolbox) и разместите его в разделе
Область данных
(Detail) ниже полей, содержащих данные о клиенте. (Кнопка
Мастера
(Control Wizards) должна быть нажата.)
В первом диалоговом окне мастера выберите переключатель
Имеющиеся таблицы и запросы
(Use existing Tables and Queries) (рис. 10.8). Нажмите кнопку Далее (Next).
Первое диалоговое окно Мастера подчиненных отчетов
Во втором диалоговом окне мастера нужно определить, какие поля требуется включить в подчиненный отчет (рис. 10.9). При этом поля в общем случае можно выбирать из нескольких таблиц и запросов. В данном случае нужно в раскрывающемся списке
Таблицы и запросы
(Tables/Query) выбрать запрос "Квартальные обороты клиентов по товарам" (который был создан для этого примера), затем нажать кнопку с двумя стрелками, чтобы включить в подчиненный отчет все поля запроса, и нажать кнопку
Далее
(Next).
Второе диалоговое окно Мастера подчиненных отчетов
В следующем диалоговом окне необходимо определить связь между главным и подчиненными отчетами, если эта связь существует, или выбрать из списка значение
Отсутствует
(None), если вы создаете независимый подчиненный отчет. В данном случае можно выбрать связь в отображенном списке, но нам предложенные варианты не подходят, поэтому выберите переключатель
Самостоятельное определение
(Define my own), чтобы задать эту связь самостоятельно.
Замечание
Так как поля в связываемых источниках данных имеют одинаковые типы и названия, Мастер подчиненных отчетов может автоматически связать отчеты по этим полям. Именно такие связи будут предложены в списке возможных вариантов связывания отчетов.
В диалоговом окне появятся две пары раскрывающихся списков (рис. 10.10). В списках слева отображаются поля главного отчета, а в списках справа — связанные с ними поля подчиненного отчета. Связь может быть задана по одному или нескольким полям. В данном случае требуется задать связь по двум полям, чтобы связать источники данных главного и подчиненного отчетов одновременно и по коду клиента, и по отчетному году. Для этого выберите в раскрывающихся списках слева элементы: "КодКлиента" (CustomerlD) и "ГодЗаказа" (OrderYear), и соответствующие (с такими же именами) элементы в раскрывающихся списках напротив (справа), как представлено на рис. 10.10.
Третье диалоговое окно Мастера подчиненных отчетов
Замечание
То, что между главным и подчиненным отчетами установлена связь по полям "КодКлиента" и "ГодЗаказа", означает, что каждая запись в главном отчете будет связана с набором записей из подчиненного отчета, имеющих соответствующие значения в связанных полях. А поскольку значение года заказа в главном отчете определяется значением параметра запроса [Отчетный год:], то в связанном описанным способом подчиненном отчете будут отобраны только те записи, у которых значение поля "ГодЗаказа" совпадет со значением такого же поля в главном отчете, а значит, и с заданным пользователем значением параметра [Отчетный год: ].
Определив связь между отчетами, нажмите кнопку
Далее
(Next).
В последнем диалоговом окне требуется задать имя подчиненного отчета. Вы можете оставить предложенное мастером название "Подчиненный отчет Квартальные обороты клиентов по товарам" и нажать кнопку
Готово
(Finish).
В результате в главном отчете будет создано поле
Подчиненный отчет
(Subreport). Вам потребуется только настроить высоту и ширину этого поля, удалить его метку, и вы получите отчет, аналогичный изображенному на рис. 10.11.
Отчет "Квартальные обороты клиентов"
В качестве подчиненного отчета может быть использована и форма. Например, отчет, аналогичный только что описанному, можно получить с помощью уже имеющейся в базе данных "Борей" формы "Подчиненная для квартальных оборотов" (Quarterly Orders Subform).
Для этого:
Скопируйте данную форму, задав ей новое имя "Подчиненная для квартальных оборотов 2".
Откройте полученную копию в режиме Конструктора и замените источник данных на запрос "Квартальные обороты клиентов по товарам", созданный для предыдущего примера. Сохраните и закройте форму.
Замечание
Источник данных для формы доступен в режиме Конструктора в окне свойств формы как свойство Источник записей (Record Source) на вкладке Данные (Data).
Затем скопируйте созданный ранее отчет "Квартальные обороты клиентов", задав ему новое имя "Квартальные обороты клиентов 2", и откройте его в режиме Конструктора.
Удалите подчиненный отчет и вместо него просто перенесите с помощью мыши созданную форму из окна базы данных в область данных отчета. Автоматически будет создан элемент
Подчиненная форма/отчет
(Subform/Subreport) с именем "Подчиненная для квартальных оборотов".
Теперь настройте высоту и ширину созданного элемента и в свойствах подчиненной формы на вкладке
Данные
(Data) определите связующие поля. Для этого нажмите кнопку Построителя справа от свойства
Подчиненные поля
(Link Child Fields) и задайте в появившемся диалоговом окне две пары одноименных связанных полей: "КодКлиента" (CustomerlD) и "ГодЗаказа" (OrderDate), таким же образом, как это было описано в предыдущем примере. Указанные поля запишутся в качестве значений свойств
Подчиненные поля
(Link Child Fields) и
Основные поля
(Link Master Fields).
В результате вы получите отчет, аналогичный представленному на рис. 10.12.
Для полноты картины добавим в главный отчет два вычисляемых поля, которые будут показывать, на какую сумму клиент купил товара за год. Оба поля представляют собой текстовые поля, у каждого из которых свойство
Составной отчет, включающий подчиненную форму
CompEbook.ru Железо, дизайн, обучение и другие
Статический перекрестный отчет
Более сложной задачей является создание
динамического
перекрестного отчета. Это такой отчет, у которого в качестве источника данных задан перекрестный запрос, и формирующийся этим запросом набор записей может иметь разное количество и разные заголовки столбцов, в зависимости от выполнения некоторых условий. Пример такого запроса приведен на рис. 10.14. Этот отчет показывает выработку сотрудников фирмы по месяцам года. Такой отчет может создаваться ежеквартально, и каждый раз число столбцов в нем будет увеличиваться, пока в конце года не станет равным 14 (12 месяцев плюс итоговый столбец и столбец с фамилиями сотрудников).
Еще одним примером динамического перекрестного отчета является отчет, в котором показывается, какие товары вы заказали поставщикам, но еще не получили. Такой отчет может представлять собой таблицу, в которой по вертикали отображаются товары, а по горизонтали — поставщики, которым эти товары заказаны. Его можно построить, если число поставщиков ограничено и не очень велико.
Динамический перекрестный отчет
Для создания динамических отчетов не обойтись без программирования. В режиме Конструктора отчет о выработке сотрудников выглядит так, как это представлено на рис. 10.15, т. е. он содержит несвязанные с данными надписи и текстовые поля.
Привязка их к результатам запроса выполняется программным путем в процессе выполнения отчета. Позже мы покажем, как создать программы для такого отчета
(см. разд. "Создание перекрестного отчета с изменяющимся числом столбцов" гл. 13).
Динамический перекрестный отчет в режиме Конструктора
CompEbook.ru Железо, дизайн, обучение и другие
Диалоговое окно Параметры страницы
CompEbook.ru Железо, дизайн, обучение и другие
Базовый запрос для отчета "Заказы клиентов"
Включите в запрос следующие поля:
из таблицы "Клиенты" (Customers): "Название" (CompanyName), "Страна" (Country), "Город" (City);
из таблицы "Заказы" (Orders): "КодЗаказа" (OrderlD), "ДатаРазмещения" (OrderDate), "ДатаНазначения" (RequiredDate), "ДатаИсполнения" (ShippedDate);
из запроса "Сведения о заказах" (Order Details Extended): "Марка" (ProductName), "Цена" (UnitPrice), "Количество" (Quantity), "Скидка" (Discount), "ОтпускнаяЦена" (ExtendedPrice).
Ограничим количество записей в выборке, введя для поля "ДатаРазмещения" (OrderDate) в строку
Условие отбора
(Criteria) Between #01.(H.98# And #31.12 . 98#. Сохраните созданный запрос с именем Заказы клиентов.
Теперь создайте новый отчет на базе запроса "Заказы клиентов". Для этого выделите этот запрос в списке запросов окна базы данных и нажмите на стрелку на кнопке
Новый объект
(New Object) на панели инструментов. Выберите из раскрывающегося списка значение
Отчет
(Report) и затем в диалоговом окне
Новый отчет
(New Report) выберите значение
Конструктор
(Design View).
CompEbook.ru Железо, дизайн, обучение и другие
Структура отчета и условия сортировки и группировки
Для числового поля "КодЗаказа" (OrderID) мы оставляем значение свойства
Группировка
(Group On) равным
По полному значению
(Each Value), как это установлено по умолчанию. Значение свойства
Интервал
(Group Interval) останется равным I, т. к. нам нужно создать отдельную группу для каждого заказа. Однако свойство
Группировка
(Group On) позволяет группировать числовые данные и по диапазонам значений. В этом случае нужно установить значение данного свойства равным
Интервал
(Interval), а значение свойства
Интервал
(Group Interval) равным числу, которое определяет диапазон группировки. Например, если числовое поле содержит возраст человека, то значение 5 свойства
Интервал
(Group Interval) будет определять диапазоны возрастов от 0 до 4, от 5 до 9, от 10 до 14 и т. д.
Для поля типа Дата/время свойство
Группировка
(Group On) кроме значения по умолчанию
По полному значению
(Each Value) может принимать следующие значения:
По годам
(Year),
По кварталам
(Qtr),
По месяцам
(Month),
По неделям
(Week),
По дням
(Day),
По часам
(Hour),
По минутам
(Minute). Значение свойства
Интервал
(Group Interval) определяет число выбранных единиц измерения в диапазоне. Например, если значение свойства
Группировка
(Group On) установлено равным
По месяцам, а Интервал
(Group Interval) имеет значение 1, то данные будут группирот ваться помесячно.
Свойство
Не разрывать
(Keep Together) позволяет управлять тем, как группа будет, размещаться на странице. Если группы небольшие, то лучше всего установить значение этого свойства равным
Вся группа
(Whole Group). Тогда при печати отчета вся группа, т. е. заголовок группы, все записи из области данных и примечание группы будут размещаться на одной странице (если вся группа не помещается на текущую страницу, она продолжится со следующей страницы), а каждая следующая группа будет начинаться с новой страницы (если только она не помещается целиком на текущей странице). Если группа может содержать столько данных, что они не поместятся на одной странице, то лучше установить значение этого свойства равным
С первыми данными
(With First Detail). В этом случае заголовок группы размещается на текущей странице, только если на ней помещается также первая строка данных, в противном случае вся группа переносится на следующую страницу.
Свойство
Не разрывать
(Keep Together) для группы связано с аналогичным свойством раздела отчета. Эти свойства должны быть согласованы. Например, если в группе установлено значение свойства
Не разрывать
(Keep Together) равным
Вся группа
(Whole Group), то значения свойства
Не разрывать
(Keep Together) соответствующих разделов отчета должны иметь значения равными Да (Yes). В противном случае приоритет имеет свойство, заданное в окне
Сортировка и группировка
(Sorting and Grouping).
Сам отчет также имеет свойство
Неразрывная группа
(Grp Keep Together), которое применяется для отчетов, печатающихся в несколько колонок. Оно действует только для групп, у которых значение свойства
Не разрывать
(Keep Together) установлено равным
Вся группа
(Whole Group) или
С первыми данными
(With First Detail). Если же установлено значение Нет (No), то свойство
Неразрывная группа
(Grp Keep Together) игнорируется. Данное свойство отчета может иметь значения
На странице
(Per Page) и
В столбце
(Per Column). В первом случае группа печатается без разрыва на странице, а во втором — без разрыва в колонке (полосе) отчета.
Замечание
В ячейки
Поле/выражение
(Field/Expression) окна
Сортировка и группировка
(Sorting and Grouping) можно вводить не только названия полей, но и выражения, т. е. группировка может выполняться по значению выражения. Ввод выражения начинается со знака равенства (=) так же, как в свойствах форм и отчетов.
CompEbook.ru Железо, дизайн, обучение и другие
Отчет "Заказы клиента" в режиме Конструктора
Измените стандартное значение свойства
Тип границы
(Border Style) для надписей и текстовых полей, присвоив им значение
Сплошная
(Solid), т. к. все остальные надписи и поля будут окружены рамками.
Далее перетащите в область данных следующие поля: "Марка" (ProductName), "Цена" (UnitPrice), "Количество" (Quantity), "Скидка" (Discount) и "ОтпускнаяЦена" (ExtendedPrice). Теперь нужно переместить присоединенные к ним надписи в область заголовка группы "КодЗаказа". Для этого выделите все присоединенные надписи для этих полей и удалите их в буфер обмена (например, с помощью комбинации клавиш <Ctrl>+<X>). Затем выделите раздел
Заголовок группы 'КодЗаказа'
и вставьте в него надписи из буфера обмена (комбинация клавиш <Ctrl>+<V>). Расположите эти надписи под прямоугольником так, чтобы нижняя граница прямоугольника и верхняя граница рамки надписей совпали. Измените для всех надписей свойство шрифта
Насыщенность
(Font Weight) на
жирный
(Bold) и распределите надписи по ширине отчета так, как это показано на рис. 10.20. Рамки надписей должны плотно примыкать друг к другу. Подтяните нижний край раздела заголовка группы так, чтобы он совпал с нижней границей надписей.
Разместите поля в области данных так, чтобы они располагались вдоль верхнего края раздела точно под соответствующими надписями. Рамки полей так же, как и рамки надписей, должны плотно примыкать друг к другу. Подтяните нижний край области данных так, чтобы он совпал с нижней границей полей.
Отчет "Заказы клиентов" в режиме Предварительного просмотра
В разделе примечания группы "КодЗаказа" (OrderlD Footer) разместите два вычисляемых текстовых поля. Одно из них будет содержать текст, который определяется выражением:
="Итого по заказу "
&
[КодЗаказа] & ":"
(для английской версии используйте имя поля OrderID). Второе поле должно содержать итоговую сумму заказа и вычисляется с помощью выражения:
=Sum([ОтпускнаяЦена])
(для английской версии используйте имя поля ExtendedPrice). У этих полей в нашем примере отсутствуют присоединенные надписи (первое поле играет роль надписи для второго). Поэтому перед их размещением можно соответствующим образом изменить стандартное свойство
Добавление подписи
(Auto Label) для текстового поля. Эти поля также имеют рамки, ширина которых должна быть установлена так, чтобы они примыкали друг к другу у левой границы поля "ОтпускнаяЦена", находящегося справа в области данных.
В разделе примечания группы "Название" (CompanyName Footer) выведем итоги продаж по клиенту в целом (по всем его заказам). Эти поля формируются аналогично итоговым полям в группе "КодЗаказа", т. е. вычисляются согласно выражениям:
для первого поля: ="Итого по клиенту " & [Марка] & ":"
для второго поля: =Sum([ОтпускнаяЦена])
(для английской версии используйте имена полей ProductName и ExtendedPrice соответственно). Чтобы выделить значения этих полей, увеличьте размер шрифта до 11 пунктов и установите насыщенность шрифта —
жирный
(Bold).
Для созданных полей с итоговыми суммами задайте для свойства
Формат поля
(Format) значение
Денежный
(Currency).
В итоге вы должны получить отчет, который будет выглядеть при печати так, как представлено на рис. 10.21.
CompEbook.ru Железо, дизайн, обучение и другие
Помещение группирующего поля в область данных отчета
Если в отчете используется группировка, то не всегда группирующее поле выносится в отдельный раздел (заголовок или примечание группы). Иногда его оставляют в области данных отчета, вынося итоговые значения в раздел примечания группы. В этом случае также используется свойство
Не выводить повторы
(Hide Dublicates), для группирующего поля, чтобы не выводить повторяющиеся значения. Отчет при этом будет выглядеть так, как это показано на рис. 10.24.
CompEbook.ru Железо, дизайн, обучение и другие
Использование полей с накоплением суммы
Для того чтобы текстовое поле накапливало данные из предыдущих записей, нужно установить для него значение свойства
Сумма с накоплением
(Running Sum) равным либо значению
Для группы
(Over Group), либо Для всего (Over All).
Значение
Для группы
(Over Group) используется, когда нужно просуммировать значение в пределах одной группы, в следующей группе это значение сбрасывается в О и суммирование начинается вновь.
Значение
Для всего
(Over All) используется либо в том случае, когда в отчете нет группировки, либо когда нужно получить итоговое значение по всем группам.
Такое накапливающее поле может быть размещено либо в области данных, и тогда оно будет суммировать данные в каждой записи, либо в области заголовка или примечания группы, тогда оно будет накапливать итоговые значения для всех групп одного уровня. Пример отчета, содержащего поля с накоплением, приведен на рис. 10.25. Первое вычисляемое поле в этом отчете содержит выражение: = [ОтпускнаяЦена] (для английской версии = [ExtendedPrice]) и имеет свойство
Сумма с накоплением
(Running Sum), равное значению
Для группы
(Over Group), второе содержит такое же выражение, но имеет значение данного свойства, равное значению
Для всего
(Over All).
CompEbook.ru Железо, дизайн, обучение и другие
Подсчет числа записей в группе
Если требуется подсчитать общее количество записей в отчете, то тогда просто создается одно вычисляемое поле в разделе заголовка или примечания отчета, у которого свойство
Данные
(Control Source) устанавливается равным выражению =Count(*).
CompEbook.ru Железо, дизайн, обучение и другие
Диалоговое окно для ввода критериев отбора записей
Совет
Если захотите поэкспериментировать с формой "Продажи по годам", советуем вам временно изменить свойство Модальное (Modal) этой формы на Нет (No), чтобы иметь возможность, в то время как форма открыта, переключиться на отчет, содержащий вычисляемые поля.
CompEbook.ru Железо, дизайн, обучение и другие
Свойства Фильтр и Фильтр включен в окне свойств отчета
CompEbook.ru Железо, дизайн, обучение и другие
Скрытие повторяющихся данных в отчете
При составлении простых отчетов, содержащих отсортированные данные, могут возникать ситуации, когда в нескольких строках отчета в одном поле повторяются одни и те же данные, например наименование клиента, адрес и пр. Очевидно, что отчет будет выглядеть лучше, если данные в поле не будут повторяться, а будут печататься каждый раз только в первой строке. Каждое поле в области данных отчета имеет свойство
Не выводить повторы
(Hide Dublicates). Если установить это свойство равным Да (Yes) для поля, содержащего повторяющиеся данные, данные повторяться не будут. Для сравнения на рис. 10.22 и 10.23 приводится один и тот же отчет с разными значениями свойства
Не выводить повторы
(Hide Dublicates) для поля "Название" (CompanyName).
Сортировка и группировка данных
В отличие от форм отчеты содержат, как правило, большое количество записей, которые для удобства просмотра и анализа должны быть отсортированы и объединены в группы. Рассмотрим возможности, которые предоставляет Access 2002 для группировки и сортировки записей в отчетах. Для этого создадим отчет "Заказы клиентов", в котором сгруппируем все заказы по клиентам.
CompEbook.ru Железо, дизайн, обучение и другие
Создание базового запроса "Заказы клиентов"
Для этого сначала создадим базовый запрос. Объединим в этом запросе таблицы "Клиенты" (Customers) и "Заказы" (Orders) и запрос "Сведения о заказах" (Order Details Extended), как это показано на рис. 10.18.
Создание подчиненных отчетов
Подчиненные отчеты могут создаваться двумя способами:
с помощью Мастера подчиненной формы/отчета для создания нового подчиненного отчета в существующем отчете;
путем внедрения уже существующего отчета (или формы) в другой отчет (в этом случае внедряемый отчет становится подчиненным).
Для того чтобы продемонстрировать, как создаются составные отчеты, мы построим отчет, показывающий квартальные обороты клиентов. Сначала нужно создать запрос, который будет базовым для главного отчета. Этот запрос будет иметь параметр [Отчетный год:] и выбираться будут все клиенты, которые имеют заказы в указанном году (рис. 10.5).
Этот запрос очень просто создать на основе имеющегося в базе данных "Борей" подобного запроса "Квартальные обороты" (Quarterly Orders):
Выделите названный запрос в окне базы данных и скопируйте его, используя команды копирования и вставки
Правка, Копировать
(Edit, Copy) и
Правка, Вставить
(Edit, Paste). Дайте ему название "Квартальные обороты клиентов".
Откройте его в режиме Конструктора.
Удалите последнее поле "ДатаРазмещения" (OrderDate) из таблицы полей внизу окна запроса и вместо него вставьте вычисляемое поле: ГодЗаказа: Year ("[ДатаРазмещения]) (для английской версии используйте английское имя поля OrderDate, в качестве имени вычисляемого поля можете задать OrderYear), а в строку
Условие отбора
(Criteria) введите имя параметра запроса [Отчетный год: ]. Сохраните и закройте запрос.
Создание полей, содержащих сумму с накоплением
Часто в отчетах требуется создать поле, в котором суммируются значения из всех предыдущих записей. Такое поле представляет собой специальный вид итогового значения.
Создание составных отчетов
Под составными отчетами в данном случае понимаются отчеты, имеющие сложную структуру: включающие подчиненные отчеты, построенные на базе перекрестных запросов, выводимые на печать в виде нескольких колонок, как это делается, например, в газетах. Ниже будут описаны такие отчеты, их назначение и способы создания.
CompEbook.ru Железо, дизайн, обучение и другие
Вычисление процентов
Иногда в отчете требуется вывести проценты, которые составляют конкретные значения в общей или частичной итоговой сумме. В отчетах Access можно создавать такие поля. Для этого в области данных создается вычисляемое поле, значение которого определяется выражением типа = [Стоимость ]/ [Общая стоимость], т.е. нужно меньшее значение разделить на большее. Свойство
Формат
(Format) для этого поля нужно установить равным
Процентный
(Percent).
Если требуется вычислить процент частичной (групповой) суммы от общей суммы или суммы более высокого уровня группировки, нужно создать аналогичное поле в разделе заголовка или примечания соответствующей группы.
Access обеспечивает возможность таких расчетов за счет того, что отчет формируется в два этапа. На первом этапе вычисляются поля в области данных, итоговые значения для групп и для отчета в целом и определяется длина отчета. Во время второго этапа вычисляются все выражения, которые ссылаются на итоги, рассчитанные во время первого этапа.
Пример использования полей, содержащих процент от итогового значения, вы можете найти в отчете "Продажи по сотрудникам и странам" (Employee Sales by Country) базы данных "Борей" (Northwind).
CompEbook.ru Железо, дизайн, обучение и другие
В данной главе мы привели
В данной главе мы привели много примеров отчетов (и уже готовых, и тех, которые вы могли создать сами в процессе изучения материала), которые должны были убедить вас, что Access является не только СУБД, которая будет надежно хранить ваши данные, но и средством, которое позволяет легко и быстро обрабатывать и представлять эти данные в удобном для вас виде. При этом такие сложные обработки данных практически не требуют умения программировать, все можно сделать пользуясь элементарными навыками проведения вычислений, несколькими достаточно простыми правилами работы с элементами управления в Конструкторе и будучи просто аккуратным. Новые средства публикации отчетов в Интернете
(см. гл. 12)
значительно увеличивают ценность этого инструмента, т. к. делают отчеты доступными широкому кругу пользователей, на чьих компьютерах Access не установлен. Мы надеемся, что материалы этой и всех предыдущих глав показали, что Access является прекрасным средством разработки, доступным вдумчивому и упорному пользователю.
CompEbook.ru Железо, дизайн, обучение и другие
