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