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