При создании отчетов по движению товаров в СКД часто возникает задача: отобразить только те позиции, по которым были движения (приход или расход) за период, но при этом сохранить в отчете информацию о начальных и конечных остатках для этих позиций. Стандартный подход с фильтрацией в запросе может привести к тому, что строки с остатками просто исчезают из отчета.
Проанализируем типичную ситуацию. У нас есть отчет с колонками "Начальный остаток", "Приход", "Расход", "Конечный остаток". Мы добавляем параметр &ТолькоСОборотами. Когда он включен, отчет должен скрывать товары, которые просто "висели" на остатках без движения. Но вместо этого отчет показывает только приход и расход, а остатки обнуляются. Давайте разберемся, почему так происходит и как это исправить.
Основная причина кроется в том, как устроен запрос для получения данных. Обычно для таких отчетов используется временная таблица, куда с помощью ОБЪЕДИНИТЬ ВСЕ помещаются три набора данных:
Далее из этой временной таблицы выбираются данные с группировкой по номенклатуре и другим разрезам, а числовые поля суммируются. Проблема возникает, когда мы пытаемся отфильтровать данные по оборотам с помощью оператора ГДЕ (WHERE) в основном запросе. Взглянем на ошибочный фрагмент кода:
...
ИЗ
ОстаткиИОбороты КАК ОстаткиИОбороты
ГДЕ
...
И ВЫБОР
КОГДА &ТолькоСОборотами = ИСТИНА
ТОГДА ОстаткиИОбороты.СуммаПоступление > 0 ИЛИ ОстаткиИОбороты.СуммаВыбытие > 0
ИНАЧЕ ИСТИНА
КОНЕЦ
СГРУППИРОВАТЬ ПО
...
Оператор ГДЕ применяется к каждой строке временной таблицы до группировки. Когда флаг &ТолькоСОборотами установлен, это условие отсекает все строки, где поля СуммаПоступление и СуммаВыбытие равны нулю. А это как раз те самые строки, которые содержат данные о начальных и конечных остатках! В итоге до операции суммирования доходят только строки с оборотами, и мы теряем данные по остаткам.
Для решения этой задачи нужно применять фильтрацию не к исходным данным, а к сгруппированным результатам. Для этого в языке запросов 1С существует специальный оператор. Рассмотрим два способа решения проблемы.
Самый правильный и надежный способ — изменить текст запроса. Вместо фильтрации в секции ГДЕ, нужно использовать секцию ИМЕЮЩИЕ (HAVING).
Ключевое отличие:
ГДЕ — фильтрует строки до того, как они будут сгруппированы.ИМЕЮЩИЕ — фильтрует уже сгруппированные записи на основе агрегатных функций (СУММА, КОЛИЧЕСТВО и т.д.).Это именно то, что нам нужно: сначала сгруппировать все данные по номенклатуре, получить итоговые суммы по остаткам и оборотам, и только потом проверить, были ли обороты по этой номенклатуре.
Пошаговая инструкция:
ВЫБОР КОГДА &ТолькоСОборотами ... КОНЕЦ из секции ГДЕ.СГРУППИРОВАТЬ ПО, добавьте новую секцию ИМЕЮЩИЕ.Вот как будет выглядеть исправленный фрагмент запроса:
ВЫБРАТЬ
ОстаткиИОбороты.Счет КАК Счет,
ОстаткиИОбороты.Номенклатура КАК Номенклатура,
СУММА(ОстаткиИОбороты.СуммаКонОст) КАК СуммаКонОст,
СУММА(ОстаткиИОбороты.КоличествоКонОст) КАК КоличествоКонОст,
ОстаткиИОбороты.Склад КАК Склад,
ОстаткиИОбороты.Организация КАК Организация,
ОстаткиИОбороты.Подразделение КАК Подразделение,
СУММА(ОстаткиИОбороты.СуммаПоступление) КАК СуммаПоступление,
СУММА(ОстаткиИОбороты.СуммаВыбытие) КАК СуммаВыбытие,
СУММА(ОстаткиИОбороты.КоличествоПоступление) КАК КоличествоПоступление,
СУММА(ОстаткиИОбороты.КоличествоВыбытие) КАК КоличествоВыбытие,
СУММА(ОстаткиИОбороты.СуммаНачОст) КАК СуммаНачОст,
СУММА(ОстаткиИОбороты.КоличествоНачОст) КАК КоличествоНачОст,
ОстаткиИОбороты.Регистратор КАК Регистратор
ИЗ
ОстаткиИОбороты КАК ОстаткиИОбороты
ГДЕ
ВЫБОР
КОГДА &Номенклатура = ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка) ИЛИ &Номенклатура = НЕОПРЕДЕЛЕНО
ТОГДА ИСТИНА
ИНАЧЕ ОстаткиИОбороты.Номенклатура = &Номенклатура
КОНЕЦ
И ВЫБОР
КОГДА &Склад = ЗНАЧЕНИЕ(Справочник.Склады.ПустаяСсылка) ИЛИ &Склад = НЕОПРЕДЕЛЕНО
ТОГДА ИСТИНА
ИНАЧЕ ОстаткиИОбороты.Склад = &Склад
КОНЕЦ
И ВЫБОР
КОГДА &НоменклатурнаяГруппа = ЗНАЧЕНИЕ(Справочник.НоменклатурныеГруппы.ПустаяСсылка) ИЛИ &НоменклатурнаяГруппа = НЕОПРЕДЕЛЕНО
ТОГДА ИСТИНА
ИНАЧЕ ОстаткиИОбороты.Номенклатура.НоменклатурнаяГруппа = &НоменклатурнаяГруппа
КОНЕЦ
СГРУППИРОВАТЬ ПО
ОстаткиИОбороты.Счет,
ОстаткиИОбороты.Номенклатура,
ОстаткиИОбороты.Склад,
ОстаткиИОбороты.Организация,
ОстаткиИОбороты.Подразделение,
ОстаткиИОбороты.Регистратор
ИМЕЮЩИЕ
ВЫБОР
КОГДА &ТолькоСОборотами
ТОГДА СУММА(ОстаткиИОбороты.СуммаПоступление) <> 0
ИЛИ СУММА(ОстаткиИОбороты.СуммаВыбытие) <> 0
ИНАЧЕ ИСТИНА
КОНЕЦ
Теперь, когда параметр &ТолькоСОборотами установлен в ИСТИНА, система сначала рассчитает все итоги (СУММА(...)) для каждой номенклатуры, а уже потом проверит, были ли по ней обороты. Если оборотов не было, вся сгруппированная строка будет отброшена. Если обороты были, строка попадет в отчет со всеми рассчитанными значениями, включая начальные и конечные остатки.
Если вы не хотите или не можете изменять текст запроса, похожего результата можно добиться стандартными средствами настройки СКД. Этот способ заключается в наложении отбора на вычисляемые поля (ресурсы) отчета.
Пошаговая инструкция:
СуммаПоступление (или КоличествоПоступление), Условие: Не равно, Значение: 0.СуммаВыбытие (или КоличествоВыбытие), Условие: Не равно, Значение: 0.После этих настроек в пользовательском режиме отчета появится новый флажок. Когда он включен, СКД будет применять созданный нами сложный отбор к уже готовым, рассчитанным данным, что приведет к корректному результату. Отчет покажет только те строки, где итоговый приход или итоговый расход не равны нулю, со всеми сопутствующими данными по остаткам.
Оба способа приводят к желаемому результату. Выбор между ними зависит от ваших предпочтений: первый способ (с ИМЕЮЩИЕ) считается более "академически" правильным и инкапсулирует логику внутри запроса, а второй (настройка отбора) — более гибкий и не требует навыков программирования.