Перейти к основному содержимому

Как собрать платежи LensSklad в DataLens для отчета ДДС

· 12 мин. чтения
Анатолий Рыбин
DataSklad

Отчет о движении денежных средств удобнее строить не по отдельным документам, а по одной сводной таблице платежей. В LensSklad платежные документы МоегоСклада выгружаются в PostgreSQL отдельными таблицами, поэтому перед визуализацией в Yandex DataLens их стоит объединить в одно представление или SQL-датасет.

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

Подключение PostgreSQL в DataLens с включенным уровнем доступа SQL-запросов

Что включить в экспорте LensSklad​

Перед созданием датасета проверьте настройки экспорта LensSklad и включите платежные сущности, которые должны попасть в отчет.

Для базового отчета по банковским платежам нужны две таблицы:

  • paymentin — входящие платежи;
  • paymentout — исходящие платежи.

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

  • cashin — приходные кассовые ордера;
  • cashout — расходные кассовые ордера.

Важно: SQL-запрос не должен ссылаться на таблицу, которой нет в вашей базе. Если кассовые ордера не включены в экспорте, используйте вариант запроса только с paymentin и paymentout. Если ордера включены, используйте расширенный вариант с четырьмя таблицами.

Вариант 1. SQL без кассовых ордеров​

Этот запрос подходит, если в экспорте LensSklad включены только банковские платежи: paymentin и paymentout.

Для SQL-датасета в DataLens вставьте запрос без первой строки create or replace view ... as. Для представления в PostgreSQL используйте запрос целиком.

create or replace view datalens_payments_dds as
SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    organizationaccount_id::uuid AS organizationaccount_id,
    agent_id,
    contract_id,
    applicable,
    incomingnumber AS document_number,
    incomingdate AS document_date,
    'paymentin'::text AS type,
    'in'::text AS direction,
    sum AS signed_sum
FROM paymentin

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    organizationaccount_id::uuid AS organizationaccount_id,
    agent_id,
    contract_id,
    applicable,
    NULL::text AS document_number,
    NULL::timestamp AS document_date,
    'paymentout'::text AS type,
    'out'::text AS direction,
    -sum AS signed_sum
FROM paymentout;

В этом варианте входящие платежи попадают в signed_sum с положительным знаком, а исходящие — с отрицательным. Поля document_number и document_date заполняются из incomingnumber и incomingdate только для paymentin; для paymentout они остаются пустыми.

Вариант 2. SQL с кассовыми ордерами​

Если в экспорте включены cashin и cashout, можно добавить их в ту же сводную таблицу. У кассовых ордеров в этом запросе нет банковского счета организации, поэтому organizationaccount_id заполняется как NULL::uuid.

create or replace view datalens_payments_dds as
SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    organizationaccount_id::uuid AS organizationaccount_id,
    agent_id,
    contract_id,
    applicable,
    incomingnumber AS document_number,
    incomingdate AS document_date,
    'paymentin'::text AS type,
    'in'::text AS direction,
    sum AS signed_sum
FROM paymentin

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    NULL::uuid AS organizationaccount_id,
    agent_id,
    contract_id,
    applicable,
    NULL::text AS document_number,
    NULL::timestamp AS document_date,
    'cashin'::text AS type,
    'in'::text AS direction,
    sum AS signed_sum
FROM cashin

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    organizationaccount_id::uuid AS organizationaccount_id,
    agent_id,
    contract_id,
    applicable,
    NULL::text AS document_number,
    NULL::timestamp AS document_date,
    'paymentout'::text AS type,
    'out'::text AS direction,
    -sum AS signed_sum
FROM paymentout

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    NULL::uuid AS organizationaccount_id,
    agent_id,
    contract_id,
    applicable,
    NULL::text AS document_number,
    NULL::timestamp AS document_date,
    'cashout'::text AS type,
    'out'::text AS direction,
    -sum AS signed_sum
FROM cashout;

PostgreSQL не позволяет обычному статическому запросу union all автоматически пропустить отсутствующую таблицу. Поэтому надежнее держать два отдельных варианта: базовый без кассовых ордеров и расширенный с кассовыми ордерами.

Как создать представление в PostgreSQL​

Если у вас есть доступ к базе PostgreSQL, куда LensSklad выгружает данные, удобнее создать обычное SQL-представление. DataLens будет подключаться к нему как к таблице, а сам SQL останется в базе и будет доступен всем отчетам.

Общий порядок такой:

  1. Откройте SQL-клиент для базы PostgreSQL.
  2. Проверьте, что нужные платежные таблицы включены в экспорте LensSklad и уже появились в базе.
  3. Выберите вариант SQL: без кассовых ордеров или с кассовыми ордерами.
  4. Выполните запрос create or replace view datalens_payments_dds as ....
  5. Откройте DataLens и подключите созданное представление datalens_payments_dds как источник датасета.
  6. Проверьте типы полей: дата должна быть датой, суммы — числом, признак проведения — логическим или категориальным полем.

