В целях демонстрации решения задачи разработана простая конфигурация, состоящая из двух документов, одного справочника, одного регистра накопления вида "Остатки" и отчета.
В принципы ведения учета в базе с этой конфигурацией вникать не нужно, это просто шуточные данные для демонстрации.
А вот принципы построения отчета можно применять в любой конфигурации для решения подобной задачи.
Кратко о конфигурации:
В справочник заносятся организации, документом "Поступление (списание) денежных средств" отражается факт поступления или списания денежных средств на [условный] счет организации, документом "Черная прибыль" дополнительно вносятся к учету некоторые средства.
Итак, имеется таблица регистра накопления со следующим содержимым:
Так же имеются документы с названием "Черная прибыль", которые не делают движений в регистрах (разработаны и добавлены в конфигурацию бухгалтером-самоучкой).
Необходимо сформировать отчет, который будет включать следующие данные:
| Организация | Начальный остаток средств | Приход средств | Расход средств | Конечный остаток средств | Черная прибыль |
| Месяц |
Т.е., в отчете необходимо получить данные в разрезе организаций и по месяцам.
Создаем отчет с использованием системы компоновки данных и конструируем во внутренностях такой запрос:
ВЫБРАТЬ
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.Организация КАК Организация,
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.ПериодМесяц КАК ПериодМесяц,
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.СуммаНачальныйОстаток КАК СуммаНачальныйОстаток,
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.СуммаПриход КАК СуммаПриход,
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.СуммаРасход КАК СуммаРасход,
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.СуммаКонечныйОстаток КАК СуммаКонечныйОстаток,
NULL КАК ЧернаяПрибыль
ИЗ
РегистрНакопления.ОстаткиДенежныхСредствПоОрганизациям.ОстаткиИОбороты({(&НачалоПериода)}, {(&КонецПериода)}, Авто, , {(Организация).* КАК Организация}) КАК ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ЧёрнаяПрибыль.Организация,
НАЧАЛОПЕРИОДА(ЧёрнаяПрибыль.Дата, МЕСЯЦ),
NULL,
NULL,
NULL,
NULL,
ЧёрнаяПрибыль.Сумма
ИЗ
Документ.ЧёрнаяПрибыль КАК ЧёрнаяПрибыль
ГДЕ
ЧёрнаяПрибыль.ПометкаУдаления = ЛОЖЬ
{ГДЕ
ЧёрнаяПрибыль.Организация.* КАК Организация,
(ЧёрнаяПрибыль.Дата >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)),
(ЧёрнаяПрибыль.Дата <= КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ))}
В конструкторе СКД выбираем необходимые поля для вывода в отчет, создаем группировки по полям Организация и Месяц, для группировки по месяцам устанавливаем дополнение периода Месяц.
Вроде бы, всё просто. Некоторые спрашивают, можно ли в таких случаях в запросе делать левое соединение. Ответ: не запрещается, но если сделать соединение к таблице, в которой нет данных (а такое может быть), получим пустой отчет.
Смотрим, что получилось в нашем случае:
(в условном оформлении задан формат поля "ПериодМесяц": ДФ='MMMM yyyy')
На первый взгляд, всё красиво, но в апреле и августе начальный и конечный остатки не вывелись, а должны бы, несмотря на то, что оборотов в эти месяцы не было. Так же, не вывелся конечный остаток в итогах.
Есть несколько путей доработки отчета для получения необходимого результата, ниже приведен такой вариант: дополнение результата подзапроса к регистру остатков недостающими периодами (апрель) и добавление в эту выборку периодов, которых нет в выборке из регистра, но есть во второй таблице (август).
Получаем такой запрос (пояснения ниже):
ВЫБРАТЬ
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.Организация КАК Организация,
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.ПериодМесяц КАК ПериодМесяц,
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.СуммаНачальныйОстаток КАК СуммаНачальныйОстаток,
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.СуммаПриход КАК СуммаПриход,
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.СуммаРасход КАК СуммаРасход,
ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты.СуммаКонечныйОстаток КАК СуммаКонечныйОстаток
ПОМЕСТИТЬ ДанныеИзРегистра
ИЗ
РегистрНакопления.ОстаткиДенежныхСредствПоОрганизациям.ОстаткиИОбороты({(&НачалоПериода)}, {(&КонецПериода)}, Авто, , {(Организация).* КАК Организация}) КАК ОстаткиДенежныхСредствПоОрганизациямОстаткиИОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЧёрнаяПрибыль.Организация КАК Организация,
НАЧАЛОПЕРИОДА(ЧёрнаяПрибыль.Дата, МЕСЯЦ) КАК ПериодМесяц,
ЧёрнаяПрибыль.Сумма КАК ЧернаяПрибыль
ПОМЕСТИТЬ ДанныеИзДокументов
ИЗ
Документ.ЧёрнаяПрибыль КАК ЧёрнаяПрибыль
ГДЕ
ЧёрнаяПрибыль.ПометкаУдаления = ЛОЖЬ
{ГДЕ
ЧёрнаяПрибыль.Организация.* КАК Организация,
(ЧёрнаяПрибыль.Дата >= НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ)),
(ЧёрнаяПрибыль.Дата <= КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ))}
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
0 КАК Цифра
ПОМЕСТИТЬ Цифры
ОБЪЕДИНИТЬ
ВЫБРАТЬ
1
ОБЪЕДИНИТЬ
ВЫБРАТЬ
2
ОБЪЕДИНИТЬ
ВЫБРАТЬ
3
ОБЪЕДИНИТЬ
ВЫБРАТЬ
4
ОБЪЕДИНИТЬ
ВЫБРАТЬ
5
ОБЪЕДИНИТЬ
ВЫБРАТЬ
6
ОБЪЕДИНИТЬ
ВЫБРАТЬ
7
ОБЪЕДИНИТЬ
ВЫБРАТЬ
8
ОБЪЕДИНИТЬ
ВЫБРАТЬ
9
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Тысячи.Цифра * 1000 + Сотни.Цифра * 100 + Десятки.Цифра * 10 + Единицы.Цифра КАК Дней
ПОМЕСТИТЬ СписокДней
ИЗ
Цифры КАК Тысячи,
Цифры КАК Сотни,
Цифры КАК Десятки,
Цифры КАК Единицы
ГДЕ
Тысячи.Цифра * 1000 + Сотни.Цифра * 100 + Десятки.Цифра * 10 + Единицы.Цифра <= РАЗНОСТЬДАТ(ДАТАВРЕМЯ(2000, 1, 1), ДОБАВИТЬКДАТЕ(&КонецПериода, ГОД, 10), МЕСЯЦ)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(ДАТАВРЕМЯ(2000, 1, 1), МЕСЯЦ, СписокДней.Дней) КАК Период
ПОМЕСТИТЬ ВсеМесяцы
ИЗ
СписокДней КАК СписокДней
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ДанныеИзРегистра.Организация,
ДанныеИзРегистра.ПериодМесяц,
ДанныеИзРегистра.СуммаКонечныйОстаток,
МИНИМУМ(ДанныеИзРегистра1.ПериодМесяц) КАК ПериодМесяц1
ПОМЕСТИТЬ ВсеИнтервалы
ИЗ
ДанныеИзРегистра КАК ДанныеИзРегистра
ЛЕВОЕ СОЕДИНЕНИЕ ДанныеИзРегистра КАК ДанныеИзРегистра1
ПО ДанныеИзРегистра.Организация = ДанныеИзРегистра1.Организация
И ДанныеИзРегистра.ПериодМесяц < ДанныеИзРегистра1.ПериодМесяц
СГРУППИРОВАТЬ ПО
ДанныеИзРегистра.Организация,
ДанныеИзРегистра.СуммаКонечныйОстаток,
ДанныеИзРегистра.ПериодМесяц
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВсеИнтервалы.Организация,
ВсеИнтервалы.СуммаКонечныйОстаток,
ВсеИнтервалы.ПериодМесяц,
ВсеИнтервалы.ПериодМесяц1
ПОМЕСТИТЬ ИнтервалыБольшеМесяца
ИЗ
ВсеИнтервалы КАК ВсеИнтервалы
ГДЕ
РАЗНОСТЬДАТ(ВсеИнтервалы.ПериодМесяц, ВсеИнтервалы.ПериодМесяц1, МЕСЯЦ) > 1
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ИнтервалыБольшеМесяца.Организация,
ИнтервалыБольшеМесяца.СуммаКонечныйОстаток,
ВсеМесяцы.Период
ПОМЕСТИТЬ НедостающиеПериоды
ИЗ
ВсеМесяцы КАК ВсеМесяцы
ЛЕВОЕ СОЕДИНЕНИЕ ИнтервалыБольшеМесяца КАК ИнтервалыБольшеМесяца
ПО ВсеМесяцы.Период > ИнтервалыБольшеМесяца.ПериодМесяц
И ВсеМесяцы.Период < ИнтервалыБольшеМесяца.ПериодМесяц1
ГДЕ
ВсеМесяцы.Период > ИнтервалыБольшеМесяца.ПериодМесяц
И ВсеМесяцы.Период < ИнтервалыБольшеМесяца.ПериодМесяц1
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ДанныеИзДокументов.ПериодМесяц,
ДанныеИзДокументов.Организация КАК Организация,
МАКСИМУМ(ДанныеИзРегистра.ПериодМесяц) КАК ПериодМесяц1,
ДанныеИзРегистра.СуммаКонечныйОстаток КАК СуммаКонечныйОстаток
ПОМЕСТИТЬ НедостающиеПериодыИСальдоПоДокументам
ИЗ
ДанныеИзДокументов КАК ДанныеИзДокументов
ЛЕВОЕ СОЕДИНЕНИЕ ДанныеИзРегистра КАК ДанныеИзРегистра
ПО ДанныеИзДокументов.ПериодМесяц > ДанныеИзРегистра.ПериодМесяц
И ДанныеИзДокументов.Организация = ДанныеИзРегистра.Организация
ГДЕ
ДанныеИзДокументов.ПериодМесяц <> ДАТАВРЕМЯ(1, 1, 1)
И (НЕ ДанныеИзДокументов.ПериодМесяц В
(ВЫБРАТЬ
ДанныеИзРегистра.ПериодМесяц
ИЗ
ДанныеИзРегистра))
СГРУППИРОВАТЬ ПО
ДанныеИзДокументов.ПериодМесяц,
ДанныеИзДокументов.Организация,
ДанныеИзРегистра.СуммаКонечныйОстаток
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
НедостающиеПериодыИСальдоПоДокументам.ПериодМесяц,
НедостающиеПериодыИСальдоПоДокументам.Организация,
МАКСИМУМ(НедостающиеПериодыИСальдоПоДокументам.ПериодМесяц1) КАК ПериодМесяц1
ПОМЕСТИТЬ НедостающиеПериодыБезКонОст
ИЗ
НедостающиеПериодыИСальдоПоДокументам КАК НедостающиеПериодыИСальдоПоДокументам
СГРУППИРОВАТЬ ПО
НедостающиеПериодыИСальдоПоДокументам.ПериодМесяц,
НедостающиеПериодыИСальдоПоДокументам.Организация
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗЛИЧНЫЕ
НедостающиеПериодыБезКонОст.Организация,
НедостающиеПериодыБезКонОст.ПериодМесяц,
НедостающиеПериодыИСальдоПоДокументам.СуммаКонечныйОстаток
ПОМЕСТИТЬ НедостающееСальдоПоДокументам
ИЗ
НедостающиеПериодыБезКонОст КАК НедостающиеПериодыБезКонОст
ЛЕВОЕ СОЕДИНЕНИЕ НедостающиеПериодыИСальдоПоДокументам КАК НедостающиеПериодыИСальдоПоДокументам
ПО НедостающиеПериодыБезКонОст.Организация = НедостающиеПериодыИСальдоПоДокументам.Организация
И НедостающиеПериодыБезКонОст.ПериодМесяц1 = НедостающиеПериодыИСальдоПоДокументам.ПериодМесяц1
ГДЕ
(НЕ НедостающиеПериодыБезКонОст.ПериодМесяц В
(ВЫБРАТЬ
НедостающиеПериоды.Период
ИЗ
НедостающиеПериоды))
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ДанныеИзРегистра.Организация,
ДанныеИзРегистра.ПериодМесяц,
ДанныеИзРегистра.СуммаНачальныйОстаток,
ДанныеИзРегистра.СуммаПриход,
ДанныеИзРегистра.СуммаРасход,
ДанныеИзРегистра.СуммаКонечныйОстаток
ПОМЕСТИТЬ СальдоВсеПериоды
ИЗ
ДанныеИзРегистра КАК ДанныеИзРегистра
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
НедостающиеПериоды.Организация,
НедостающиеПериоды.Период,
НедостающиеПериоды.СуммаКонечныйОстаток,
0,
0,
НедостающиеПериоды.СуммаКонечныйОстаток
ИЗ
НедостающиеПериоды КАК НедостающиеПериоды
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
НеДостающееСальдоПоДокументам.Организация,
НеДостающееСальдоПоДокументам.ПериодМесяц,
НеДостающееСальдоПоДокументам.СуммаКонечныйОстаток,
0,
0,
НеДостающееСальдоПоДокументам.СуммаКонечныйОстаток
ИЗ
НедостающееСальдоПоДокументам КАК НеДостающееСальдоПоДокументам
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СальдоВсеПериоды.Организация КАК Организация,
СальдоВсеПериоды.ПериодМесяц КАК ПериодМесяц,
СальдоВсеПериоды.СуммаНачальныйОстаток КАК СуммаНачальныйОстаток,
СальдоВсеПериоды.СуммаПриход КАК СуммаПриход,
СальдоВсеПериоды.СуммаРасход КАК СуммаРасход,
СальдоВсеПериоды.СуммаКонечныйОстаток КАК СуммаКонечныйОстаток,
NULL КАК ЧернаяПрибыль
ПОМЕСТИТЬ Данные
ИЗ
СальдоВсеПериоды КАК СальдоВсеПериоды
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ДанныеИзДокументов.Организация,
ДанныеИзДокументов.ПериодМесяц,
NULL,
NULL,
NULL,
NULL,
ДанныеИзДокументов.ЧернаяПрибыль
ИЗ
ДанныеИзДокументов КАК ДанныеИзДокументов
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Данные.Организация,
Данные.ПериодМесяц,
СУММА(Данные.СуммаНачальныйОстаток) КАК СуммаНачальныйОстаток,
СУММА(Данные.СуммаПриход) КАК СуммаПриход,
СУММА(Данные.СуммаРасход) КАК СуммаРасход,
СУММА(Данные.СуммаКонечныйОстаток) КАК СуммаКонечныйОстаток,
СУММА(Данные.ЧернаяПрибыль) КАК ЧернаяПрибыль
ИЗ
Данные КАК Данные
СГРУППИРОВАТЬ ПО
Данные.Организация,
Данные.ПериодМесяц
Пояснения к запросу:
В разные временные таблицы выбираются данные из регистра и документов. Затем происходят манипуляции с этими данными - вычисление недостающих периодов и добавление данных по ним в результирующую выборку.
Пояснения к временным таблицам:
- ДанныеИзРегистра - данные из регистра накопления (остатки и обороты)
- ДанныеИзДокументов - данные из документов (черная прибыль)
- ВсеМесяцы - таблица в которой содержатся все месяцы из интервала Январь 2000 - (Конечная дата отчета + 10 лет); этот интервал можно менять под конкретные нужды
- ВсеИнтервалы - интервалы [в количестве месяцев] между периодами в результате запроса к регистру накопления
- ИнтервалыБольшеМесяца - интервалы [в количестве месяцев] между периодами в результате запроса к регистру накопления, где количество месяцев в интервале больше 1
- НедостающиеПериоды - недостающие периоды в данных из регистра накопления и конечное сальдо по этим периодам (оно будет равно начальному в отчете)
- НедостающиеПериодыИСальдоПоДокументам - периоды (и сальдо по ним), которых нет в регистре, но есть в документах
- НедостающиеПериодыБезКонОст - свернутая таблица с недостающими периодами по документам без остатков
- НедостающееСальдоПоДокументам - свернутая таблица с недостающими периодами по документам и остатками
- СальдоВсеПериоды - таблица в которой содержатся все недостающие периоды (и по документам и "разрывы" в регистре) с остатками
- Данные - объединение данных из регистра (с дополненными периодами) с данными из документов
В данной реализации необходимо, чтобы была заполнена конечная дата формирования отчета.
Результат формирования отчета:
Таким образом, получили отчет, в котором корректно выводятся начальный и конечный остатки за все периоды, а так же нужные данные, которых нет в регистре накопления.
В публикацию включена демонстрационная база.
Вступайте в нашу телеграмм-группу Инфостарт