При разработке отчетов в конфигурациях на платформе «1С:Предприятие» (включая такие классические решения, как «Управление производственным предприятием» или «Комплексная автоматизация») программисты часто сталкиваются с задачей сравнения показателей за несколько разных периодов. Например, бизнес требует вывести в одном отчете средние закупочные цены за текущий месяц и сравнить их со средними ценами за предыдущие полгода, причем в разрезе менеджеров, контрагентов и номенклатуры, да еще и с пересчетом в иностранную валюту (USD).
В этой статье мы подробно разберем, как спроектировать такой отчет. Мы проанализируем как классические подходы на уровне чистых SQL-запросов, так и более современные, гибкие механизмы с использованием Системы компоновки данных (СКД) и типовых механизмов конфигураций 1С.
Представим задачу, которую необходимо решить: сформировать отчет по закупкам, предоставляющий информацию по средним ценам за два интервала одновременно — за предыдущие 6 месяцев (базовый период) и за текущий месяц (актуальный период). Отчет должен содержать следующие ключевые показатели:
Для решения этой задачи нам нужно объединить данные из разных временных интервалов регистра накопления Закупки (или ПриобретениеТоваровУслуг в зависимости от конфигурации) и связать их с регистром сведений КурсыВалют.
Рассмотрим несколько способов реализации этой задачи.
Первая идея, которая приходит в голову начинающему разработчику — написать два независимых запроса к виртуальным таблицам оборотов за разные периоды, а затем объединить их результаты с помощью конструкции ОБЪЕДИНИТЬ ВСЕ, искусственно разделяя колонки через значения NULL.
Проанализируем пример такого запроса, ориентированного на продажи (или закупки):
ВЫБРАТЬ
ОтгрузкаОбороты.Номенклатура КАК Номенклатура,
ОтгрузкаОбороты.Период КАК ЭтотПериод,
НАЧАЛОПЕРИОДА(ОтгрузкаОбороты.Период, ГОД) КАК ЭтотГод,
ОтгрузкаОбороты.СтоимостьОборот КАК ЭтотСуммаОборот,
NULL КАК ПрошлыйПериод,
NULL КАК ПрошлыйГод,
NULL КАК ПрошлыйСуммаОборот
ПОМЕСТИТЬ Выборка
ИЗ
РегистрНакопления.Продажи.Обороты(&Нач, &Кон, Месяц, ) КАК ОтгрузкаОбороты
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПрошлыйОтгрузкаОбороты.Номенклатура,
NULL,
NULL,
NULL,
ДОБАВИТЬКДАТЕ(ПрошлыйОтгрузкаОбороты.Период, ГОД, -1),
НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(ПрошлыйОтгрузкаОбороты.Период, ГОД, -1), ГОД),
ПрошлыйОтгрузкаОбороты.СтоимостьОборот
ИЗ
РегистрНакопления.Продажи.Обороты(ДОБАВИТЬКДАТЕ(&Нач, ГОД, -1), ДОБАВИТЬКДАТЕ(&Кон, ГОД, -1), Месяц, ) КАК ПрошлыйОтгрузкаОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Выборка.Номенклатура КАК Номенклатура,
МАКСИМУМ(Выборка.ЭтотПериод) КАК ЭтотПериод,
МАКСИМУМ(Выборка.ЭтотГод) КАК ЭтотГод,
СУММА(Выборка.ЭтотСуммаОборот) КАК ЭтотСуммаОборот,
МАКСИМУМ(Выборка.ПрошлыйПериод) КАК ПрошлыйПериод,
МАКСИМУМ(Выборка.ПрошлыйГод) КАК ПрошлыйГод,
СУММА(Выборка.ПрошлыйСуммаОборот) КАК ПрошлыйСуммаОборот,
СУММА(ЕСТЬNULL(Выборка.ЭтотСуммаОборот, 0) - ЕСТЬNULL(Выборка.ПрошлыйСуммаОборот, 0)) КАК Разница
ИЗ
Выборка КАК Выборка
ГДЕ
Выборка.Номенклатура = &Номенклатура
СГРУППИРОВАТЬ ПО
Выборка.Номенклатура
УПОРЯДОЧИТЬ ПО
Номенклатура
Преимущества этого подхода: логика запроса линейна и понятна на уровне SQL. Мы явно разделяем потоки данных во временную таблицу, а на втором шаге схлопываем их по ключевым полям (в данном случае по номенклатуре) при помощи агрегатных функций СУММА и МАКСИМУМ.
Недостатки: этот код громоздок и не масштабируем. Если бизнес потребует добавить третий или четвертый период (например, аналогичный период позапрошлого года), нам придется вручную переписывать запрос, плодить новые конструкции NULL КАК ... и усложнять итоговую группировку. Кроме того, расчет средних цен на уровне запроса усложняется из-за потенциального деления на ноль, если в каком-то периоде не было закупок.
Вместо того чтобы жестко разносить данные по разным колонкам с помощью NULL внутри текста запроса, мы можем применить более изящное решение: добавить в подзапросы маркер периода (например, числовое поле или булево значение), а затем свернуть данные. Выясним, как это выглядит на практике:
ВЫБРАТЬ
ЗакупкиОбороты.Номенклатура КАК Номенклатура,
ЗакупкиОбороты.Контрагент КАК Контрагент,
ЗакупкиОбороты.КоличествоОборот КАК Количество,
ЗакупкиОбороты.СтоимостьОборот КАК Стоимость,
1 КАК НомерПериода
ИЗ
РегистрНакопления.Закупки.Обороты(&НачалоТекущегоПериода, &КонецТекущегоПериода, , ) КАК ЗакупкиОбороты
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ЗакупкиОборотыПрошлые.Номенклатура,
ЗакупкиОборотыПрошлые.Контрагент,
ЗакупкиОборотыПрошлые.КоличествоОборот,
ЗакупкиОборотыПрошлые.СтоимостьОборот,
2 КАК НомерПериода
ИЗ
РегистрНакопления.Закупки.Обороты(&НачалоПрошлогоПериода, &КонецПрошлогоПериода, , ) КАК ЗакупкиОборотыПрошлые
В данном случае поле НомерПериода выступает классификатором. Мы передаем в отчет плоскую таблицу, содержащую признак того, к какому интервалу относятся эти данные. Но как красиво и правильно вывести это пользователю? Для этого идеально подходит СКД.
Для реализации динамического вывода колонок в Системе компоновки данных лучше всего отказаться от ручного формирования структуры на уровне запроса. Переложим эту задачу на компоновщик.
Разберем по шагам алгоритм настройки СКД:
НомерПериода или ПериодТекст, где значениями будут "Текущий период" и "Предыдущие 6 месяцев").Менеджер, Номенклатура, Контрагент.НомерПериода (или текстовое наименование периода).Количество, Стоимость, а также рассчитываемое поле СредняяЦена.При такой схеме СКД сама динамически сформирует кросс-таблицу. Она выведет строки с номенклатурой, а по горизонтали автоматически развернет две группы колонок — для первого периода и для второго. Это исключает дублирование кода и упрощает его поддержку в разы.
При реализации подобных отчетов существует несколько неочевидных ловушек, в которые часто попадают разработчики. Проанализируем их подробнее.
Попытка рассчитать среднюю цену в самом запросе (например, делением стоимости на количество внутри подзапроса) или использование функции СРЕДНЕЕ в ресурсах СКД — это грубейшая ошибка. Среднее арифметическое от цен документов не учитывает объемы поставок. Например, если вы купили 1 шт. товара по 200 рублей и 100 шт. по 100 рублей, то среднее арифметическое цен документов составит 150 рублей. Однако реальная средневзвешенная цена закупки равна:
(1 * 200 + 100 * 100) / (1 + 100) = 100.99 руб.
Как делать правильно: расчет средней цены должен выполняться на вкладке «Ресурсы» в СКД. Для ресурса СредняяЦена необходимо прописать следующее выражение расчета итогов:
Выбор Когда Сумма(Количество) = 0 Тогда 0 Иначе Сумма(Стоимость) / Сумма(Количество) Конец
Это гарантирует, что на любом уровне группировки (будь то конкретный менеджер, группа номенклатуры или общие итоги по всему отчету) средняя цена рассчитается математически корректно как средневзвешенная.
Чтобы избавить пользователя от необходимости вручную высчитывать даты начала и окончания прошлого периода (минус 6 месяцев от текущего), мы можем настроить автоматический расчет в параметрах СКД.
Пусть у нас есть основные параметры &НачалоПериода и &КонецПериода, которые пользователь заполняет на форме отчета (например, выбирает текущий месяц). Создадим дополнительные параметры для прошлого периода, например &НачалоПрошлогоПериода и &КонецПрошлогоПериода, установим для них флаг «Ограничение доступности» (чтобы скрыть их от пользователя) и пропишем в поле «Выражение» формулы на встроенном языке СКД:
ДобавитьКДате(&НачалоПериода, "Месяц", -6)ДобавитьКДате(&КонецПериода, "Месяц", -6)Таким образом, система автоматически вычислит границы сравниваемого периода без привлечения кода на встроенном языке в модулях отчета.
Если требуется выводить показатели цен в долларах (USD), у нас есть два пути:
СтоимостьУУ).КурсыВалют. Важно помнить, что курс валюты меняется ежедневно. Поэтому пересчитывать стоимость каждой закупки нужно на дату конкретной операции внутри временных таблиц запроса, и только после этого суммировать полученные валютные показатели. Попытка взять средний курс за месяц приведет к существенным искажениям финансового результата.Поскольку мы использовали кросс-таблицу СКД, колонки периодов формируются динамически. Как в таком случае посчитать разницу между ними? Для этого в СКД предусмотрена мощная функция ВычислитьВыражение.
На вкладке «Рассчитываемые поля» мы можем добавить поле РазницаЦен, а в выражении ресурса для него прописать:
ВычислитьВыражение("СредняяЦена", "НомерПериода", , "Текущий", "Текущий") - ВычислитьВыражение("СредняяЦена", "НомерПериода", , "Предыдущий", "Предыдущий")
Этот подход позволяет СКД на лету вычислять разницы показателей между динамическими колонками отчета.
Если требования бизнеса часто меняются (сегодня нужно сравнить два периода, завтра — разбить закупки по декадам или кварталам), отличным решением будет изучение и внедрение механизмов, заложенных в типовых отчетах УПП. Обратите внимание на такие отчёты, как ДебиторскаяЗадолженностьПоИнтервалам и ДебиторскаяЗадолженностьПоСрокамДолга.
В этих отчетах используется справочник НастройкиИнтервалов. Разработчики УПП реализовали универсальный механизм, когда запрос динамически перестраивается или фильтруется на основании интервалов, заданных пользователем в справочнике. Связав ваш запрос СКД с таблицей интервалов, вы сможете построить гибкий и масштабируемый отчёт, который без изменения программного кода сможет выводить данные по любому количеству периодов сравнения.
Мы рассмотрели различные варианты решения задачи построения отчета по нескольким периодам сразу. Использование простого объединения запросов (Способ 1) подходит для быстрых разовых задач, но для построения сложных аналитических панелей лучшим выбором будет использование кросс-таблиц СКД (Способ 3) в сочетании с правильным расчетом средневзвешенных показателей на уровне ресурсов и автоматическим смещением дат параметров. Подобные приемы делают код чистым, отчет производительным, а интерфейс пользователя — удобным и интерактивным.