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