18.08.2010

Создание отчета с остатками без виртуальных таблиц

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

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

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

Данные для нашего отчета будем получать из следующих таблиц:

Документ.РасходТовара - содержит операции продаж. Реквизиты:

Покупатель - содержит покупателя;
Сумма - содержит сумму документа.

Документ.ПоступлениеДенег - содержит оплаты клиентами. Реквизиты:

Покупатель - покупатель, выполнивший оплату;
Сумма - сумма оплаты;
РасходныйДокумент - документ, за который выполнена оплата.

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

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

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

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

Копировать в буфер обмена

ВЫБРАТЬ
 РасходТовара.Ссылка КАК РасходныйДокумент,
 РасходТовара.Дата КАК ПериодРасходногоДокумента,
 РасходТовара.Сумма КАК СуммаРасходногоДокумента
ПОМЕСТИТЬ РасходныеДокументы
ИЗ
 Документ.РасходТовара КАК РасходТовара
  ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоступлениеДенег КАК ПоступлениеДенег
  ПО (ПоступлениеДенег.РасходныйДокумент = РасходТовара.Ссылка)
   И (ПоступлениеДенег.Дата < &НачалоПериода)
ГДЕ
 РасходТовара.Дата < &НачалоПериода
 
СГРУППИРОВАТЬ ПО
 РасходТовара.Ссылка,
 РасходТовара.Дата,
 РасходТовара.Сумма
 
ИМЕЮЩИЕ
 МАКСИМУМ(РасходТовара.Сумма) - ЕСТЬNULL(СУММА(ПоступлениеДенег.Сумма), 0) <> 0
 
ОБЪЕДИНИТЬ
 
ВЫБРАТЬ
 РасходТовара.Ссылка,
 РасходТовара.Дата,
 РасходТовара.Сумма
ИЗ
 Документ.РасходТовара КАК РасходТовара
ГДЕ
 РасходТовара.Дата >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
 И РасходТовара.Дата <= КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)
;

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

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

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

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

Копировать в буфер обмена

ВЫБРАТЬ
 ВЫБОР
  КОГДА ВложенныйЗапрос.Период < НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
   ТОГДА НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
  ИНАЧЕ ВложенныйЗапрос.Период
 КОНЕЦ КАК Период,
 ЕСТЬNULL(СУММА(ВЫБОР
    КОГДА ВложенныйЗапрос.Период >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
     ТОГДА ВложенныйЗапрос.СуммаРасходныхДокументов
   КОНЕЦ), 0) КАК СуммаРасходныхДокументов,
 ЕСТЬNULL(СУММА(ВЫБОР
    КОГДА ВложенныйЗапрос.Период >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
     ТОГДА ВложенныйЗапрос.СуммаПоступления
   КОНЕЦ), 0) КАК СуммаПоступления,
 ВложенныйЗапрос.РасходныйДокументПокупатель
ПОМЕСТИТЬ ДатыДвижений
ИЗ
 (ВЫБРАТЬ
  НАЧАЛОПЕРИОДА(РасходныеДокументы.ПериодРасходногоДокумента, ДЕНЬ) КАК Период,
  РасходныеДокументы.СуммаРасходногоДокумента КАК СуммаРасходныхДокументов,
  NULL КАК СуммаПоступления,
  РасходныеДокументы.РасходныйДокумент.Покупатель КАК РасходныйДокументПокупатель
 ИЗ
  РасходныеДокументы КАК РасходныеДокументы
 
 ОБЪЕДИНИТЬ
 
 ВЫБРАТЬ
  НАЧАЛОПЕРИОДА(ПоступлениеДенег.Дата, ДЕНЬ),
  NULL,
  ПоступлениеДенег.Сумма,
  ПоступлениеДенег.РасходныйДокумент.Покупатель
 ИЗ
  Документ.ПоступлениеДенег КАК ПоступлениеДенег
 ГДЕ
  ПоступлениеДенег.Дата >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
  И ПоступлениеДенег.Дата <= КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)) КАК ВложенныйЗапрос
 
