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