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