Меню

Временная таблица регистра накопления Остатки и обороты



Правильный расчет итогов в СКД, при объединении нескольких разнородных таблиц остатков и оборотов

Содержание

  • Вступление
  • Среда разработки
  • Постановка задачи
  • Ошибочное решение
  • Правильное решение
  • Файлы для скачивания

Вступление

Но, к сожалению, такой идеальный вариант на практике встречается редко. Как правило, выбирать данные из регистров нужно, хоть и по совпадающим измерениям, но среди них попадаются и измерения, которые есть в одних регистрах, но которых нет в других регистрах. Также, обычно, какой-то регистратор попадает в выборку по одному регистру, но не попадает в выборку по другому регистру. А еще бывает так, что среди регистраторов одних регистров встречаются документы, которые не являются регистраторами по другим регистрам.

И если имеет место хотя бы один из перечисленных выше факторов, то простое объединение виртуальных таблиц ОстаткиИОбороты этих регистров в наборе-запросе СКД , приведет к тому, что начальные и конечные остатки по регистраторам и по необщим измерениям регистров будут рассчитываться некорректно.

О том, как справиться с данной проблемой, и все-таки объединить в одном отчете несколько виртуальных таблиц ОстаткиИОбороты так, чтобы остатки считались правильно, и пойдет речь в этой статье.

На полученное решение меня вдохновила статья «Создание отчета с остатками без виртуальных таблиц» с сайта ИТС. В ней решается немного другая, хоть и похожая, задача — рассчитываются остатки по таблицам документов, формирующих приход и расход. Я же из этой статьи взял идею — вместо расчета виртуальной таблицы остатков и оборотов, начальные остатки считаю отдельно, обороты — отдельно, а затем все это аккуратно склеиваю в одну таблицу остатков и оборотов так, чтобы остатки везде были правильными, при этом еще и не забыв правильно посчитать конечные остатки.

Среда разработки

В качестве примера, на котором мы будем смотреть, как решается данная задача, будем использовать типовую конфигурацию «Управление производственным предприятием для Украины» (1.3.41.1).
При этом будем пользоваться платформой 1С версии 8.3.10.2699.
Сам отчет будем разрабатывать в виде внешнего отчета, готовую версию которого можно найти в разделе «Файлы для скачивания».

Постановка задачи

Данные в отчет должны выводиться в разрезе следующих группировок:

  • Склад — Склад, на котором учитывается номенклатура. Так как в регистрах накопления ЗаказыПоставщикам и РазмещениеЗаказовПокупателей измерение Склад отсутствует, то для колонок «Ожидаемая поставка» и «Резерв в поставке» в этой группировке должен отображаться Склад из шапки документа ЗаказПоставщику.
  • Номенклатура — Номенклатура, остаток которой отображается в отчете.
  • Характеристика номенклатуры — Характеристика номенклатуры, остаток которой отображается в отчете.
  • Документ резерва — Документ, которым товар был зарезервирован на складе или в поставке.
    Для данных, получаемых из регистра накопления ТоварыВРезервеНаСкладах — это одноименное измерение.
    Для данных, получаемых из регистра РазмещениеЗаказовПокупателей — это соответствующий «заказ покупателя».
    А для данных, получаемых из регистров ТоварыНаСкладах, ТоварыВРознице и ЗаказыПоставщикам, это поле будет Неопределено.
  • Заказ поставщику — Заказ поставщику, в рамках которого ожидается поставка. Будет заполнен только для ресурсов «Ожидаемая поставка», «Резерв в поставке» и производных от них.

Также должна быть возможность разворачивать отчет по периодам вплоть до регистратора.

Ошибочное решение

Для того, чтобы ощутить разницу между правильным и неправильным решениями, сначала попробуем решить задачу «в лоб» — создать отчет, получающий данные из виртуальных таблиц ОстаткиИОбороты.

Рассматривая пример ошибочного решения, я не буду останавливаться на несущественных деталях. Результирующий внешний отчет можно найти в разделе «Файлы для скачивания».

