1С запрос нарастающим итогом

Запроса 1с – сумма нарастающим итогом.
Получить в запросе 1с сумму нарастающим итогом мне понадобилось всего однажды, но алгоритм оказался для меня не тривиальным. Делюсь, может кому пригодится.

Задание: Получить список документов "Поступление денег" и сумму поступлений нарастающим итогом.

Описание алгоритма:
Хронологию документов определяет стандартный реквизит "МоментВремени". Нарастающий итог делается левым соединением временной таблицы самой к себе по условию сравнения моментов времени. Таким образом для каждой записи первой ВТ отбираются все записи хронологически стоящие до неё. Группировкий получаем сумму нарастаюшего итога. Обратите внимание на условие левого соединения во втором пакете. В нем вся хитрость.

Пример:
// Выбираем все нужные записи во временную таблицу
ВЫБРАТЬ
&nbsp&nbsp&nbsp ПоступлениеДенег . Ссылка ,
&nbsp&nbsp&nbsp ПоступлениеДенег . Сумма ,
&nbsp&nbsp&nbsp ПоступлениеДенег . МоментВремени
ПОМЕСТИТЬ т1
ИЗ
&nbsp&nbsp&nbsp Документ . ПоступлениеДенег КАК ПоступлениеДенег
ГДЕ
&nbsp&nbsp&nbsp ПоступлениеДенег . Валюта = &ampВалюта
;
////////////////////////////////////////////////////////////////////////////////
// Левое соединение временной таблицы самой к себе, группировка и сортировка.
ВЫБРАТЬ
&nbsp&nbsp&nbsp т1 . Ссылка ,
&nbsp&nbsp&nbsp т1 . Сумма ,
&nbsp&nbsp&nbsp т1 . МоментВремени КАК МоментВремени ,
&nbsp&nbsp&nbsp СУММА ( т2 . Сумма ) КАК СуммаНарастающимИтогом
ИЗ
&nbsp&nbsp&nbsp т1 КАК т1
&nbsp&nbsp&nbsp &nbsp&nbsp&nbsp ЛЕВОЕ СОЕДИНЕНИЕ т1 КАК т2
&nbsp&nbsp&nbsp &nbsp&nbsp&nbsp ПО т1 . МоментВремени &gt = т2 . МоментВремени
СГРУППИРОВАТЬ ПО
&nbsp&nbsp&nbsp т1 . Ссылка ,
&nbsp&nbsp&nbsp т1 . Сумма ,
&nbsp&nbsp&nbsp т1 . МоментВремени
УПОРЯДОЧИТЬ ПО
&nbsp&nbsp&nbsp МоментВремени

воскресенье, 11 февраля 2018 г.

Запрос с нарастающим итогом

1. Нужно отобрать необходимые обороты (в данном случае "Приход") из регистра и поместить их во временную таблицу к которой будем обращаться.
2. Выбрать 2 раза получившуюся таблицу и соединить по внутреннему соединению по необходимым полям и периоду через ">="
3. Применить необходимую группировку
4. Упорядочить по вкусу.

ВЫБРАТЬ
ВзаиморасчетыОбороты.Период,
ВзаиморасчетыОбороты.Регистратор,
ВзаиморасчетыОбороты.Контрагент,
ВзаиморасчетыОбороты.СуммаПриход
ПОМЕСТИТЬ ВТ_Источник
ИЗ
РегистрНакопления.Взаиморасчеты.Обороты(, , Регистратор, ) КАК ВзаиморасчетыОбороты
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Источник.Период КАК Период,
ВТ_Источник.Регистратор,
ВТ_Источник.Контрагент КАК Контрагент,
ВТ_Источник.СуммаПриход,
СУММА(ВТ_Источник1.СуммаПриход) КАК СуммаНарастающегоИтога
ИЗ
ВТ_Источник КАК ВТ_Источник
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Источник КАК ВТ_Источник1
ПО ВТ_Источник.Период >= ВТ_Источник1.Период
И ВТ_Источник.Контрагент = ВТ_Источник1.Контрагент

СГРУППИРОВАТЬ ПО
ВТ_Источник.Период,
ВТ_Источник.Регистратор,
ВТ_Источник.Контрагент,
ВТ_Источник.СуммаПриход

УПОРЯДОЧИТЬ ПО
Контрагент,
Период

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