СГРУППИРОВАТЬ ПО
 ВложенныйЗапрос.РасходныйДокументПокупатель,
 ВЫБОР
  КОГДА ВложенныйЗапрос.Период < НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
   ТОГДА НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
  ИНАЧЕ ВложенныйЗапрос.Период
 КОНЕЦ
;

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

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

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

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

При помощи следующего запроса мы получим для каждого покупателя и периода остатки и обороты:

Копировать в буфер обмена

ВЫБРАТЬ
 ВременныйЗапрос.Период,
 ВременныйЗапрос.РасходныйДокументПокупатель,
 СУММА(ВременныйЗапрос.НачальныйОстаток) КАК НачальныйОстаток,
 МАКСИМУМ(ДатыДвижений.СуммаРасходныхДокументов) КАК СуммаРасходныхДокументов,
 МАКСИМУМ(ДатыДвижений.СуммаПоступления) КАК СуммаПоступления,
 СУММА(ВременныйЗапрос.НачальныйОстаток) + МАКСИМУМ(ДатыДвижений.СуммаРасходныхДокументов) - МАКСИМУМ(ДатыДвижений.СуммаПоступления) КАК КонечныйОстаток
ИЗ
 (ВЫБРАТЬ
  ДатыДвижений.Период КАК Период,
  РасходныеДокументы.РасходныйДокумент КАК РасходныйДокумент,
  ЕСТЬNULL(МАКСИМУМ(РасходныеДокументы.СуммаРасходногоДокумента) - ЕСТЬNULL(СУММА(ПоступлениеДенег.Сумма), 0), 0) КАК НачальныйОстаток,
  ДатыДвижений.РасходныйДокументПокупатель КАК РасходныйДокументПокупатель
 ИЗ
  ДатыДвижений КАК ДатыДвижений
   ЛЕВОЕ СОЕДИНЕНИЕ РасходныеДокументы КАК РасходныеДокументы
   ПО (РасходныеДокументы.ПериодРасходногоДокумента < ДатыДвижений.Период)
    И ДатыДвижений.РасходныйДокументПокупатель = РасходныеДокументы.РасходныйДокумент.Покупатель
   ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоступлениеДенег КАК ПоступлениеДенег
   ПО (ПоступлениеДенег.Дата < ДатыДвижений.Период)
    И (РасходныеДокументы.РасходныйДокумент = ПоступлениеДенег.РасходныйДокумент)
 
 СГРУППИРОВАТЬ ПО
  ДатыДвижений.Период,
  РасходныеДокументы.РасходныйДокумент,
  ДатыДвижений.РасходныйДокументПокупатель) КАК ВременныйЗапрос
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ ДатыДвижений КАК ДатыДвижений
  ПО ВременныйЗапрос.Период = ДатыДвижений.Период
   И ВременныйЗапрос.РасходныйДокументПокупатель = ДатыДвижений.РасходныйДокументПокупатель
 
СГРУППИРОВАТЬ ПО
 ВременныйЗапрос.Период,
 ВременныйЗапрос.РасходныйДокументПокупатель

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

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

Запрос для получения данных готов.

Подготовим его для работы системы компоновки данных.

В случае, если в отчет не нужно будет выдавать данные по покупателю, нужно будет обеспечить удаление данного поля из вложенных запросов. Для этого в текст запроса добавим инструкции компоновки данных {ВЫБРАТЬ РасходныйДокументПокупатель} во вложенные запросы. Таким образом, мы указываем системе компоновки данных, что поле РасходныйДокументПокупатель нужно добавлять в список выборки только в случае если это поле будет задействовано для вывода данных в отчет.

Результирующий запрос для схемы компоновки данных будет выглядеть следующим образом:

Копировать в буфер обмена

