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