Как создать SQL-датасет прямо в DataLens​

Если вы не хотите создавать представление в PostgreSQL, тот же запрос можно использовать как SQL-датасет в DataLens:

  1. Откройте подключение DataLens к PostgreSQL, куда LensSklad выгружает данные.
  2. В настройках подключения включите уровень доступа SQL-запросов, чтобы DataLens разрешил использовать произвольный SQL в датасете.
  3. Создайте новый датасет.
  4. Выберите режим SQL-запроса.
  5. Вставьте выбранный SQL без строки create or replace view datalens_payments_dds as.
  6. Сохраните датасет и проверьте типы полей.

В источнике датасета выберите режим SQL и вставьте запрос в поле подзапроса. Так DataLens будет брать не отдельную таблицу paymentin или paymentout, а уже подготовленную сводную таблицу платежей.

Создание SQL-источника платежей в DataLens

SQL-датасет быстрее настроить, но представление в базе проще переиспользовать в нескольких отчетах, проверять в SQL-клиенте и хранить как часть аналитического слоя.

Поля итогового датасета​

В примерах выше итоговый датасет содержит такие поля:

  • id — идентификатор документа;
  • name — номер или название документа;
  • uuidhref — ссылка на документ;
  • sum — сумма документа без изменения знака в формате хранения МоегоСклада;
  • vatsum — сумма НДС в формате хранения МоегоСклада;
  • moment — дата и время платежного документа;
  • created — дата создания документа;
  • updated — дата обновления документа;
  • state_id — идентификатор статуса;
  • organization_id — идентификатор организации;
  • organizationaccount_id — идентификатор счета организации, если он есть у документа;
  • agent_id — идентификатор контрагента;
  • contract_id — идентификатор договора;
  • applicable — признак проведения документа;
  • document_number — входящий номер, если он есть у документа;
  • document_date — входящая дата, если она есть у документа;
  • type — тип исходного документа: paymentin, paymentout, cashin или cashout;
  • direction — направление платежа: in для поступлений и out для списаний;
  • signed_sum — сумма со знаком для расчета ДДС в формате хранения МоегоСклада.

На этом этапе в датасете еще видны системные поля из SQL-запроса. Это нормально: сначала нужно проверить, что данные пришли, типы определились правильно, ссылки открываются, а суммы выглядят как числа.

Настроенные поля датасета платежей в DataLens

Как назвать вычисляемые поля в DataLens​

Денежные поля МоегоСклада, например sum, vatsum и рассчитанный в запросе signed_sum, хранятся в базе целыми числами в копейках. В SQL выше они оставлены как есть, чтобы запрос точно соответствовал экспортируемым полям и не смешивал сбор данных с настройкой витрины.

После проверки датасета я рекомендую не работать в чартах напрямую с системными полями sum, vatsum и signed_sum. Они нужны как техническая основа, но в интерфейсе отчета лучше создать свои понятные поля, а системные поля скрыть.

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

Например, для основной суммы платежа можно создать поле:

  • Сумма платежа | SUM — SUM([signed_sum]/100).

Такое поле уже можно использовать в KPI, таблицах, линейных графиках и диаграммах ДДС. В SQL-запросе signed_sum уже собран со знаком: входящие платежи попадают туда с плюсом, исходящие — с минусом.

Дополнительно удобно завести поле для НДС:

  • НДС платежа | SUM — SUM([vatsum]/100).

Создание вычисляемого поля суммы платежа в DataLens

На скриншоте показан сам интерфейс создания формулы. Название поля и выражение задайте по своей схеме: для ДДС используйте SUM([signed_sum]/100), потому что это поле уже учитывает знак платежа.

Для текстовых и ссылочных полей можно сделать такой набор:

  • Платеж проведен — [applicable];
  • Платеж — [name];
  • Платеж | URL — URL([uuidhref], [name]);
  • Дата платежа — [moment].

Если в датасет добавлена таблица контрагентов, можно вывести контрагента обычным текстом или ссылкой:

  • Контрагент — [name];
  • Контрагент | URL — URL([uuidhref], [name]).

Вариант с URL([uuidhref], [name]) удобен тем, что прямо из аналитики можно перейти в ваш аккаунт МоегоСклада к нужному платежу или контрагенту. В итоговом датасете важно только не перепутать поля платежа и контрагента: если у них одинаковые технические названия name и uuidhref, лучше заранее дать им понятные алиасы в SQL или в датасете.