ВЫБРАТЬ
 РасходТовара.Ссылка КАК РасходныйДокумент,
 РасходТовара.Дата КАК ПериодРасходногоДокумента,
 РасходТовара.Сумма КАК СуммаРасходногоДокумента
ПОМЕСТИТЬ РасходныеДокументы
ИЗ
 Документ.РасходТовара КАК РасходТовара
  ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоступлениеДенег КАК ПоступлениеДенег
  ПО (ПоступлениеДенег.РасходныйДокумент = РасходТовара.Ссылка)
   И (ПоступлениеДенег.Дата < &НачалоПериода)
ГДЕ
 РасходТовара.Дата < &НачалоПериода
 
СГРУППИРОВАТЬ ПО
 РасходТовара.Ссылка,
 РасходТовара.Дата,
 РасходТовара.Сумма
 
ИМЕЮЩИЕ
 МАКСИМУМ(РасходТовара.Сумма) - ЕСТЬNULL(СУММА(ПоступлениеДенег.Сумма), 0) <> 0
 
ОБЪЕДИНИТЬ
 
ВЫБРАТЬ
 РасходТовара.Ссылка,
 РасходТовара.Дата,
 РасходТовара.Сумма
ИЗ
 Документ.РасходТовара КАК РасходТовара
ГДЕ
 РасходТовара.Дата >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
 И РасходТовара.Дата <= КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)
;
 
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
 ВЫБОР
  КОГДА ВложенныйЗапрос.Период < НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
   ТОГДА НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
  ИНАЧЕ ВложенныйЗапрос.Период
 КОНЕЦ КАК Период,
 ЕСТЬNULL(СУММА(ВЫБОР
    КОГДА ВложенныйЗапрос.Период >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
     ТОГДА ВложенныйЗапрос.СуммаРасходныхДокументов
   КОНЕЦ), 0) КАК СуммаРасходныхДокументов,
 ЕСТЬNULL(СУММА(ВЫБОР
    КОГДА ВложенныйЗапрос.Период >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
     ТОГДА ВложенныйЗапрос.СуммаПоступления
   КОНЕЦ), 0) КАК СуммаПоступления,
 ВложенныйЗапрос.РасходныйДокументПокупатель
ПОМЕСТИТЬ ДатыДвижений
ИЗ
 (ВЫБРАТЬ
  НАЧАЛОПЕРИОДА(РасходныеДокументы.ПериодРасходногоДокумента, ДЕНЬ) КАК Период,
  РасходныеДокументы.СуммаРасходногоДокумента КАК СуммаРасходныхДокументов,
  NULL КАК СуммаПоступления,
  РасходныеДокументы.РасходныйДокумент.Покупатель КАК РасходныйДокументПокупатель
 {ВЫБРАТЬ
  РасходныйДокументПокупатель}
 ИЗ
  РасходныеДокументы КАК РасходныеДокументы
 
 ОБЪЕДИНИТЬ
 
 ВЫБРАТЬ
  НАЧАЛОПЕРИОДА(ПоступлениеДенег.Дата, ДЕНЬ),
  NULL,
  ПоступлениеДенег.Сумма,
  ПоступлениеДенег.РасходныйДокумент.Покупатель
 ИЗ
  Документ.ПоступлениеДенег КАК ПоступлениеДенег
 ГДЕ
  ПоступлениеДенег.Дата >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
  И ПоступлениеДенег.Дата <= КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)) КАК ВложенныйЗапрос
 
СГРУППИРОВАТЬ ПО
 ВложенныйЗапрос.РасходныйДокументПокупатель,
 ВЫБОР
  КОГДА ВложенныйЗапрос.Период < НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
   ТОГДА НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)
  ИНАЧЕ ВложенныйЗапрос.Период
 КОНЕЦ
