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