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