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