Фиксится данная проблема путем наложения более конкретного условия:

Если даты совпадают то происходит отработка по более конкретному условия – "МоментВремени" из регистратора.

ВЫБРАТЬ
ВзаиморасчетыОбороты.Период,
ВзаиморасчетыОбороты.Регистратор,
ВзаиморасчетыОбороты.Контрагент,
ВзаиморасчетыОбороты.СуммаПриход
ПОМЕСТИТЬ ВТ_Источник
ИЗ
РегистрНакопления.Взаиморасчеты.Обороты(, , Регистратор, ) КАК ВзаиморасчетыОбороты
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Источник.Период КАК Период,
ВТ_Источник.Регистратор,
ВТ_Источник.Контрагент КАК Контрагент,
ВТ_Источник.СуммаПриход,
СУММА(ВТ_Источник1.СуммаПриход) КАК СуммаНарастающегоИтога
ИЗ
ВТ_Источник КАК ВТ_Источник
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Источник КАК ВТ_Источник1
ПО (ВТ_Источник.Период > ВТ_Источник1.Период
ИЛИ ВТ_Источник.Период = ВТ_Источник1.Период
И ВТ_Источник.Регистратор.МоментВремени >= ВТ_Источник1.Регистратор.МоментВремени)
И ВТ_Источник.Контрагент = ВТ_Источник1.Контрагент

СГРУППИРОВАТЬ ПО
ВТ_Источник.Период,
ВТ_Источник.Регистратор,
ВТ_Источник.Контрагент,
ВТ_Источник.СуммаПриход

УПОРЯДОЧИТЬ ПО
Контрагент,
Период

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

Вот примеры таких задач:

• Получение нарастающего объема продаж по месяцам, например, для вывода в виде диаграммы
• Расчет разницы продаж текущего и предыдущего периода – для всех строк отчета
• Сравнение объема продаж каждого менеджера с эталонным значением, которое вычисляется в ходе формирования этого отчета
• Получение нарастающего итога по горизонтали и вертикали в кросс-таблицах

И все эти задачи решаются элементарно с помощью функций СКД – буквально за пару минут. Без запросов в цикле и кривого кода.

Сегодня рассмотрим использование некоторых функций СКД на конкретных примерах.

Видео 1. Как получить сумму в отчете нарастающим итогом?

Подобные задачи часто встречаются на практике. Наиболее типичный пример – в отчет должны выводиться продажи по периодам, необходимо знать не только сумму продаж текущего месяца, но и нарастающим итогом с начала формирования отчета.

На самом деле эту задачу можно решить и с помощью запроса (без функций СКД), но есть 2 проблемы:

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

С помощью функций СКД эта задача решается одной строчкой кода, и отчет будет работать максимально быстро, смотрим:

Видео 2. Как получить значение из предыдущей строки отчета?

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

Приведем несколько примеров, когда это может быть полезно:

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

Такие задачи также трудоемко решить с помощью запроса, а в СКД это делается одной строкой:

Видео 3. Как сравнить итог по текущей строке с эталонным значением?

Пример, когда этот функционал может быть полезен:

• В системе ведется учет продаж менеджерами
• Руководитель отдела ведет личные продажи наряду с менеджерами
• Требуется вывести отчет по продажам в разрезе менеджеров
• В отчет также необходимо выводить дельту относительно объема продаж руководителя отдела.

То есть речь о том, что нужно сравнить каждую строку продаж менеджера с продажами руководителя отдела.

А эти данные могут, например, использоваться для начисления премии.

И опять же с помощью СКД эта задача решается в одну строку:

Видео 4. Получение нарастающего итога в кросс-таблице

В отчете вида кросс-таблица нарастающий итог может считаться как вертикально (по строкам), так и горизонтально (по столбцам).

Мы покажем, как с помощью одного параметра СКД управлять направлением расчета итогов:

Видео 5. Вывод табличной части в отдельной ячейке отчета

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

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

В СКД эта задача вновь решается одной функцией:

Чтобы не изобретать велосипед при разработке отчетов, рекомендуем изучить полный функционал Системы компоновки данных.

Эта тема детально раскрыта в курсе:

Поддержка – 2 месяца. Объем курса – 34 учебных часа.

Не откладывайте свое обучение!

Комментарии / обсуждение (48):

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

