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