При разработке отчетов в системе компоновки данных (СКД) программисты часто сталкиваются с задачей, когда в рамках одного отчета необходимо вывести несколько независимых списков (например, списки контрагентов по различным категориям) в соседних колонках. Если решать эту задачу классическим объединением (ОБЪЕДИНИТЬ ВСЕ) или левым соединением по не связанным полям, в результате получается так называемая «лестница» из пустых строк (значений NULL). Строки одной категории выводятся первыми, а в колонках других категорий отображаются пустоты, после чего начинаются строки следующей категории.
В этой статье мы подробно разберем, как решить эту проблему на примере реальной задачи для конфигурации «Управление торговлей 11». Нам необходимо классифицировать клиентов за последние 3 месяца по трем категориям и вывести их в параллельные колонки:
Мы рассмотрим три различных метода решения этой задачи: от программного формирования таблицы значений до элегантных решений исключительно средствами языка запросов и настроек СКД.
---
Этот способ является наиболее очевидным, когда нужно гарантировать абсолютно ровное заполнение колонок без пустых строк сверху. Суть метода заключается в том, что мы выполняем пакетный запрос в модуле объекта отчета, выгружаем результаты по каждой категории в отдельные массивы, программно определяем колонку с максимальным количеством строк и формируем результирующую ТаблицуЗначений, которую передаем в СКД как внешний набор данных.
Проанализируем программный код, реализующий этот алгоритм в модуле отчета внутри предопределенной процедуры ПриКомпоновкеРезультата:
Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)
СтандартнаяОбработка = Ложь;
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ РАЗЛИЧНЫЕ
| Партнеры.Наименование КАК Покупатели,
| РеализацияТоваровУслуг.Ссылка КАК КоличествоПокупок,
| Партнеры.Ссылка КАК Ссылка,
| РеализацияТоваровУслуг.Дата КАК Дат
|ПОМЕСТИТЬ РП
|ИЗ
| Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Партнеры КАК Партнеры
| ПО РеализацияТоваровУслуг.Partner = Партнеры.Ссылка
|ГДЕ
| Партнеры.Клиент = ИСТИНА
| И РеализацияТоваровУслуг.Проведен = ИСТИНА
| И РеализацияТоваровУслуг.Дата МЕЖДУ ДОБАВИТЬКДАТЕ(&Дата, МЕСЯЦ, -3) И &Дата
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| РП.Покупатели КАК НеРегулярныеПокупатели,
| КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РП.КоличествоПокупок) КАК КоличествоПокупокН,
| РП.Ссылка КАК Ссылка
|ПОМЕСТИТЬ НеРегПок
|ИЗ
| РП КАК РП
|
|СГРУППИРОВАТЬ ПО
| РП.Покупатели,
| РП.Ссылка
|
|ИМЕЮЩИЕ
| (КОЛИЧЕСТВО(РАЗЛИЧНЫЕ НАЧАЛОПЕРИОДА(РП.Дат, МЕСЯЦ)) = 2
| ИЛИ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ НАЧАЛОПЕРИОДА(РП.Дат, МЕСЯЦ)) = 1)
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| РП.Покупатели КАК РегулярныеПокупатели,
| КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РП.КоличествоПокупок) КАК КоличествоПокупокР,
| РП.Ссылка КАК Ссылка
|ПОМЕСТИТЬ РегПок
|ИЗ
| РП КАК РП
|
|СГРУППИРОВАТЬ ПО
| РП.Покупатели,
| РП.Ссылка
|
|ИМЕЮЩИЕ
| КОЛИЧЕСТВО(РАЗЛИЧНЫЕ НАЧАЛОПЕРИОДА(РП.Дат, МЕСЯЦ)) = 3
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| НеРегПок.НеРегулярныеПокупатели КАК НеРегулярныеПокупатели,
| НеРегПок.КоличествоПокупокН КАК КоличествоПокупокН
|ИЗ
| НеРегПок КАК НеРегПок
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ РАЗЛИЧНЫЕ
| Партнеры.Наименование КАК НеПокупатели
|ИЗ
| Справочник.Партнеры КАК Партнеры
|ГДЕ
| Партнеры.Клиент = ИСТИНА
| И НЕ Партнеры.Ссылка В (ВЫБРАТЬ НеРегПок.Ссылка ИЗ НеРегПок)
| И НЕ Партнеры.Ссылка В (ВЫБРАТЬ РегПок.Ссылка ИЗ РегПок)
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| РегПок.РегулярныеПокупатели КАК РегулярныеПокупатели,
| РегПок.КоличествоПокупокР КАК КоличествоПокупокР
|ИЗ
| РегПок КАК РегПок";
Настройки = КомпоновщикНастроек.ПолучитьНастройки();
ПараметрДата = Настройки.ПараметрыДанных.НайтиЗначениеПараметра(Новый ПараметрКомпоновкиДанных("ДатаОтсчета"));
Если ПараметрДата <> Неопределено И ЗначениеЗаполнено(ПараметрДата.Значение) Тогда
Запрос.УстановитьПараметр("Дата", ПараметрДата.Значение);
Иначе
Запрос.УстановитьПараметр("Дата", ТекущаяДата());
КонецЕсли;
МассивРезультатов = Запрос.ВыполнитьПакет();
// Получаем данные по каждой категории клиентов
ТЗ_НеРегулярные = МассивРезультатов[3].Выгрузить();
ТЗ_НеПокупатели = МассивРезультатов[4].Выгрузить();
ТЗ_Регулярные = МассивРезультатов[5].Выгрузить();
МассивРП = ТЗ_Регулярные.ВыгрузитьКолонку("РегулярныеПокупатели");
МассивРПК = ТЗ_Регулярные.ВыгрузитьКолонку("КоличествоПокупокР");
МассивНР = ТЗ_НеРегулярные.ВыгрузитьКолонку("НеРегулярныеПокупатели");
МассивНРК = ТЗ_НеРегулярные.ВыгрузитьКолонку("КоличествоПокупокН");
МассивНП = ТЗ_НеПокупатели.ВыгрузитьКолонку("НеПокупатели");
// Вычисляем максимальную длину среди всех списков
НаибольшееКолСтрок = Макс(МассивРП.Количество(), Макс(МассивНР.Количество(), МассивНП.Количество()));
// Инициализируем результирующую Таблицу Значений
ТЗ = Новый ТаблицаЗначений;
ТЗ.Колонки.Добавить("РегулярныеПокупатели", Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(150)));
ТЗ.Колонки.Добавить("КоличествоПокупокР", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(10, 0)));
ТЗ.Колонки.Добавить("НеРегулярныеПокупатели", Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(150)));
ТЗ.Колонки.Добавить("КоличествоПокупокН", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(10, 0)));
ТЗ.Колонки.Добавить("НеПокупатели", Новый ОписаниеТипов("Строка", Новый КвалификаторыСтроки(150)));
// Добавляем необходимое количество пустых строк
Для Индекс = 1 По НаибольшееКолСтрок Цикл
ТЗ.Добавить();
КонецЦикла;
// Поколонно загружаем массивы в Таблицу Значений
ТЗ.ЗагрузитьКолонку(МассивРП, "РегулярныеПокупатели");
ТЗ.ЗагрузитьКолонку(МассивРПК, "КоличествоПокупокР");
ТЗ.ЗагрузитьКолонку(МассивНР, "НеРегулярныеПокупатели");
ТЗ.ЗагрузитьКолонку(МассивНРК, "КоличествоПокупокН");
ТЗ.ЗагрузитьКолонку(МассивНП, "НеПокупатели");
// Передаем ТЗ в СКД в качестве внешнего набора данных
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("НаборДанныхТЗ", ТЗ);
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, Настройки, ДанныеРасшифровки);
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных, ДанныеРасшифровки);
ДокументРезультат.Очистить();
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
ПроцессорВывода.УстановитьДокумент(ДокументРезультат);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
КонецПроцедуры
Важное примечание по коду: Обратите внимание на использование функции НАЧАЛОПЕРИОДА(РП.Дат, МЕСЯЦ) в условиях секции ИМЕЮЩИЕ вместо стандартной функции МЕСЯЦ(РП.Дат). Функция МЕСЯЦ() возвращает лишь число от 1 до 12. Если анализируемый период переходит через границу года (например, с декабря по февраль), то уникальных месяцев будет три (12, 1, 2). Но если анализировать более длительные интервалы, свертка по простому номеру месяца без учета года приведет к ошибкам в расчетах. Использование НАЧАЛОПЕРИОДА полностью решает эту проблему.
Для вывода этой таблицы в СКД достаточно создать Набор данных - объект с именем НаборДанныхТЗ, описать в нем соответствующие поля и в настройках структуры добавить обычные «Детальные записи».
---
Если перед вами стоит задача сделать отчет без написания программного кода в модуле формы или модуля отчета (например, при создании дополнительного отчета, который должен работать исключительно на стандартных механизмах БСП), всю логику выравнивания строк можно перенести в сам текст запроса СКД.
Для этого мы воспользуемся механизмом нумерации строк внутри временных таблиц при помощи функции АВТОНОМЕРЗАПИСИ(), а затем свяжем списки через полное соединение по этому номеру строки. Выясним, как будет выглядеть такой запрос:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Партнеры.Наименование КАК Покупатель,
РеализацияТоваровУслуг.Ссылка КАК Документ,
РеализацияТоваровУслуг.Дата КАК ДатаДок
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Партнеры КАК Партнеры
ПО РеализацияТоваровУслуг.Partner = Партнеры.Ссылка
ГДЕ
Партнеры.Клиент = ИСТИНА
И РеализацияТоваровУслуг.Проведен = ИСТИНА
И РеализацияТоваровУслуг.Дата МЕЖДУ ДОБАВИТЬКДАТЕ(&Дата, МЕСЯЦ, -3) И &Дата
;
////////////////////////////////////////////////////////////////////////////////
// 1. Формируем категорию Регулярных покупателей
ВЫБРАТЬ
ВТ_Продажи.Покупатель КАК Покупатель,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ_Продажи.Документ) КАК КоличествоПокупок
ПОМЕСТИТЬ ВТ_Рег_Предварительная
ИЗ
ВТ_Продажи КАК ВТ_Продажи
СГРУППИРОВАТЬ ПО
ВТ_Продажи.Покупатель
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ НАЧАЛОПЕРИОДА(ВТ_Продажи.ДатаДок, МЕСЯЦ)) = 3
;
ВЫБРАТЬ
ВТ.Покупатель КАК Покупатель,
ВТ.КоличествоПокупок КАК КоличествоПокупок,
АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Рег_Нумерованная
ИЗ
ВТ_Рег_Предварительная КАК ВТ
;
////////////////////////////////////////////////////////////////////////////////
// 2. Формируем категорию Нерегулярных покупателей
ВЫБРАТЬ
ВТ_Продажи.Покупатель КАК Покупатель,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ_Продажи.Документ) КАК КоличествоПокупок
ПОМЕСТИТЬ ВТ_НеРег_Предварительная
ИЗ
ВТ_Продажи КАК ВТ_Продажи
СГРУППИРОВАТЬ ПО
ВТ_Продажи.Покупатель
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ НАЧАЛОПЕРИОДА(ВТ_Продажи.ДатаДок, МЕСЯЦ)) < 3
;
ВЫБРАТЬ
ВТ.Покупатель КАК Покупатель,
ВТ.КоличествоПокупок КАК КоличествоПокупок,
АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ВТ_НеРег_Нумерованная
ИЗ
ВТ_НеРег_Предварительная КАК ВТ
;
////////////////////////////////////////////////////////////////////////////////
// 3. Формируем категорию Не Покупателей
ВЫБРАТЬ РАЗЛИЧНЫЕ
Партнеры.Наименование КАК Покупатель
ПОМЕСТИТЬ ВТ_НеПок_Предварительная
ИЗ
Справочник.Партнеры КАК Партнеры
ГДЕ
Партнеры.Клиент = ИСТИНА
И НЕ Партнеры.Ссылка В (ВЫБРАТЬ ВТ.Покупатель ИЗ ВТ_Рег_Предварительная КАК ВТ)
И НЕ Партнеры.Ссылка В (ВЫБРАТЬ ВТ.Покупатель ИЗ ВТ_НеРег_Предварительная КАК ВТ)
;
ВЫБРАТЬ
ВТ.Покупатель КАК Покупатель,
АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ВТ_НеПок_Нумерованная
ИЗ
ВТ_НеПок_Предварительная КАК ВТ
;
////////////////////////////////////////////////////////////////////////////////
// 4. Связываем списки через ПОЛНОЕ СОЕДИНЕНИЕ по Номеру Строки
ВЫБРАТЬ
ЕСТЬNULL(Рег.Покупатель, """") КАК РегулярныеПокупатели,
ЕСТЬNULL(Рег.КоличествоПокупок, 0) КАК КоличествоПокупокР,
ЕСТЬNULL(НеРег.Покупатель, """") КАК НеРегулярныеПокупатели,
ЕСТЬNULL(НеРег.КоличествоПокупок, 0) КАК КоличествоПокупокН,
ЕСТЬNULL(НеПок.Покупатель, """") КАК НеПокупатели
ИЗ
ВТ_Рег_Нумерованная КАК Рег
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_НеРег_Нумерованная КАК НеРег
ПО Рег.НомерСтроки = НеРег.НомерСтроки
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_НеПок_Нумерованная КАК НеПок
ПО ЕСТЬNULL(Рег.НомерСтроки, НеРег.НомерСтроки) = НеПок.НомерСтроки
Преимущества данного метода: Мы получаем готовый плоский результат, полностью подготовленный для вывода. Не нужно писать ни одной строчки кода в конфигураторе, отчет сохраняет полную интерактивность и может быть легко кастомизирован пользователем в режиме «1С:Предприятие».
---
Рассмотрим еще один профессиональный подход. Что делать, если категорий клиентов со временем станет не 3, а 10? Писать 10 полных соединений в запросе крайне неэффективно и трудоемко. Система компоновки данных предоставляет встроенный механизм визуализации плоских списков в виде сводных таблиц (кросс-таблиц).
Для реализации этого метода мы преобразуем наш запрос так, чтобы он возвращал одну общую таблицу с колонками: Покупатель, Категория (строковое представление), КоличествоПокупок и рассчитанный НомерСтроки (внутри своей категории).
Запрос для объединения категорий в одну плоскую таблицу с автонумерацией:
// ... (Предварительные таблицы ВТ_Рег_Нумерованная, ВТ_НеРег_Нумерованная и ВТ_НеПок_Нумерованная получаем аналогично Решению 2)
ВЫБРАТЬ
"Регулярные покупатели" КАК Категория,
Рег.Покупатель КАК Покупатель,
Рег.КоличествоПокупок КАК КоличествоПокупок,
Рег.НомерСтроки КАК НомерСтроки
ИЗ
ВТ_Рег_Нумерованная КАК Рег
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Нерегулярные покупатели",
НеРег.Покупатель,
НеРег.КоличествоПокупок,
НеРег.НомерСтроки
ИЗ
ВТ_НеРег_Нумерованная КАК НеРег
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Не покупатели",
НеПок.Покупатель,
0,
НеПок.НомерСтроки
ИЗ
ВТ_НеПок_Нумерованная КАК НеПок
Для настройки вывода такого запроса в СКД выполним следующие шаги:
Покупатель ресурсом с выражением: Максимум(Покупатель). Поле КоличествоПокупок сделаем ресурсом с выражением: Сумма(КоличествоПокупок). Это необходимо, так как в ячейках кросс-таблицы на пересечении строк и колонок СКД умеет отображать только ресурсы.НомерСтроки (само отображение поля в отчете можно скрыть в настройках вывода).Категория.Покупатель и КоличествоПокупок.Этот изящный способ заставит СКД автоматически спроектировать параллельные колонки по всем имеющимся в результате запроса категориям, а за счет группировки строк по сквозному порядковому номеру записи распределятся плотно друг к другу без каких-либо пустот в интерфейсе отчета.