Создадим новый внешний отчет на схеме компоновки данных.
Добавим в его схему компоновки данных новый набор данных — запрос, в который поместим текст запроса, в котором данные таблиц ОстаткиИОбороты разных регистров накопления объединяются в одну таблицу.

Настроим поля набора данных, чтобы лишнее не отображалось, а нужное отображалось красиво.
И даже не забудем настроить красивое отображение периодов, как описано в статье «Форматирование на разных языках периодов в СКД».
На закладке «Вычисляемые поля» добавим поля для ресурсов «Свободный остаток», «Свободно в поставке» и «Плановый остаток».
На закладке «Ресурсы» укажем выражения для вычисления значений ресурсов по группировкам.
На закладке «Параметры» добавим доступный пользователю параметр «Период» типа СтандартныйПериод, а используемые в запросе параметры «ПериодНачало» и «ПериодОкончание» сделаем недоступными для пользователя и заполняемыми из параметра «Период».

Читайте также:  EAGLE FIGHTING CHAMPIONSHIP UFC Бокс Спорт EFC

Ну и на закладке «Настройки», для варианта «Основной» создадим требуемую нам тестовую иерархию группировок отчета, с разворотом данных до регистратора.
Чтобы не захламлять пример, я не стал добавлять группировки по периодам, а также по измерениям, которые присутствуют не во всех таблицах. С ними, рассматриваемые ошибки станут еще очевиднее, но нам и так будет достаточно.

Для отчета в целом настроим начальные значения параметров, выбранные поля, сортировку и другие настройки.

Также, важным моментом является еще одна настройка.
Т.к. в виртуальных таблицах ОстаткиИОбороты присутствуют записи с пустым регистратором, в которых запрос возвращает начальные и конечные остатки, а СКД использует их для расчетов, то, чтобы эти «пустые» строки не путали пользователей, нужно на группировке Регистратор настроить отбор
Настройка отбора по группировке Регистратор

При этом, также, обязательно нужно для этой группировки, на закладке «Другие настройки», настройку «Выводить отбор» установить в значение «Не выводить»
Отключение отображения отбора по группировке Регистратор

Скептики, которые могут заметить, что отчет не работает из-за этой настройки, могут попробовать ее не делать — результат будет еще хуже и непонятнее.

Отчет готов.
Если его сохранить и запустить в демо базе, то можно увидеть на первый взгляд правильные цифры. И очень этому радоваться.
До тех пор, пока не обнаружится что-то вроде такого:
Ошибка в получившемся отчете

Такое произошло из-за того, что документ «Заказ поставщику» не является регистратором по регистрам накопления ТоварыНаСкладах и ТоварыВРознице, по которым рассчитывается значение ресурса «Остаток». А раз так, то система и не рассчитала остатки товара на момент этого документа.

Правильное решение

Чтобы устранить обнаруженную в предыдущем разделе проблему, прийдется отказаться от виртуальных таблиц ОстаткиИОбороты, и взять на себя ответственность за расчет начальных и конечных остатков.

Полученный в результате внешний отчет можно найти в разделе «Файлы для скачивания».

Начнем с переписывания запроса.

Так как запрос будет достаточно сложным, то разобьем его на пакет из нескольких запросов.

Первым запросом пакета получим остатки на начало периода в разрезе необходимых нам в отчете измерений, и поместим их во временную таблицу вт_НачальныеОстатки.

По структуре этот запрос очень похож на исходный запрос, за тем исключением, что данные берутся не из виртуальных таблиц ОстаткиИОбороты, а из виртуальных таблиц Остатки. И выбираются только начальные остатки.

Также, обратите внимание на использование расширения языка запросов для построителя.
А именно, на явное указание доступных для выбора полей измерений и на указание доступных для отбора полей в параметрах виртуальных таблиц. Оно нужно для того, чтобы система правильно сопоставила одни и те же поля измерений из разных запросов пакета.

