В процессе работы с отчетами в 1С часто возникает необходимость собрать информацию из разных источников данных, например, из нескольких регистров. Такая задача кажется простой на первый взгляд, но при попытке объединить данные в системе компоновки данных (СКД) могут появиться сложности: некорректное отображение, задвоение информации или даже ошибки. Сегодня мы рассмотрим, как эффективно и правильно объединять данные из различных регистров, таких как регистр накопления «Продажи» и регистр накопления «ТоварыНаСкладах», чтобы получить полную картину по товарам, включающую продажи, остатки и резервы, а также как учесть характеристики номенклатуры при таком объединении.
Предположим, что мы работаем в конфигурации 1С:Управление торговлей 11.4 и нам нужно вывести в отчет количество проданных товаров (из регистра «Продажи») и остаток этих же товаров на складе (из регистра «ТоварыНаСкладах») для каждой номенклатуры. Мы выясним, какой подход является наиболее правильным, чтобы данные не задваивались и не терялись, используя проверенные механизмы анализа закупок, продаж и остатков товаров.
Система компоновки данных предоставляет мощный механизм для построения сложных отчетов. Когда нам необходимо объединить данные из разных источников, у нас есть несколько подходов. Рассмотрим два основных метода: использование связей наборов данных непосредственно в конструкторе СКД и объединение данных напрямую в тексте запроса СКД. Для тех, кто сомневается в выборе, полезно изучить сравнение этих двух подходов, чтобы понять, что и когда лучше выбирать.
Первый подход заключается в определении нескольких наборов данных запроса в схеме компоновки данных и последующем создании связей между ними. Разберем этот метод по шагам.
Создаем новую схему компоновки данных. Открываем конфигурацию, создаем новый внешний отчет или обработку, добавляем новую схему компоновки данных.
Добавляем наборы данных запроса. Переходим на закладку «Наборы данных» и добавляем два отдельных набора данных типа «Запрос». Для нашего примера это будут данные из регистра накопления «Продажи» и регистра накопления «ТоварыНаСкладах».
Для первого набора данных, например, «ПродажиОбороты», мы можем выбрать поля Номенклатура и КоличествоОборот из виртуальной таблицы РегистрНакопления.Продажи.Обороты. Дополнительно можно реализовать анализ списания запасов с выводом себестоимости и цены продажи даже без закрытия месяца (поможет отчет по валовой прибыли без закрытия месяца).
Для второго набора данных, например, «ТоварыНаСкладахОстатки», выбираем поля Номенклатура и КоличествоОстаток из виртуальной таблицы РегистрНакопления.ТоварыНаСкладах.Остатки.
Важно помнить, что виртуальные таблицы Обороты и Остатки требуют параметров периода. Настройте их соответственно на закладке «Параметры» схемы СКД.
Создаем связи между наборами данных. Переходим на закладку «Связи наборов данных». Здесь мы указываем, как наборы данных должны быть объединены. Создаем новую связь.
Выбираем «Источник» (например, «ПродажиОбороты») и «Приемник» (например, «ТоварыНаСкладахОстатки»).
Определяем выражение связи. В нашем случае, данные должны связываться по номенклатуре. Поэтому выражение связи будет выглядеть как ПродажиОбороты.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура.
Выбираем тип соединения. Это один из самых критичных моментов. Неправильный выбор типа соединения может привести к потере данных или их задвоению:
Левое внешнее соединение (LEFT JOIN): Показывает все записи из первого (левого) набора данных, и соответствующие им записи из второго (правого) набора. Если во втором наборе нет соответствия, поля из него будут пустыми (NULL). Этот тип соединения часто используется, когда мы хотим увидеть все продажи, даже если по каким-то товарам нет остатков на складе. О том, как корректно использовать ЛЕВОЕ СОЕДИНЕНИЕ и избежать ошибок, стоит почитать отдельно.
Правое внешнее соединение (RIGHT JOIN): Аналогично левому, но показывает все записи из правого набора данных и соответствующие из левого. Если мы хотим увидеть все товары на складе, даже если по ним не было продаж.
Внутреннее соединение (INNER JOIN): Показывает только те записи, для которых есть соответствия в обоих наборах данных. Если товар не продавался или его нет на складе, он не попадет в отчет. Используется, когда нужны только те товары, которые и продавались, и есть в наличии.
Полное внешнее соединение (FULL OUTER JOIN): Показывает все записи из обоих наборов данных, заполняя отсутствующие поля значениями NULL. Это позволяет увидеть и продажи без остатков, и остатки без продаж.
Для нашей задачи, когда мы хотим видеть все продажи (и при этом остатки, если они есть), наиболее подходящим будет Левое внешнее соединение, где основным (левым) набором данных будут «ПродажиОбороты».
Несмотря на то, что этот метод кажется интуитивно понятным, при сложных связях или большом объеме данных он может быть менее производительным или давать неочевидные результаты. Поэтому рассмотрим второй, более гибкий и часто рекомендуемый разработчиками 1С подход.
Наиболее эффективный и контролируемый способ объединения данных – это создание единого запроса в СКД, который с помощью оператора СОЕДИНЕНИЕ объединяет нужные регистры. Этот подход дает полный контроль над логикой объединения, что особенно важно, когда требуется реализовать отчет по движениям товаров с резервами нарастающим итогом.
Создаем единый набор данных запроса. На закладке «Наборы данных» схемы СКД добавляем всего один набор данных типа «Запрос».
Пишем запрос с ЛЕВЫМ СОЕДИНЕНИЕМ. Внутри этого запроса мы будем использовать оператор ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN) для объединения виртуальных таблиц регистров «Продажи» и «ТоварыНаСкладах».
Рассмотрим подробнее пример запроса, который позволит нам объединить данные по продажам и остаткам:
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.КоличествоОборот КАК КоличествоПродано,
ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоНаСкладе
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&НачалоПериода, &КонецПериода, , ) КАК ТоварыНаСкладахОстатки
ПО ПродажиОбороты.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура
Проанализируем этот запрос по частям:
ВЫБРАТЬ ... КАК ...: Здесь мы указываем, какие поля хотим получить в результате и как их назвать в отчете. Мы выбираем Номенклатура, КоличествоОборот (из продаж) и КоличествоОстаток (со склада).
ИЗ РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК ПродажиОбороты: Это наш основной источник данных – виртуальная таблица Обороты регистра накопления «Продажи». Виртуальные таблицы в 1С генерируются платформой на основе данных регистра и позволяют получать агрегированные данные (остатки, обороты, срез последних) за определенный период или на определенную дату. Параметры &НачалоПериода и &КонецПечета крайне важны для корректной фильтрации данных по времени. Авто означает, что обороты будут рассчитаны в целом по периоду без детализации. Мы присваиваем этой таблице псевдоним ПродажиОбороты для удобства.
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&НачалоПериода, &КонецПериода, , ) КАК ТоварыНаСкладахОстатки: К основному набору данных мы присоединяем виртуальную таблицу Остатки регистра накопления «ТоварыНаСкладах». Использование ЛЕВОГО СОЕДИНЕНИЯ гарантирует, что мы увидим все записи из ПродажиОбороты, даже если по ним нет соответствующих остатков. Если &КонецПериода для остатков не указан, обычно это подразумевает остатки на конец дня &НачалоПериода.
ПО ПродажиОбороты.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура: Это условие, по которому происходит соединение двух таблиц. Мы связываем их по полю Номенклатура. Очень важно, чтобы поля для связи были идентичными по типу и смыслу.
Учет характеристик номенклатуры. Часто бывает необходимо детализировать данные не только по номенклатуре, но и по ее характеристикам (например, размер, цвет). Если в регистрах «Продажи» и «ТоварыНаСкладах» используется характеристика номенклатуры, мы можем легко добавить ее в условие соединения, учитывая особенности вывода характеристик номенклатуры в сложных аналитических отчетах. Подобную детализацию можно увидеть в различных вариантах отчетов с характеристиками и без.
Посмотрим на пример запроса, который учитывает характеристики:
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.Характеристика КАК Характеристика,
ПродажиОбороты.КоличествоОборот КАК КоличествоПродано,
ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоНаСкладе
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&НачалоПериода, &КонецПериода, , ) КАК ТоварыНаСкладахОстатки
ПО ПродажиОбороты.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура
И ПродажиОбороты.Характеристика = ТоварыНаСкладахОстатки.Характеристика
Мы видим, что в условие ПО добавилась вторая строка: И ПродажиОбороты.Характеристика = ТоварыНаСкладахОстатки.Характеристика. Таким образом, соединение будет производиться не только по самой номенклатуре, но и по ее конкретной характеристике.
Также важно убедиться, что параметры виртуальных таблиц (такие как &НачалоПериода, &КонецПериода) совпадают или корректно настроены в СКД для обоих частей запроса. Это обеспечит получение данных за один и тот же временной интервал или на одну и ту же дату.
При работе с объединением данных в СКД, особенно из виртуальных таблиц, важно учитывать несколько нюансов:
Параметры виртуальных таблиц: Всегда внимательно относитесь к параметрам, передаваемым в виртуальные таблицы Обороты, Остатки, СрезПервых, СрезПоследних. Несоответствие периодов или дат может привести к некорректным результатам. Обычно мы выносим эти параметры на закладку «Параметры» СКД и связываем их с параметрами отчета (например, «Период»).
Производительность запросов: Сложные запросы с большим количеством соединений и виртуальных таблиц могут работать медленно на больших объемах данных. В таких случаях крайне важна оптимизация производительности сложных отчетов СКД при работе с виртуальными таблицами. Старайтесь оптимизировать запросы, использовать только необходимые поля и условия. Иногда может потребоваться создание временных таблиц для предварительной обработки данных, прежде чем их объединять.
Агрегатные функции: Если после объединения данных у вас появляются задвоения, возможно, вам потребуется использовать агрегатные функции в СКД или на уровне запроса. Чтобы избежать ошибок, полезно провести сравнение товаров на складах организаций и себестоимости товаров, что позволит выявить расхождения на ранних этапах.
Выбор полей для связи: Всегда выбирайте для связи поля, которые гарантируют уникальность или однозначное соответствие. В нашем случае это сочетание Номенклатура и Характеристика. Если бы мы связывали только по Номенклатуре, а характеристики были разными, это могло бы привести к неправильному соединению.
Объединение данных из разных регистров в отчетах 1С с использованием СКД — это мощный инструмент для построения комплексной аналитики. Мы разобрали два основных подхода: использование связей наборов данных в конструкторе СКД и, что более предпочтительно, написание единого запроса с оператором ЛЕВОЕ СОЕДИНЕНИЕ. Второй метод предоставляет большую гибкость и контроль, позволяя точно определить логику объединения, включая учет характеристик номенклатуры и правильную работу с виртуальными таблицами.
Применяя эти знания, вы сможете создавать детальные и точные отчеты, которые объединяют информацию из различных частей вашей учетной системы, а также эффективно искать ошибки в учете через сравнение товаров на складах и организаций. Это предоставит полную картину для принятия качественных управленческих решений.