Добрый день!
На закладке Ресурсы описываем два выражения для расчета ресурсов. Одно из них – для общего итога:

Вот такое выражение получается – используем две функции (ВычислитьВыражениеСГруппировкойМассив и ВычислитьВыражение):

Добрый день Василий!

Проходил ваши курсы по СКД и Запросам, курсы очень классные, всем рекомендую.

Сейчас решаю одну задачку строю отчет на СКД, хочу получить отчет по дебиторской задолженности
на конец каждого месяца (не дня, как у вас пример в курсе по товарным остаткам которые получаются на каждый день),
а именно месяца, строю кросс таблицу в строках партнеры, а в колонках месяцы, и ничего не получается,
например взял клиент товар в январе образовалась дебиторская задолженность 15000, задаю период в отчете
1кв ,получается вот такая картина:
|январь|февраль|март |
|30000 |15000 |15000 |
в январе сумма задваивается почему не пойму, если сделать документ корректировку реализации в июне
к примеру в сторону увеличения на 5000, то логично получить вот такую таблицу:

|январь|февраль|март |апрель|май |июнь |
|15000 |15000 |15000 |15000 |15000|20000 |, а получается вот так:

|январь|февраль|март |апрель|май |июнь |
|35000 |20000 |20000 |20000 |20000|20000 |

суммы вообще не те…
Отчет делаю в УТ 11.4, ниже привожу запрос:

ВЫБРАТЬ
0 КАК Поле1
ПОМЕСТИТЬ ВТ_Цифра

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
РасчетыСКлиентамиОстаткиИОбороты.Период КАК Месяц,
ЕСТЬNULL(РасчетыСКлиентамиОстаткиИОбороты.СуммаНачальныйОстаток, 0) КАК НачОстаток,
ЕСТЬNULL(РасчетыСКлиентамиОстаткиИОбороты.СуммаКонечныйОстаток, 0) КАК КонОстаток,
РасчетыСКлиентамиОстаткиИОбороты.АналитикаУчетаПоПартнерам.Партнер КАК Партнер
ПОМЕСТИТЬ Вт_Остатки
ИЗ
РегистрНакопления.РасчетыСКлиентами.ОстаткиИОбороты(, , Месяц, , ) КАК РасчетыСКлиентамиОстаткиИОбороты
;

Добрый день!
Давайте попробуем решить эту задачу при помощи двух связанных наборов данных. В первом наборе данных выберем все даты, на которые нужно получить остатки. Во втором наборе данных получаем остатки по регистру накопления РасчетыСКлиентами на дату, указанную в параметре. Связываем эти наборы данных, дату из первого набора передаем в параметр второго набора. Подобный прием рассматривался в курсе по СКД.

Добрый день Василий!
Спасибо за ответ!
Василий подскажите пожалуйста когда то проходил Ваш курс по запросам, курс супер, там есть урок и пример получение остатков на каждый день, сейчас решил освежить свою память и попытался воспроизвести этот пример только я вывожу еще и начальный остаток, приход, расход и конечный остаток (у вас в уроке только конечный остаток), но столкнулся с некоторыми трудностями когда выводишь данные с 01.07 по 31.07, то если в 31.07 были движения в данные выборки они не попадают к примеру остаток на начало 30.07 был 430 потом приход 100 далее расход 3 остаток на конец 30.07 527, тогда на начало 31.07 527 прихода не было расход 10 остаток на конец 31.07 517, а у меня получается две строки с датой 31.07:
|31.07.2019 |Товар1 |430|100|3 |527| – это строка ошибочная дубль даты 30.07
|31.07.2019 |Товар1|527|0 |10|517|
подскажите пожалуйста, что я делаю не так? Ниже привожу текст запроса:
ВЫБРАТЬ
0 КАК Поле1
ПОМЕСТИТЬ ВТ_Цифра

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

Добрый день!
1. Посмотрите, пожалуйста, комментарий к курсу по запросам:
https://xn—-1-bedvffifm4g.xn--p1ai/1c-v8/development-optimize-queries/startpage/module09/comment-page-3/#comment-755210
В нем приводятся изменения для текста запроса, попробуйте у себя их же применить.

2. Попробуйте решить эту задачу не при помощи одного большого запроса, а при помощи СКД – при помощи соединения двух наборов данных. В первом наборе данных будет перечень дат. Для каждой из этих дат во втором запросе получаем данные при помощи виртуальной таблицы ОстаткиИОбороты.