Еще важным моментом является исключение возможности появления в измерениях отчета значений NULL. Нужно это для того, чтобы в следующих запросах соединение таблиц по измерениям выполнялось корректно (т.к. NULL = NULL будет давать ЛОЖЬ, а не ИСТИНА). Достигается данное требование путем дополнения конструкций ВЫРАЗИТЬ оператором ВЫБОР Т.е. когда в измерении значение интересующего нас типа, то берем его, а если нет, то, чтобы система не вернула в это поле NULL, явно указываем пустое значение подходящего типа.

Следующим запросом пакета получим движения за период формирования отчета в разрезе необходимых нам в отчете измерений, и поместим их во временную таблицу вт_Движения.

Этот запрос по структуре тоже очень похож на исходный запрос, но в нем данные берутся из виртуальных таблиц Обороты.

Тут, кроме упомянутого выше использования расширения языка запросов для построителя, важным моментом является то, что не нужно выбирать данные в разрезе всех периодов. Достаточно выбрать ПериодСекунда и Регистратор.
И еще очень важно выбрать момент времени регистратора (для корректной связки таблиц остатков с таблицей движений).

Читайте также:  Сколько лет работодателю хранить график отпусков

Следующим запросом пакета объединим две предыдущие таблицы в одну, и поместим результат во временную таблицу вт_ОстаткиИДвижения.

Тут следует заметить то, что это еще не окончательная таблица, а промежуточные данные, которые нам понадобятся для получения окончательного результата.

Следующим запросом пакета рассчитаем начальные остатки по регистраторам, и поместим их во временную таблицу вт_НачальныеОстаткиПоРегистраторам.

Иллюстрация расчета остатка на момент регистратора

Выполняемые в этом запросе действия уже не столь очевидны и требуют дополнительного пояснения.
Рассчет начального остатка по регистратору проиллюстрирован на следующем рисунке.

Как видно из рисунка, начальный остаток по регистратору получается путем взятия остатка на начало периода отчета, прибавления к нему всех приходов за период от начала отчета до момента регистратора, и отнимания от него всех расходов за этот же период.

Технически это реализуется следующим образом. К полученной ранее таблице вт_ОстаткиИДвижения, которая в данном запросе используется как поставщик всех попавших в отчет значений измерений, левым соединением присоединяется таблица начальных остатков. Соединение происходит по всем используемым измерениям, но без учета регистраторов. Т.е. в рамках одной комбинации значений измерений, для каждого регистратора, значение остатка на начало периода отчета получается одинаковым. Поэтому в формуле расчета начального остатка на момент регистратора, это значение берется с помощью функции МАКСИМУМ — чтобы не было необходимости включать его в список полей группировки.
Далее, к этой же таблице левым соединением присоединяется таблица движений, которые были до момента регистратора (исключительно)

вт_Движения.РегистраторМоментВремени не включала в список полей те измерения регистров, на которые мы не рассчитывали.
Отключение автозаполнения полей

Далее, для всех используемых в отчете измерений укажем роль «Измерение».
Указание роли для Измерений

Для начальных и конечных остатков ресурсов укажем соответствующую роль и принадлежность к ресурсу.
Указание роли для начальных и конечных остатков

Чтобы при настройке отчета, разные значения одного ресурса были объединены в одну группу, пропишем путь соответствующих полей через точку, указывая перед точкой желаемое имя группы.
Настройка группировки значений ресурсов
И заодно дадим значениям ресурсов более читаемые заголовки.

Аналогично поступим и с полями периода.
Настройка группировки периодов
А также, воспользуемся статьей «Форматирование на разных языках периодов в СКД», чтобы настроить красивое отображение периодов.

На закладке «Вычиляемые поля» добавим расчет полей «Свободный остаток», «Свободно в поставке» и «План».
Настройка вычисляемых полей

На закладке «Ресурсы» настроим расчет ресурсов отчета.
Обратите внимание, что для вычисляемых полей, соответствующий ресурс рассчитываем не как сумму по полю, а повторив формулу вычисления поля, взяв от каждого используемого в формуле ресурса значение суммы:
Настройка ресурсов