;
 
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
 ВременныйЗапрос.Период,
 ВременныйЗапрос.РасходныйДокументПокупатель,
 СУММА(ВременныйЗапрос.НачальныйОстаток) КАК НачальныйОстаток,
 МАКСИМУМ(ДатыДвижений.СуммаРасходныхДокументов) КАК СуммаРасходныхДокументов,
 МАКСИМУМ(ДатыДвижений.СуммаПоступления) КАК СуммаПоступления,
 СУММА(ВременныйЗапрос.НачальныйОстаток) + МАКСИМУМ(ДатыДвижений.СуммаРасходныхДокументов) - МАКСИМУМ(ДатыДвижений.СуммаПоступления) КАК КонечныйОстаток
ИЗ
 (ВЫБРАТЬ
  ДатыДвижений.Период КАК Период,
  РасходныеДокументы.РасходныйДокумент КАК РасходныйДокумент,
  ЕСТЬNULL(МАКСИМУМ(РасходныеДокументы.СуммаРасходногоДокумента) - ЕСТЬNULL(СУММА(ПоступлениеДенег.Сумма), 0), 0) КАК НачальныйОстаток,
  ДатыДвижений.РасходныйДокументПокупатель КАК РасходныйДокументПокупатель
 {ВЫБРАТЬ
  РасходныйДокументПокупатель}
 ИЗ
  ДатыДвижений КАК ДатыДвижений
   ЛЕВОЕ СОЕДИНЕНИЕ РасходныеДокументы КАК РасходныеДокументы
   ПО (РасходныеДокументы.ПериодРасходногоДокумента < ДатыДвижений.Период)
    И ДатыДвижений.РасходныйДокументПокупатель = РасходныеДокументы.РасходныйДокумент.Покупатель
   ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоступлениеДенег КАК ПоступлениеДенег
   ПО (ПоступлениеДенег.Дата < ДатыДвижений.Период)
    И (РасходныеДокументы.РасходныйДокумент = ПоступлениеДенег.РасходныйДокумент)
 
 СГРУППИРОВАТЬ ПО
  ДатыДвижений.Период,
  РасходныеДокументы.РасходныйДокумент,
  ДатыДвижений.РасходныйДокументПокупатель) КАК ВременныйЗапрос
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ ДатыДвижений КАК ДатыДвижений
  ПО ВременныйЗапрос.Период = ДатыДвижений.Период
   И ВременныйЗапрос.РасходныйДокументПокупатель = ДатыДвижений.РасходныйДокументПокупатель
 
СГРУППИРОВАТЬ ПО
 ВременныйЗапрос.Период,
 ВременныйЗапрос.РасходныйДокументПокупатель

Создадим схему компоновки данных, добавим в нее набор данных - запрос и вставим в него текст запроса.

Проставим роли полей:

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

Поместим на закладке "Ресурсы", при помощи кнопки ">>", все числовые поля в список ресурсов.

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

Схема компоновки данных готова.

Пример результата отчета:


Параметры данных:	Начало периода = 01.12.2005 0:00:00
	Конец периода = 31.01.2006 0:00:00

Период	Магазин "Обувь"				Попов Б.В. ИЧП				Итого
Период	Начальный остаток	Сумма поступления	Сумма расходных документов	Конечный остаток	Начальный остаток	Сумма поступления	Сумма расходных документов	Конечный остаток	Начальный остаток	Сумма поступления	Сумма расходных документов	Конечный остаток
15.12.2005			2 500	2 500							2 500	2 500
20.12.2005	2 500	1 500		1 000					2 500	1 500		1 000
07.01.2006	1 000			1 000			2 200	2 200	1 000		2 200	3 200
15.01.2006	1 000	700		300	2 200			2 200	3 200	700		2 500
18.01.2006	300		5 000	5 300	2 200			2 200	2 500		5 000	7 500
Итого		2 200	7 500	5 300			2 200	2 200		2 200	9 700	7 500

См. также разделы "Дополнение периодов в системе компоновки данных", "Особенности расчета итогов по полям остатка. Использование реквизитов измерений.", "Типичные проблемы при расчете остатков".