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