Часто в практике разработки на 1С возникает задача: вывести данные из базы в виде матрицы (кросс-таблицы), где строки — это номенклатура, а колонки — динамически меняющиеся данные, например, склады. При этом количество складов и товаров большое и периодически меняется. Разберем по шагам, как правильно подойти к решению этой задачи, какие инструменты платформы использовать и почему написать такой вывод только лишь одним запросом не получится.
Давайте проанализируем ситуацию. В классическом SQL существует оператор PIVOT, который позволяет трансформировать строки в столбцы. Однако в языке запросов платформы 1С:Предприятие подобных встроенных механизмов для создания кросс-таблиц напрямую на уровне СУБД нет.
Важно понимать: Структура полей (колонок) результата запроса в 1С должна быть жестко задана на этапе его написания. Если количество складов заранее неизвестно и вычисляется только в момент выполнения, запрос может вернуть только «плоскую» таблицу. Выглядеть она будет стандартно:
Разворот этих плоских данных по горизонтали (транспонирование) — это всегда задача подсистемы вывода. Рассмотрим подробнее, какие инструменты для этого предлагает платформа.
Как справедливо заметили участники обсуждения, не нужно изобретать велосипед. Лучше всего для такой задачи использовать отчет на СКД. Это самый быстрый и оптимальный способ построить кросс-таблицу.
Посмотрим на пример настройки СКД для вывода складов в колонки:
РегистрНакопления.ОстаткиТоваров.Остатки, получаем поля Номенклатура, Склад и КоличествоОстаток.Номенклатура.Склад.КоличествоОстаток.Если конечная цель — получить данные в Excel, то после формирования отчета в режиме предприятия результат легко выгружается в файл стандартной кнопкой сохранения. Чтобы файл Excel выглядел аккуратно и не содержал лишнего «мусора» от 1С (что особенно важно для пользователей старой закалки), перед выгрузкой в настройках СКД мы рекомендуем отключить вывод общих итогов, заголовков и параметров формирования отчета.
Выясним, как решить задачу, если использование СКД по каким-то причинам недоступно или вы хотите реализовать сложную алгоритмическую обработку перед выводом. Мы можем сделать это программно через ТаблицаЗначений.
Разберем по шагам классический программный подход:
ТаблицаЗначений. Сначала добавляем в нее колонку «Номенклатура», а затем в цикле добавляем отдельные колонки под каждый найденный уникальный склад.Полученную таким образом матрицу можно вывести в ТабличныйДокумент и программно сохранить.
Проанализируем еще одну альтернативу. В платформе 1С есть более старый, но до сих пор отлично работающий инструмент — ПостроительОтчета. Он идеально заточен под простые программные задачи вывода кросс-таблиц, когда нужно быстро раскидать измерения по строкам и колонкам без создания полноценной схемы СКД.
Настроить его программно для вывода динамических складов по горизонтали занимает буквально несколько строк кода. Мы задаем текст запроса, указываем измерения для строк и колонок, и построитель сам формирует макет и выводит результат в табличный документ.
Рассмотрим ситуацию, когда нашу кросс-таблицу со складами нужно не просто показать пользователю, но и автоматически сохранять в Excel (например, для регламентной рассылки). Программно это делается простым методом:
ТабличныйДокумент.Записать("C:\Temp\ОстаткиПоСкладам.xlsx", ТипФайлаТабличногоДокумента.XLSX);
Важный нюанс из практики: Если номенклатуры и складов очень много, формируется огромная матрица. Стандартный метод сохранения платформы 1С в формат XLSX при превышении определенного лимита ячеек (или более 65535 строк) может потреблять слишком много ресурсов, зависать или падать с ошибкой нехватки памяти.
В таких случаях разработчики применяют хитрость:
ТабличныйДокумент в легковесный формат HTML4.Excel.Application скрыто открывают этот HTML-файл.Этот обходной путь работает значительно быстрее на огромных объемах данных и не приводит к падениям рабочих процессов сервера 1С.