При работе с Системой Компоновки Данных (СКД) в 1С часто возникает задача формирования отчетов, которые должны корректно отображать данные за произвольный период, например, за несколько месяцев, группируя результаты по месяцам и выводя их в отдельных колонках. Нередко пользователи сталкиваются с проблемами дублирования строк, некорректной агрегации и отображения неверных итогов при расширении периода отчета.
Рассмотрим типовую ситуацию: у нас есть отчет, который хорошо работает для одного месяца, но при выборе нескольких месяцев начинает показывать некорректные данные, дублировать строки и неверно рассчитывать ресурсы. Например, для проекта "Союз" за один месяц отчет показывает трудоемкость 8, но за два месяца (май-июнь) данные по июню внезапно становятся 4, а также вместо одной строки по проекту выводятся две, каждая с неверными данными за соответствующий месяц. Наша задача — избавиться от дублирований, объединить данные по одинаковым работам в выбранных месяцах и вывести корректные данные в колонках, соответствующих каждому месяцу.
Прежде всего, проанализируем структуру запроса, предоставленного изначально. Запрос состоит из трех частей, каждая из которых формирует временные таблицы:
ВТОбщиеДанные, агрегируя данные по проектам, датам начала/окончания и дате документа.ВТВремя, суммируя нормативные часы.Рассмотрим код запроса более подробно:
ВЫБРАТЬ
Распределение.Проект КАК Проект,
СУММА(Распределение.ФактРаспределенныеЧасы) КАК ФактРаспределенныеЧасы,
Распределение.ДатаНачала КАК ДатаНачала,
Распределение.ДатаОкончания КАК ДатаОкончания,
Распределение.Ссылка.Дата КАК Дата
ПОМЕСТИТЬ ВТОбщиеДанные
ИЗ
Документ.Распределение.Проекты КАК Распределение
ГДЕ
Распределение.Ссылка.Дата МЕЖДУ &НачПериода И &КонПериода
И Распределение.Ссылка.ПометкаУдаления = ЛОЖЬ
И Распределение.Ссылка.Проведен = ИСТИНА
И Распределение.Ссылка.СтатусДокумента = &СтатусДокумента
И Распределение.Ссылка.Дата >= &НачПериода
СГРУППИРОВАТЬ ПО
Распределение.ДатаНачала,
Распределение.ДатаОкончания,
Распределение.Ссылка.Дата,
Распределение.Проект
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СУММА(ГрафикиРаботыПоВидамВремени.ДополнительноеЗначениеНорма) КАК ДополнительноеЗначениеНорма
ПОМЕСТИТЬ ВТВремя
ИЗ
РегистрСведений.ГрафикиРаботыПоВидамВремени КАК ГрафикиРаботыПоВидамВремени
ГДЕ
ГрафикиРаботыПоВидамВремени.Дата МЕЖДУ &НачПериода И &КонПериода
И ГрафикиРаботыПоВидамВремени.ВидУчетаВремени = &ВидУчетаВремени
И ГрафикиРаботыПоВидамВремени.ГрафикРаботы.Наименование ПОДОБНО "%Пятидневка 40 ч (1 ставка)%"
И ГрафикиРаботыПоВидамВремени.Дата >= &НачПериода
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТОбщиеДанные.Проект КАК Проект,
ВТОбщиеДанные.ДатаНачала КАК ДатаНачала,
ВТОбщиеДанные.ДатаОкончания КАК ДатаОкончания,
ВТОбщиеДанные.ФактРаспределенныеЧасы / ВТВремя.ДополнительноеЗначениеНорма КАК ТрудоемкостьЗаПериод,
НАЧАЛОПЕРИОДА(ВТОбщиеДанные.Дата, МЕСЯЦ) КАК Месяц
ИЗ
ВТОбщиеДанные КАК ВТОбщиеДанные,
ВТВремя КАК ВТВремя
ГДЕ
ВТОбщиеДанные.Дата >= &НачПериода
СГРУППИРОВАТЬ ПО
ВТОбщиеДанные.ДатаНачала,
ВТОбщиеДанные.Проект,
НАЧАЛОПЕРИОДА(ВТОбщиеДанные.ДатаНачала, МЕСЯЦ),
ВТОбщиеДанные.ДатаОкончания,
ВТОбщиеДанные.ФактРаспределенныеЧасы / ВТВремя.ДополнительноеЗначениеНорма,
НАЧАЛОПЕРИОДА(ВТОбщиеДанные.Дата, МЕСЯЦ)
Выясним основные проблемы в данном запросе:
ВТОбщиеДанные и ВТВремя в последнем запросе. Использование синтаксиса ИЗ ВТОбщиеДанные КАК ВТОбщиеДанные, ВТВремя КАК ВТВремя без явного условия соединения (СОЕДИНЕНИЕ ПО ...) приводит к неявному декартову произведению или некорректному внутреннему соединению. Условие ГДЕ ВТОбщиеДанные.Дата >= &НачПериода не является условием соединения между временными таблицами. Автор уточнил, что соединять нужно "только по Дате и периоду регистрации". Это критически важно, так как таблица ВТВремя в текущем виде возвращает одну общую сумму нормативных часов за весь период, а не по дням/месяцам. Деление ФактРаспределенныеЧасы на эту общую сумму будет неверным для каждой детальной записи.СГРУППИРОВАТЬ ПО указаны почти все выбираемые поля, включая результат деления ФактРаспределенныеЧасы / ВТВремя.ДополнительноеЗначениеНорма. Это приводит к тому, что запрос возвращает уже сгруппированные данные, не позволяя СКД выполнять дальнейшую гибкую агрегацию и построение кросс-таблиц. Это и является основной причиной дублирования строк и невозможности правильной агрегации по месяцам в структуре отчета.ВТВремя. Таблица ВТВремя суммирует все ДополнительноеЗначениеНорма за указанный период в одну строку. Если нам нужна норма по каждой конкретной дате для расчета трудоемкости по датам документов, то такая агрегация не подходит.Общая рекомендация для СКД: запрос должен возвращать максимально "сырые", негруппированные данные, а все группировки, агрегации и форматирование следует настраивать уже в структуре отчета СКД. Это обеспечивает максимальную гибкость и правильность расчетов.
Для устранения выявленных проблем, мы внесем изменения в запрос. Наша цель — получить плоскую, негруппированную таблицу с детальными данными, которая затем будет обработана СКД.
ВТВремяНам необходимо, чтобы ВТВремя возвращала нормативные часы для каждой даты в периоде, а не одну общую сумму. Для этого добавим группировку по Дата в запросе к регистру ГрафикиРаботыПоВидамВремени.
Исходный запрос для ВТВремя:
ВЫБРАТЬ
СУММА(ГрафикиРаботыПоВидамВремени.ДополнительноеЗначениеНорма) КАК ДополнительноеЗначениеНорма
ПОМЕСТИТЬ ВТВремя
ИЗ
РегистрСведений.ГрафикиРаботыПоВиВидамВремени КАК ГрафикиРаботыПоВидамВремени
ГДЕ
ГрафикиРаботыПоВидамВремени.Дата МЕЖДУ &НачПериода И &КонПериода
И ГрафикиРаботыПоВидамВремени.ВидУчетаВремени = &ВидУчетаВремени
И ГрафикиРаботыПоВидамВремени.ГрафикРаботы.Наименование ПОДОБНО "%Пятидневка 40 ч (1 ставка)%"
И ГрафикиРаботыПоВидамВремени.Дата >= &НачПериода
;
Измененный запрос для ВТВремя:
ВЫБРАТЬ
ГрафикиРаботыПоВидамВремени.Дата КАК Дата,
СУММА(ГрафикиРаботыПоВидамВремени.ДополнительноеЗначениеНорма) КАК ДополнительноеЗначениеНорма
ПОМЕСТИТЬ ВТВремя
ИЗ
РегистрСведений.ГрафикиРаботыПоВидамВремени КАК ГрафикиРаботыПоВидамВремени
ГДЕ
ГрафикиРаботыПоВидамВремени.Дата МЕЖДУ &НачПериода И &КонПериода
И ГрафикиРаботыПоВидамВремени.ВидУчетаВремени = &ВидУчетаВремени
И ГрафикиРаботыПоВидамВремени.ГрафикРаботы.Наименование ПОДОБНО "%Пятидневка 40 ч (1 ставка)%"
И ГрафикиРаботыПоВидамВремени.Дата >= &НачПериода
СГРУППИРОВАТЬ ПО
ГрафикиРаботыПоВидамВремени.Дата // Группируем по дате, чтобы получить норму за каждый день
;
Теперь ВТВремя будет содержать одну строку с нормативными часами для каждой даты, что позволит нам корректно присоединять эти данные.
Мы изменим последнее выражение ВЫБРАТЬ следующим образом:
ЛЕВОЕ СОЕДИНЕНИЕ по полю Дата, чтобы связать документы с нормами часов. Мы используем ЛЕВОЕ СОЕДИНЕНИЕ, чтобы данные из ВТОбщиеДанные не терялись, даже если для какой-то даты нет данных в ВТВремя.СГРУППИРОВАТЬ ПО.ЕСТЬNULL для поля НормаЧасовЗаДень, чтобы избежать деления на NULL, если для какой-то даты не нашлось нормы.ДатаНачала и ДатаОкончания проекта, чтобы не путать их с датой документа или датой отчета.НАЧАЛОПЕРИОДА(ВТОбщиеДанные.Дата, МЕСЯЦ), чтобы потом использовать его для группировки в СКД.Исходный результирующий запрос:
ВЫБРАТЬ
ВТОбщиеДанные.Проект КАК Проект,
ВТОбщиеДанные.ДатаНачала КАК ДатаНачала,
ВТОбщиеДанные.ДатаОкончания КАК ДатаОкончания,
ВТОбщиеДанные.ФактРаспределенныеЧасы / ВТВремя.ДополнительноеЗначениеНорма КАК ТрудоемкостьЗаПериод,
НАЧАЛОПЕРИОДА(ВТОбщиеДанные.Дата, МЕСЯЦ) КАК Месяц
ИЗ
ВТОбщиеДанные КАК ВТОбщиеДанные,
ВТВремя КАК ВТВремя
ГДЕ
ВТОбщиеДанные.Дата >= &НачПериода
СГРУППИРОВАТЬ ПО
ВТОбщиеДанные.ДатаНачала,
ВТОбщиеДанные.Проект,
НАЧАЛОПЕРИОДА(ВТОбщиеДанные.ДатаНачала, МЕСЯЦ),
ВТОбщиеДанные.ДатаОкончания,
ВТОбщиеДанные.ФактРаспределенныеЧасы / ВТВремя.ДополнительноеЗначениеНорма,
НАЧАЛОПЕРИОДА(ВТОбщиеДанные.Дата, МЕСЯЦ)
Измененный результирующий запрос:
ВЫБРАТЬ
ВТОбщиеДанные.Проект КАК Проект,
ВТОбщиеДанные.ДатаНачала КАК ДатаНачалаПроекта, // Дата начала проекта
ВТОбщиеДанные.ДатаОкончания КАК ДатаОкончанияПроекта, // Дата окончания проекта
ВТОбщиеДанные.ФактРаспределенныеЧасы КАК ФактРаспределенныеЧасы, // Исходные часы
ЕСТЬNULL(ВТВремя.ДополнительноеЗначениеНорма, 0) КАК НормаЧасовЗаДень, // Норма за конкретный день
НАЧАЛОПЕРИОДА(ВТОбщиеДанные.Дата, МЕСЯЦ) КАК МесяцОтчета // Месяц для группировки в СКД
ИЗ
ВТОбщиеДанные КАК ВТОбщиеДанные
ЛЕВОЕ СОЕДИНЕНИЕ ВТВремя КАК ВТВремя
ПО ВТОбщиеДанные.Дата = ВТВремя.Дата // Соединяем по дате документа
Таким образом, полный текст запроса в схеме компоновки данных будет выглядеть следующим образом:
ВЫБРАТЬ
Распределение.Проект КАК Проект,
СУММА(Распределение.ФактРаспределенныеЧасы) КАК ФактРаспределенныеЧасы,
Распределение.ДатаНачала КАК ДатаНачала,
Распределение.ДатаОкончания КАК ДатаОкончания,
Распределение.Ссылка.Дата КАК Дата
ПОМЕСТИТЬ ВТОбщиеДанные
ИЗ
Документ.Распределение.Проекты КАК Распределение
ГДЕ
Распределение.Ссылка.Дата МЕЖДУ &НачПериода И &КонПериода
И Распределение.Ссылка.ПометкаУдаления = ЛОЖЬ
И Распределение.Ссылка.Проведен = ИСТИНА
И Распределение.Ссылка.СтатусДокумента = &СтатусДокумента
И Распределение.Ссылка.Дата >= &НачПериода
СГРУППИРОВАТЬ ПО
Распределение.ДатаНачала,
Распределение.ДатаОкончания,
Распределение.Ссылка.Дата,
Распределение.Проект
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ГрафикиРаботыПоВидамВремени.Дата КАК Дата,
СУММА(ГрафикиРаботыПоВидамВремени.ДополнительноеЗначениеНорма) КАК ДополнительноеЗначениеНорма
ПОМЕСТИТЬ ВТВремя
ИЗ
РегистрСведений.ГрафикиРаботыПоВидамВремени КАК ГрафикиРаботыПоВидамВремени
ГДЕ
ГрафикиРаботыПоВидамВремени.Дата МЕЖДУ &НачПериода И &КонПериода
И ГрафикиРаботыПоВидамВремени.ВидУчетаВремени = &ВидУчетаВремени
И ГрафикиРаботыПоВидамВремени.ГрафикРаботы.Наименование ПОДОБНО "%Пятидневка 40 ч (1 ставка)%"
И ГрафикиРаботыПоВидамВремени.Дата >= &НачПериода
СГРУППИРОВАТЬ ПО
ГрафикиРаботыПоВидамВремени.Дата
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТОбщиеДанные.Проект КАК Проект,
ВТОбщиеДанные.ДатаНачала КАК ДатаНачалаПроекта,
ВТОбщиеДанные.ДатаОкончания КАК ДатаОкончанияПроекта,
ВТОбщиеДанные.ФактРаспределенныеЧасы КАК ФактРаспределенныеЧасы,
ЕСТЬNULL(ВТВремя.ДополнительноеЗначениеНорма, 0) КАК НормаЧасовЗаДень,
НАЧАЛОПЕРИОДА(ВТОбщиеДанные.Дата, МЕСЯЦ) КАК МесяцОтчета
ИЗ
ВТОбщиеДанные КАК ВТОбщиеДанные
ЛЕВОЕ СОЕДИНЕНИЕ ВТВремя КАК ВТВремя
ПО ВТОбщиеДанные.Дата = ВТВремя.Дата
Теперь наш запрос возвращает плоский набор данных, содержащий проект, даты начала/окончания проекта, фактически распределенные часы, нормативные часы за соответствующий день и месяц отчета. Эти данные готовы для гибкой обработки в СКД.
После модификации запроса переходим к настройке самой схемы компоновки данных. Здесь мы зададим, как именно будут группироваться, агрегироваться и отображаться данные.
В конструкторе схемы компоновки данных на вкладке "Наборы данных" убедитесь, что все необходимые поля из запроса доступны. При необходимости, можно добавить вычисляемые поля или изменить представление существующих.
Например, если вы хотите рассчитать ТрудоемкостьЗаПериод как ресурс, вы не будете создавать его как вычисляемое поле, а определите его на следующей вкладке.
Перейдем на вкладку "Ресурсы". Здесь мы определяем, какие поля будут агрегироваться и как. Нам необходимо определить ресурс ТрудоемкостьЗаПериод. Выражение для ресурса будет учитывать, что ФактРаспределенныеЧасы и НормаЧасовЗаДень приходят из детальных записей и их нужно правильно агрегировать. Если ФактРаспределенныеЧасы уже является суммой за период по проекту и дате документа (как в ВТОбщиеДанные), а НормаЧасовЗаДень — это норма на одну дату, то выражение должно суммировать отношение:
СУММА(ФактРаспределенныеЧасы / НормаЧасовЗаДень)
Обязательно убедитесь, что НормаЧасовЗаДень не равна нулю во избежание ошибок деления. Функция ЕСТЬNULL в запросе уже помогает с этим, заменяя NULL на 0, но в случае, если в детализации окажется фактический 0, это все равно вызовет ошибку. Для более надежного решения можно добавить проверку в выражении ресурса, например:
СУММА(ВЫБОР КОГДА НормаЧасовЗаДень = 0 ТОГДА 0 ИНАЧЕ ФактРаспределенныеЧасы / НормаЧасовЗаДень КОНЕЦ)
После определения ресурса вы можете указать для каких группировок он доступен. В нашем случае, он будет доступен для всех группировок, чтобы его можно было выводить как в общей сумме, так и по месяцам.
На вкладке "Параметры" определим параметры периода. Для удобного выбора периода мы рекомендуем использовать механизм "Стандартный период".
ПериодОтчета, с типом "Стандартный период".НачПериода и КонПериода в выражениях укажите:
НачПериода: &ПериодОтчета.ДатаНачалаКонПериода: &ПериодОтчета.ДатаОкончанияЭто позволит пользователю удобно выбирать предопределенные периоды (например, "Текущий месяц", "Последний квартал") или задавать произвольный интервал.
Это ключевой шаг для правильного отображения данных. На вкладке "Настройки" (или "Настройки отчета" в расширенном режиме) мы будем формировать структуру отчета.
Для нашей задачи нам потребуется создать перекрестную таблицу (сводную таблицу), где по строкам будут данные по проектам, а по колонкам — месяцы.
Разберем по шагам:
Создание группировки по строкам:
Добавьте новую группировку в область "Строки". В эту группировку мы включим поля, которые идентифицируют уникальную "работу" или проект. Это могут быть:
ПроектДатаНачалаПроектаДатаОкончанияПроектаИли, если вы хотите более детальную запись, можете добавить другие поля, которые определяют уникальную строку до агрегации по месяцам.
В выбранные поля для этой группировки выберите Проект, ДатаНачалаПроекта, ДатаОкончанияПроекта.
На этом уровне будут отображаться общие данные по проекту.
Создание группировки по колонкам (Месяцы):
Добавьте новую группировку в область "Колонки". В эту группировку мы добавим поле МесяцОтчета.
В выбранные поля для этой группировки также добавьте МесяцОтчета. Это обеспечит вывод заголовков колонок с названиями месяцев.
Для поля МесяцОтчета в настройках выбранных полей, на вкладке "Оформление" (или в свойствах поля), вы можете настроить формат даты, например, ДФ='ММММ гггг', чтобы заголовки колонок выглядели как "Июнь 2024", "Июль 2024" и т.д.
Важный момент для колонок: чтобы обеспечить отображение всех месяцев в выбранном периоде, даже если по ним нет данных, необходимо установить "Тип заполнения" для группировки по МесяцОтчета. Перейдите в свойства группировки по МесяцОтчета, найдите вкладку "Прочее" (или "Дополнительные настройки") и в поле "Тип заполнения" выберите "Месяц". Это заставит СКД сгенерировать колонки для всех месяцев в заданном диапазоне параметров НачПериода и КонПериода.
Размещение ресурсов:
Теперь необходимо добавить определенный ранее ресурс ТрудоемкостьЗаПериод в выбранные поля для детальных записей или на уровень соответствующей группировки, чтобы он отображался в пересечении строк и колонок. Обычно ресурс помещается в "Детальные записи" или на уровень группировки по строкам.
Если вы хотите, чтобы ресурс отображался в каждой ячейке, соответствующей пересечению проекта и месяца, добавьте ТрудоемкостьЗаПериод в список выбранных полей для "Детальных записей". СКД автоматически рассчитает его для каждого пересечения.
Устранение дублирующихся строк (автоматически СКД):
Поскольку мы убрали группировки из запроса и оставили их на откуп СКД, то СКД сама будет агрегировать данные. Если в рамках одной комбинации "Проект" + "ДатаНачалаПроекта" + "ДатаОкончанияПроекта" + "МесяцОтчета" окажется несколько детальных записей (например, по разным дням месяца), СКД автоматически их просуммирует, если ресурс настроен на СУММА(), устраняя тем самым дублирование на уровне вывода отчета.
Таким образом, структура отчета в СКД будет примерно следующей:
МесяцОтчета
Проект
ДатаНачалаПроекта
ДатаОкончанияПроекта
ТрудоемкостьЗаПериод (как ресурс)В режиме "Быстрые настройки" это будет выглядеть как добавление полей Проект, ДатаНачалаПроекта, ДатаОкончанияПроекта в "Поля группировок строк", а МесяцОтчета — в "Поля группировок колонок". Ресурс ТрудоемкостьЗаПериод будет размещен в области данных.
Для более эффективной работы с СКД и оптимизации отчетов, мы предлагаем следующие рекомендации:
Разделение запроса и настроек СКД:
Всегда стремитесь к тому, чтобы ваш запрос возвращал максимально детализированные, "сырые" данные. Это означает, что в запросе не должно быть группировок и агрегаций, если эти действия предполагаются в СКД. Сама СКД имеет мощные инструменты для группировки, сортировки, отбора, условного оформления и вывода ресурсов. Такой подход позволяет изменять представление отчета (например, добавлять новые группировки или переключать их местами) без необходимости модификации кода запроса.
Использование вычисляемых полей и полей-ресурсов:
Если у вас есть сложные расчеты, такие как наша "ТрудоемкостьЗаПериод", предпочтительнее определять их как поля-ресурсы на соответствующей вкладке СКД. Это позволяет СКД автоматически пересчитывать эти значения для каждого уровня группировки и детализации. Если расчеты необходимы до агрегации, их можно добавить как вычисляемые поля в наборе данных.
Типы данных полей из временных таблиц:
При использовании временных таблиц в запросе СКД, иногда бывает полезно явно указывать тип данных для полей. Это можно сделать с помощью выражения ВЫРАЗИТЬ(ВТ.ЗначениеПоля КАК <Описание типа>). Это особенно актуально, если СКД неправильно определяет тип поля, что может влиять на возможности сортировки, отбора или агрегации.
Обработка NULL-значений:
Всегда предусматривайте обработку NULL-значений, особенно при делении или использовании числовых полей из ЛЕВОГО СОЕДИНЕНИЯ. Как мы видели в нашем примере, ЕСТЬNULL(ВТВремя.ДополнительноеЗначениеНорма, 0) предотвращает деление на NULL. В выражении ресурсов также можно использовать конструкцию ВЫБОР КОГДА ... ИНАЧЕ ... КОНЕЦ для обработки особых случаев.
Менеджер временных таблиц (для программного формирования):
Если вы формируете отчет на СКД программно, вы можете передавать менеджер временных таблиц в метод Инициализировать процессора компоновки данных. Это может быть полезно для оптимизации, если одинаковые временные таблицы используются в разных наборах данных СКД или в других запросах.
Оформление отчета:
Используйте вкладку "Условное оформление" в СКД для выделения важных данных, изменения цвета шрифта, фона ячеек и т.д. Это делает отчет более наглядным и удобным для анализа.
Применяя эти шаги и рекомендации, вы сможете создавать гибкие, точные и корректно отображающие данные отчеты на СКД, даже при сложной логике агрегации и необходимости вывода информации за различные временные интервалы с разбивкой по месяцам.