На закладке «Параметры» добавим доступный пользователю параметр «Период» типа СтандартныйПериод, а используемые в запросе параметры «ПериодНачало» и «ПериодОкончание» сделаем недоступными для пользователя и заполняемыми из параметра «Период».
Настройка параметров

И наконец, на закладке «Настройки», для варианта «Основной» создадим требуемую нам тестовую иерархию группировок отчета, с разворотом данных до регистратора.
Настройка тестового варианта
Обратите внимание, что теперь нет необходимости, с помощью отбора на группировке «Регистратор», скрывать значения Регистратор = Неопределено, как мы это делали в случае выборки данных из виртуальных таблиц ОстаткиИОбороты (см. «Ошибочное решение»).

Отчет готов.
Если его сохранить и запустить в демо базе, то можно увидеть что обнаруженные ранее ошибки исчезли, и теперь он корректно рассчитывает остатки.
Правильный результат отчета
И даже если в отчет добавить группировки по периодам, то можно заметить, что остатки на границы периодов тоже считаются корректно.
Правильный результат отчета с группировкой по периодам

Источник

Временные таблицы 1С

Временные таблицы часто используются как для реализации сложных запросов, так и для их оптимизации.

Временные таблицы в 1С

Временные таблицы — это объекты СУБД, никаких временных таблиц на сервере 1С нет, не путайте пожалуйста, их с таблицами значений.

Временные таблицы хранятся в объекте типа МенеджерВременныхТаблиц. Когда этот объект уничтожается, уничтожаются и временные таблицы.

Физически временные таблицы по умолчанию создаются в оперативной памяти, а именно в буферном кэше.

Конечно, есть исключения. Например, если таблица слишком большая, то сервер может принять решение сбросить ее на диск. Также возможна ситуация, когда сервер по каким-либо причинам решил отдать память под другие данные, тогда таблица тоже будет записана на диск.

Читайте также:  Найти работу по свежим вакансиям в базе данных предложений работы на Электронном Центре занятости населения ЦЗН

Почему таблица создается именно в памяти? Тут все очевидно, дело в производительности, думаю не стоит объяснять, что чтение из оперативной памяти гораздо быстрее чтения с диска, даже если этот диск SSD.
Чтобы создать временную таблицу, используется ключевое слово «ПОМЕСТИТЬ», например:

Временная таблица создается при выполнении запроса, если повторно выполнить запрос, то выдастся ошибка, что таблица уже существует.
Описания временных таблиц хранятся в свойстве запроса МенеджерВременныхТаблиц. К сожалению, нельзя получить список временных таблиц, которые хранятся в запросе.

Готовую таблицу значений из памяти можно выгружать в менеджер временных таблиц запроса. Единственное условие — колонки таблицы значений должны быть типизированными, т.е. иметь тип. Вот пример, демонстрирующий это:

Вместо перечисления списка полей можно использовать «ВЫБРАТЬ *».

Если есть ТЗ, в каждой колонке которой значения всего одного типа, для типизации колонок, можно воспользоваться функцией:

Остались вопросы? Спрашивайте в комментариях.

Источник

Временная таблица регистра накопления Остатки и обороты

Добрый день! Подскажите пожалуйста не пойму! вот код ВЫБРАТЬ
Таб.Склад КАК Склад,
Таб.Номенклатура КАК Номенклатура,
Таб.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
СУММА(Таб.КоличествоПриход) КАК КоличествоПриход,
СУММА(Таб.КоличествоРасход) КАК КоличествоРасход,
Таб.Регистратор КАК Регистратор,
СУММА(Таб.КоличествоНачальныйОстаток) КАК КоличествоНачальныйОстаток,
СУММА(Таб.КоличествоКонечныйОстаток) КАК КоличествоКонечныйОстаток,
СУММА(Таб.КоличествоОборот) КАК КоличествоОборот
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
&ДатаНачало,
&ДатаКонец,
Регистратор,
Движения,
Номенклатура = &Номенклатура
И Склад В ИЕРАРХИИ (&Склад)) КАК Таб

