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