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