Круто огромное спасибо

Добрый день.
Скажите, а как изменить запрос СКД, если мне нужно выводить динамику (скажем, курсов валют по дням) не по одной валюте, а по всем?

Добрый день!
Можно использовать простой текст запроса набора данных:

Поле Курс определяем как ресурс.
В настройках компоновки добавим диаграмму (тип диаграммы – график), у которой в точках будет Период, а в сериях – Валюта:

В таком случае получим график изменения курса валют по дням.

Добрый день. Как можно рассчитать разницу между продажами за текущий месяц и такой же месяц предыдущего года ?

Добрый день!
Можно использовать объединение двух наборов данных – запросов. В каждом из них будут одинаковые тексты запросов, но разные параметры – периоды, задающие интервал времени для получения из базы данных о продажах.
В курсе по СКД рассматривается пример такого отчета.

Добрый день. У Вас очень много очень полезных уроков, но найти их в общем списке крайне тяжело. Данные уроки пришлось искать полчаса путем просматривания всех новостей за полгода.
К чему я: ну очень хотелось бы какую-то систематизацию данных для поиска подобных уроков. Или чтобы я мог сам для себя сохранить в личном кабинете интересующие для себя ссылки на новости со своими комментариями.

Проходите курсы – там раз в 20 больше, строго по порядку и систематизировано.

А примеры.. – на то они и примеры, чтобы просто показывать примеры 🙂

Спасибо за примеры,
жаль что их так мало у 1С.

Вернуться к вашей публикации меня натолкнула интересная комбинация с итогами в СКД, для которой пока не удалось найти решение, допускаю что Вы его знаете.
Отчет содержит несколько вертикальных итогов, к которым нужно добавить еще один итог, который должен отображаться не самого нижнего уровня из 5-ти существующих, а с 3-го где данные нижних уровней не должны участвовать в этом итоге, данные для данного итога формируются отдельным запросом и присоединяются левым соединением к основному запросу. Для требуемого итога была использована следующее выражение в ресурсах: Сумма(ВычислитьВыражениеСГруппировкойМассив(“Остаток”, “Валюта”))
где “Валюта” и есть та группировка с которой нужно начать считать итоги. Отчет правильно работал до тех пока была только одна валюта, как только валют стало больше, результат становился не верным и не объяснимым с точки зрения арифметики, соотношение между валютами примерно соблюдалось, но сами цифры отличались например в 2,28 раза. Учитывая, что нужно формировать итоги для данных из левого соединения была сделана попытка решить задачу через связанные наборы данных, но результат был точно таким же.
Буду Вам очень благодарен если подскажите хотя бы идею решения. Спасибо!

Добрый день!
Затрудняюсь однозначно подсказать вам. Я бы попробовал первым делом добавить соединение в запрос или соединение с дополнительным набором данных, собственно как вы и пишете. Далее проверял бы сами данные, которые возвращает запрос. Т.е. в настройках компоновки делаю одну группировку – детальные записи. Анализирую, какой получается результат. Все ли данные корректно выбираются из базы, нет ли задвоений и т.д. После этого добавлять группировки, смотреть, какой результат получается, как отрабатывают выражения для ресурсов. Вот в таком направлении я бы пробовал решать задачу.

Огромное спасибо за видео уроки.С удовольствием посмотрел, очень познавательно. У меня вопрос: “Почему при условном оформлении вычисляемых (и пользовательских) полей выдаётся ошибка “Поле не найдено.”?”. Непонятно,чем отличается вычисляемое (или пользователькое) поле?

Добрый день!
Подобная ошибка может возникать, когда для вычисляемого поля не задается выражение, а только выражение на закладке Ресурсы. Получается, что на уровне детальных записей такое вычисляемое поле не определено, оно существует только на уровне группировки. Поэтому, когда мы пытаемся использовать это поле в детальных записях, то возникает такая ошибка.
Пользовательское поле можно создать самостоятельно и при работе с отчетом в режиме “1С:Предприятие”, а вычисляемое поле может создать только разработчик при создании схемы компоновки данных.

Объясните, пожалуйста: почему нужно накладывать именно на уровне ресурсов все выражения? Не могу понять ключевые различия между описанием функции в вычисляемом поле и на уровне ресурса.

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