После этого можно скрыть из датасета или хотя бы не показывать пользователям сырые поля sum, vatsum, signed_sum, а также технические идентификаторы, которые не нужны в обычной витрине: id, uuidhref, organization_id, agent_id, contract_id, state_id. Их можно оставить доступными только там, где они нужны для связей, детализации или перехода к исходному документу.

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

Как добавить вид деятельности в МоемСкладе​

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

Один из практичных вариантов — использовать группы в разделе Товары и услуги. В МоемСкладе можно учитывать не только товары, но и услуги: зарплаты, рекламу, аренду, налоги, проценты по кредиту, лизинг и другие статьи. Тогда группы товаров и услуг становятся управленческим классификатором для ДДС.

Подробно про такую структуру я разобрал в отдельной статье: как настроить группы товаров и услуг в МоемСкладе для управленческого учета и внешней аналитики.

Такое разделение помогает не пытаться определить смысл платежа только по таблице paymentin, paymentout, cashin или cashout. Один и тот же исходящий платеж может быть переменным расходом, постоянным расходом, покупкой оборудования, лизинговым платежом, возвратом тела кредита или выплатой процентов.

Обычно я начинаю с трех основных групп:

  • инвестиционные — имущество, развитие, разработка, строительство;
  • операционные — налоги, переменные расходы, постоянные расходы, проценты, сырье, товары, услуги;
  • финансовые — инвестирование, кредитование, переводы, услуги.

Внутри этих групп можно делать подгруппы глубже — уже под конкретный бизнес и его статьи движения денег. Главное — завести вид деятельности в МоемСкладе как часть учетной структуры до выгрузки данных во внешнюю аналитику.

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

Как собрать чарты для отчета ДДС​

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

Базовый чарт для ДДС можно собрать так:

  • временная ось — Дата платежа или исходное поле moment;
  • показатель — Сумма платежа | SUM;
  • фильтр факта — Платеж проведен равен true.

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

В DataLens это можно сделать прямо из датасета:

  1. Откройте датасет и нажмите Создать чарт.
  2. Выберите тип визуализации: линейная диаграмма или столбчатая диаграмма.
  3. Перетащите Дата платежа на временную ось и задайте группировку Месяц.
  4. Перетащите Сумма платежа | SUM в показатель.
  5. Добавьте фильтр по Платеж проведен, если нужен только фактический ДДС.
  6. Добавьте фильтр по Дата платежа, чтобы указать, с какой даты строить отчет.
  7. При необходимости добавьте в цвета контрагентов, типы платежей, группы товаров или другие аналитические признаки.
  8. Сохраните чарт и разместите его на своем дашборде.

Настройка группировки даты платежа по месяцам в чарте DataLens

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

Пример столбчатой диаграммы ДДС в DataLens

Как разделить факт, план и черновики​

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

Лучше передавать признак проведения applicable в DataLens отдельным полем и делать переключатели уже в отчете:

  • Факт — только проведенные документы.
  • План и черновики — непроведенные документы, включая платежи на будущую дату.
  • Все операции — полный список платежей для контроля качества учета.

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

В цветовую настройку можно добавить дополнительную разбивку:

  • по типам платежей: paymentin, paymentout, cashin, cashout;
  • по направлению платежа: поступления и списания;
  • по группам товаров или услуг, если эта аналитика добавлена в датасет;
  • по контрагентам;
  • по организациям или счетам организации.

Для фактического ДДС обязательно отфильтруйте чарт по полю Платеж проведен, чтобы в нем были только прошедшие операции. Будущие и непроведенные платежи лучше вынести в отдельный чарт: там можно показать плановые оплаты, черновики и документы, которые нужно проверить.

На основе одного сводного датасета удобно собрать несколько виджетов:

  1. Фактический ДДС по месяцам — график по дате платежа и Сумма платежа | SUM с фильтром Платеж проведен.
  2. Поступления и списания — диаграмма с разбивкой по direction или type.
  3. Будущие и непроведенные платежи — таблица или график с фильтром Платеж проведен равен false.
  4. Платежи по организациям — таблица или диаграмма по organization_id и signed_sum.
  5. Платежи по контрагентам — таблица по agent_id и signed_sum.
  6. ДДС по виду деятельности — график или таблица по дополнительной аналитике из МоегоСклада: операционная, инвестиционная и финансовая деятельность.
  7. Контроль документов без договора — таблица с фильтром по пустому contract_id, если договор важен для управленческого учета.

Итог​

Сводная таблица платежей — это небольшой технический слой между LensSklad и DataLens, но именно он делает отчет ДДС управляемым. Вместо отдельных платежных таблиц вы получаете один датасет, где видны фактические платежи, будущие черновики, непроведенные документы и операции, которые требуют проверки.

Начните с варианта без кассовых ордеров, если вам нужен только банк. Если в LensSklad включены cashin и cashout, используйте расширенный SQL и добавьте кассовые операции в тот же отчет ДДС.