Створення звіту із залишками без віртуальних таблиць

Іноді потрібно створити звіт, до якого необхідно вивести дані щодо залишків, але використати віртуальну таблицю ЗалишкиЙОбороти регістру накопичення або бухгалтерії з будь-яких причин не вдається. Наприклад, дані, які потрібно вивести у звіт, не зберігаються в регістрі. У цій статті розповідається, яким чином організувати набір даних, щоб використовувати можливості системи компонування даних для роботи із залишками.

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

Як приклад створимо простий звіт про заборгованість покупців без використання регістрів.

Дані для нашого звіту отримуватимемо з таких таблиць:

Документ.ВидатокТовару — містить операції продажів. Реквізити:

• Покупець — містить покупця;
• Сума — містить суму документа.

Документ.НадходженняГрошей — містить оплати клієнтами. Реквізити:

• Покупець — покупець, який здійснив оплату;
• Сума — сума оплати;
• ВидатковийДокумент — документ, за який здійснено оплату.

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

У звіт потрібно вивести дані щодо дебіторської заборгованості за днями, за зазначений період.

Дані для такого звіту отримуватимемо за допомогою запиту.

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

ВИБРАТИ

 ВидатокТовару.Посилання ЯК ВидатковийДокумент,

 ВидатокТовару.Дата ЯК ПеріодВидатковогоДокумента,

 ВидатокТовару.Сума ЯК СумаВидатковогоДокумента

ПОМІСТИТИ ВидатковіДокументи

З

 Документ.ВидатокТовару ЯК ВидатокТовару

  ЛІВЕ З'ЄДНАННЯ Документ.НадходженняГрошей ЯК НадходженняГрошей

  ЗА (НадходженняГрошей.ВидатковийДокумент = ВидатокТовару.Посилання)

   І (НадходженняГрошей.Дата < &ПочатокПеріоду)

ДЕ

 ВидатокТовару.Дата < &ПочатокПеріоду

 

ЗГРУПУВАТИ ЗА

 ВидатокТовару.Посилання,

 ВидатокТовару.Дата,

 ВидатокТовару.Сума

 

ЩО МАЮТЬ

 МАКСИМУМ(ВидатокТовару.Сума) - Є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) ЯК ПочатковийЗалишок,

  ДатиРухів.ВидатковийДокументПокупець ЯК ВидатковийДокументПокупець

 {ВИБРАТИ

  ВидатковийДокументПокупець}

 З

  ДатиРухів ЯК ДатиРухів

   ЛІВЕ З'ЄДНАННЯ ВидатковіДокументи ЯК ВидатковіДокументи

   ЗА (ВидатковіДокументи.ПеріодВидатковогоДокумента < ДатиРухів.Період)

    І ДатиРухів.ВидатковийДокументПокупець = ВидатковіДокументи.ВидатковийДокумент.Покупець

   ЛІВЕ З'ЄДНАННЯ Документ.НадходженняГрошей ЯК НадходженняГрошей

   ЗА (НадходженняГрошей.Дата < ДатиРухів.Період)

    І (ВидатковіДокументи.ВидатковийДокумент = НадходженняГрошей.ВидатковийДокумент)

 

 ЗГРУПУВАТИ ЗА

  ДатиРухів.Період,

  ВидатковіДокументи.ВидатковийДокумент,

  ДатиРухів.ВидатковийДокументПокупець) ЯК ТимчасовийЗапит

  ВНУТРІШНЄ З'ЄДНАННЯ ДатиРухів ЯК ДатиРухів

  ЗА ТимчасовийЗапит.Період = ДатиРухів.Період

   І ТимчасовийЗапит.ВидатковийДокументПокупець = ДатиРухів.ВидатковийДокументПокупець

 

ЗГРУПУВАТИ ЗА

 ТимчасовийЗапит.Період,

 ТимчасовийЗапит.ВидатковийДокументПокупець

Створимо схему компонування даних, додамо до неї набір даних — запит і вставимо до нього текст запиту.

Установимо ролі полів:

• Полю "КінцевийЗалишок" установимо роль "Залишок", з типом "Кінцевий залишок", як ім'я групи введемо "Сума".
• Полю "ПочатковийЗалишок" установимо роль "Залишок", з типом "Початковий залишок", як ім'я групи також введемо "Сума".
• Полю "Період" зазначимо роль "Період", а також зазначимо, що поле є обов'язковим. Обов'язковим ми його робимо тому, що весь наш запит побудований на отриманні періоду та зв'язку з ним документів видатку й оплати.
• Полю "ВидатковийДокументПокупець" установимо роль "Вимір".

Помістимо на вкладці "Ресурси" за допомогою кнопки ">>" усі числові поля до списку ресурсів.

Як налаштування за замовчуванням до звіту помістимо таблицю, у рядках якої виведемо період, а в колонках — покупця.

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

Приклад результату звіту:

Див. також розділи "Доповнення періодів у системі компонування даних", "Особливості розрахунку підсумків за полями залишку. Використання реквізитів вимірів.", "Типові проблеми під час розрахунку залишків".