СГРУППИРОВАТЬ ПО
Таб.Склад,
Таб.Номенклатура,
Таб.ХарактеристикаНоменклатуры,
Таб.Регистратор
ИТОГИ
СУММА(КоличествоПриход),
СУММА(КоличествоРасход),
СУММА(КоличествоНачальныйОстаток),
СУММА(КоличествоКонечныйОстаток),
СУММА(КоличествоОборот)
ПО
ОБЩИЕ,
Склад,
Номенклатура,
ХарактеристикаНоменклатуры

Если строиль запрос так все норм, при попытке поместить во вложенный запрос не правильные итоги по ресурсу КоличествоКонечныйОстаток вот пример ВЫБРАТЬ
табл.Склад КАК Склад,
табл.Номенклатура КАК Номенклатура,
табл.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
табл.КоличествоПриход КАК КоличествоПриход,
табл.КоличествоРасход КАК КоличествоРасход,
табл.Регистратор,
табл.КоличествоНачальныйОстаток КАК КоличествоНачальныйОстаток,
табл.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
табл.КоличествоОборот КАК КоличествоОборот
ИЗ
(ВЫБРАТЬ
Таб.Склад КАК Склад,
Таб.Номенклатура КАК Номенклатура,
Таб.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
СУММА(Таб.КоличествоПриход) КАК КоличествоПриход,
СУММА(Таб.КоличествоРасход) КАК КоличествоРасход,
Таб.Регистратор КАК Регистратор,
СУММА(Таб.КоличествоНачальныйОстаток) КАК КоличествоНачальныйОстаток,
СУММА(Таб.КоличествоКонечныйОстаток) КАК КоличествоКонечныйОстаток,
СУММА(Таб.КоличествоОборот) КАК КоличествоОборот
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
&ДатаНачало,
&ДатаКонец,
Регистратор,
Движения,
Номенклатура = &Номенклатура
И Склад В ИЕРАРХИИ (&Склад)) КАК Таб

СГРУППИРОВАТЬ ПО
Таб.Склад,
Таб.Номенклатура,
Таб.ХарактеристикаНоменклатуры,
Таб.Регистратор) КАК табл
ИТОГИ
СУММА(КоличествоПриход),
СУММА(КоличествоРасход),
СУММА(КоличествоНачальныйОстаток),
СУММА(КоличествоКонечныйОстаток),
СУММА(КоличествоОборот)
ПО
ОБЩИЕ,
Склад,
Номенклатура,
ХарактеристикаНоменклатуры

Источник

Временные таблицы в запросах 1С 8.3

  1. Временная таблица
  2. Менеджер временных таблиц
  3. Индексы временных таблиц
  4. Получить данные временной таблицы

Временная таблица

Временные таблицы можно использовать, чтобы не выбирать данные из какой-то таблицы несколько раз. Один раз выбрали их, поместили во временную таблицу и дальше используем уже временную таблицу.

Создается временная таблица с помощью ключевого слова ПОМЕСТИТЬ:

В данном примере выборка из справочника Бренды будет помещена во временную таблицу ВТБренды, а уже потом данные будут выбраны из временной таблицы.

При создании новой временной таблицы она не сохраняется в базе данных. В файловой базе временные таблицы хранятся в оперативной памяти, в MS SQL Server в специальной служебной базе TempDB.

Также временные таблицы можно использовать для оптимизации работы СУБД. Если вместо временной таблицы использовать вложенный запрос, то оптимизатор не будет знать сколько строк в этой таблице и не сможет оптимизировать план запроса. В случае использования временной таблицы количество строк будет известно, что позволит лучше оптимизировать план запроса.

Но у временных таблиц есть и свои минусы:

  • Во-первых на создание временной таблицы тратится время.
  • Во-вторых, они могут занимать много места на диске.

Менеджер временных таблиц

Менеджер временных таблиц позволяет управлять временными таблицами. Один менеджер временных таблиц может управлять несколькими временными таблицами, но имена временных таблиц в пределах одного менеджера не должны совпадать.

Создать менеджер временных таблиц можно с помощью конструктора:

Источник

Adblock
detector