При разработке отчетов на системе компоновки данных (СКД) программисты часто сталкиваются со специфической проблемой при выводе данных в кросс-таблицу. Если на пересечении строки и колонки оказывается несколько различных записей, СКД по умолчанию пытается их сагрегировать (например, выбирая максимум или минимум) либо выводит детальные записи, что полностью ломает и раздувает структуру отчета. Типичный пример — вывод графика поставок, где один и тот же контрагент в один и тот же день может иметь несколько разных временных интервалов доставки.
В этой статье мы подробно разберем, почему возникает этот эффект, и рассмотрим два профессиональных метода решения этой задачи: классический (с помощью нумерации строк внутри группировок в запросе) и современный (через агрегацию строк в ресурсах СКД).
Проанализируем стандартное поведение кросс-таблицы. Представим, что у нас есть независимый регистр сведений со следующими измерениями: Номенклатура (СправочникСсылка), ДеньПоставки (ПеречислениеСсылка) и ВремяПоставки (ПеречислениеСсылка). Нам необходимо получить таблицу, где строками будет выступать номенклатура, колонками — дни недели, а в ячейках на пересечении должно отображаться время.
Если для одного товара на один день приходится две разные поставки (например, «Товар 1» в «День 2» должен приехать в 16:00 и в 17:00), то при обычной настройке таблицы СКД предложит нам сделать поле ВремяПоставки ресурсом. Если мы укажем выражение ресурса Максимум(ВремяПоставки), система выведет только одну запись (17:00), а запись 16:00 будет безвозвратно утеряна (схлопнута). Если же мы попытаемся выводить детальные записи вместо ресурсов, отчет «разъедется» — под каждую запись будет создаваться отдельная строка в таблице, что сделает отчет нечитаемым.
Для решения этой проблемы нам нужно либо искусственно разделить строки внутри группировки, либо объединить несколько значений внутри одной ячейки. Рассмотрим оба подхода по шагам.
Этот метод основан на классическом приеме реляционных баз данных. Наша задача — пронумеровать строки по порядку для каждого уникального сочетания полей Номенклатура и ДеньПоставки. В результате «Товар 1» в «День 2» со временем 16:00 получит номер строки 1, а со временем 17:00 — номер строки 2. Благодаря этому СКД сможет сопоставить их и вывести на разных строках внутри одной группировки номенклатуры.
Выполним нумерацию строк с помощью внутреннего соединения таблицы самой с собой. Разберем структуру запроса:
ВЫБРАТЬ
Т1.Номенклатура КАК Номенклатура,
Т1.ДеньПоставки КАК ДеньПоставки,
Т1.ВремяПоставки КАК ВремяПоставки,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.ВремяПоставки) КАК НомерСтроки
ИЗ
РегистрСведений.Поставки КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.Поставки КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура
И Т1.ДеньПоставки = Т2.ДеньПоставки
И Т1.ВремяПоставки >= Т2.ВремяПоставки
ГРУППИРОВАТЬ ПО
Т1.Номенклатура,
Т1.ДеньПоставки,
Т1.ВремяПоставки
Рассмотрим подробнее, как работает это соединение:
Номенклатура и ДеньПоставки.Т1.ВремяПоставки >= Т2.ВремяПоставки позволяет нам для каждого конкретного времени посчитать, сколько записей в этой же группе имеют время, меньшее или равное текущему.КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.ВремяПоставки) превращает это сопоставление в порядковый номер (1, 2, 3 и т.д.).Важное предупреждение по производительности: Использование неявного соединения с условием «больше или равно» (>=) на реальных таблицах с большим объемом данных (десятки тысяч записей и более) может привести к критическому падению скорости выполнения запроса на СУБД. Если ваш регистр содержит много записей, сначала отфильтруйте данные во временную таблицу по нужным параметрам отчета, проиндексируйте ее и только затем выполняйте самосоединение.
Проанализируем пример оптимизированного запроса с использованием временных таблиц:
ВЫБРАТЬ ПЕРВЫЕ 0
ИсходныеДанные.Номенклатура КАК Номенклатура,
ИсходныеДанные.ДеньПоставки КАК ДеньПоставки,
ИсходныеДанные.ВремяПоставки КАК ВремяПоставки
ПОМЕСТИТЬ ВТ_Данные
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Номенклатура КАК Номенклатура,
Т1.ДеньПоставки КАК ДеньПоставки,
Т1.ВремяПоставки КАК ВремяПоставки,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.ВремяПоставки) КАК НомерСтроки
ИЗ
ВТ_Данные КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Данные КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура
И Т1.ДеньПоставки = Т2.ДеньПоставки
И Т1.ВремяПоставки >= Т2.ВремяПоставки
ГРУППИРОВАТЬ ПО
Т1.Номенклатура,
Т1.ДеньПоставки,
Т1.ВремяПоставки
После того как запрос подготовлен, перейдем к настройке схемы компоновки данных:
ВремяПоставки. В качестве выражения укажем Максимум(ВремяПоставки).Номенклатура, а внутрь нее (вложенным элементом) добавим группировку по полю НомерСтроки.ДеньПоставки.ВремяПоставки.При таком подходе СКД выведет строки аккуратно: под каждой номенклатурой появится столько пронумерованных строк, сколько максимум поставок было в самый загруженный день. В те дни, когда поставок меньше, ячейки нижних строк просто останутся пустыми, а структура кросс-таблицы при этом полностью сохранится.
Что делать, если плодить физические строки внутри номенклатуры не хочется, а нужно просто красиво показать все доступные значения времени в одной ячейке друг под другом? В современной платформе 1С предприятия 8.3 для этого предусмотрен элегантный встроенный механизм агрегации строк, который избавляет разработчика от необходимости писать сложные ресурсоемкие запросы с самосоединениями.
Для реализации этого метода нам достаточно использовать самый простой запрос к регистру без каких-либо соединений:
ВЫБРАТЬ
Поставки.Номенклатура КАК Номенклатура,
Поставки.ДеньПоставки КАК ДеньПоставки,
Поставки.ВремяПоставки КАК ВремяПоставки
ИЗ
РегистрСведений.Поставки КАК Поставки
Вся магия настройки будет происходить непосредственно в конфигураторе СКД:
ВремяПоставки в список ресурсов.СоединитьСтроки(Массив(Различные ВремяПоставки), Символы.ПС)
Номенклатура (без вложенных строк и номеров).ДеньПоставки.ВремяПоставки.Разберем подробнее, как работает выражение ресурса:
Массив() собирает все детальные записи поля ВремяПоставки, попавшие в текущую группировку пересечения строки и колонки, в единый массив компоновки данных. Ключевое слово Различные внутри массива гарантирует, что если в базе по ошибке возникнут полные дубли записей на одно время, они не будут дублироваться в отчете.СоединитьСтроки() берет полученный массив и преобразует его в единую текстовую строку, используя в качестве разделителя системную константу переноса строки Символы.ПС.В результате работы отчета для каждой номенклатуры будет сформирована ровно одна строка. Если в какой-то день у товара будет две или более поставки, ячейка на пересечении этого дня автоматически увеличится по высоте, а значения времени отобразятся друг под другом в столбик. Такое решение работает мгновенно даже на базах объемом в миллионы записей, так как вся вычислительная нагрузка по соединению строк перекладывается на быстрый процессор компоновки данных 1С.