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

Как собрать прибыли и убытки через LensSklad в Яндекс DataLens

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

Отчет о прибылях и убытках строится методом начисления. Это значит, что в нем участвуют не деньги на расчетном счете, а исполненные обязательства: отгрузили товар или оказали услугу — появилась выручка, приняли товар или услугу от поставщика — появился расход.

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

Какие документы участвуют в отчете​

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

  • demand — отгрузки, выручка;
  • salesreturn — возвраты покупателей, уменьшение выручки;
  • supply — приемки, расходы или себестоимость закупок;
  • purchasereturn — возвраты поставщикам, уменьшение расходов.

Если компания работает с розницей и комиссионной торговлей, в расширенный вариант стоит добавить:

  • retaildemand — розничные продажи, выручка;
  • retailsalesreturn — розничные возвраты, уменьшение выручки;
  • commissionreportin — полученные отчеты комиссионера, выручка;
  • commissionreportout — выданные отчеты комиссионера, расходы.

В таблицы PostgreSQL поля выгружаются так же, как они приходят из API МоегоСклада: с теми же смыслами и близкими названиями, только в нижнем регистре.

Какие поля можно сделать общими​

У этих сущностей есть общий набор полей, который удобно вынести в один аналитический слой:

  • id — идентификатор документа;
  • name — номер документа;
  • uuidhref — ссылка на документ в интерфейсе МоегоСклада;
  • moment — дата документа;
  • created и updated — даты создания и обновления;
  • sum — сумма документа в копейках;
  • vatsum — сумма НДС в копейках;
  • applicable — признак проведения;
  • organization_id — организация;
  • agent_id — контрагент;
  • contract_id — договор;
  • state_id — статус;
  • project_id — проект, если используется;
  • store_id — склад, если он есть у документа;
  • description — комментарий или описание;
  • group_id — отдел или группа доступа;
  • owner_id — владелец документа.

Для расширенного варианта можно сразу оставить дополнительные поля, которые особенно полезны для отчетов комиссионера и части закупочных документов: commissionperiodstart, commissionperiodend, rewardtype, incomingdate, incomingnumber, payedsum, commitentsum, vatincluded, vatenabled, overhead_sum.

Часть колонок у отдельных документов будет пустой. Это нормально: общий датасет важнее, чем попытка сделать отдельную схему под каждый тип документа.

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

Связи SQL-датасета ОПиУ со справочниками организаций, контрагентов и групп товаров в DataLens

Какой знак суммы использовать​

В отчете удобно сразу хранить сумму со знаком:

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

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

Вариант 1. Отгрузки и приемки с возвратами​

Этот вариант подходит, если вы хотите начать с базового управленческого отчета по оптовым продажам и закупкам. В нем участвуют только четыре таблицы: demand, salesreturn, supply, purchasereturn.

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

create or replace view documents_pnl as
SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    store_id,
    cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    NULL::timestamp AS commissionperiodstart,
    NULL::timestamp AS commissionperiodend,
    NULL::text AS rewardtype,
    NULL::timestamp AS incomingdate,
    NULL::text AS incomingnumber,
    NULL::numeric AS payedsum,
    NULL::numeric AS commitentsum,
    NULL::numeric AS overhead_sum,
    applicable,
    'demand'::text AS type,
    'Отгрузка'::text AS document_name,
    sum AS signed_sum
FROM demand

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    store_id,
    NULL::numeric AS cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    NULL::timestamp AS commissionperiodstart,
    NULL::timestamp AS commissionperiodend,
    NULL::text AS rewardtype,
    NULL::timestamp AS incomingdate,
    NULL::text AS incomingnumber,
    NULL::numeric AS payedsum,
    NULL::numeric AS commitentsum,
    NULL::numeric AS overhead_sum,
    applicable,
    'salesreturn'::text AS type,
    'Возврат покупателя'::text AS document_name,
    -sum AS signed_sum
FROM salesreturn

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    store_id,
    NULL::numeric AS cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    NULL::timestamp AS commissionperiodstart,
    NULL::timestamp AS commissionperiodend,
    NULL::text AS rewardtype,
    incomingdate,
    incomingnumber,
    NULL::numeric AS payedsum,
    NULL::numeric AS commitentsum,
    overhead_sum,
    applicable,
    'supply'::text AS type,
    'Приемка'::text AS document_name,
    -sum AS signed_sum
FROM supply

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    store_id,
    NULL::numeric AS cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    NULL::timestamp AS commissionperiodstart,
    NULL::timestamp AS commissionperiodend,
    NULL::text AS rewardtype,
    NULL::timestamp AS incomingdate,
    NULL::text AS incomingnumber,
    NULL::numeric AS payedsum,
    NULL::numeric AS commitentsum,
    NULL::numeric AS overhead_sum,
    applicable,
    'purchasereturn'::text AS type,
    'Возврат поставщику'::text AS document_name,
    sum AS signed_sum
FROM purchasereturn;

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

Вариант 2. С розницей и отчетами комиссионера​

Если в LensSklad включены розничные продажи и комиссионные отчеты, можно собрать более полный слой для ОПиУ. В этом варианте в итоговый датасет добавляются retaildemand, retailsalesreturn, commissionreportin и commissionreportout.

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

create or replace view documents_pnl as
SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    store_id,
    cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    NULL::timestamp AS commissionperiodstart,
    NULL::timestamp AS commissionperiodend,
    NULL::text AS rewardtype,
    NULL::timestamp AS incomingdate,
    NULL::text AS incomingnumber,
    NULL::numeric AS payedsum,
    NULL::numeric AS commitentsum,
    NULL::numeric AS overhead_sum,
    applicable,
    'demand'::text AS type,
    'Отгрузка'::text AS document_name,
    sum AS signed_sum
FROM demand

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    store_id,
    NULL::numeric AS cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    NULL::timestamp AS commissionperiodstart,
    NULL::timestamp AS commissionperiodend,
    NULL::text AS rewardtype,
    NULL::timestamp AS incomingdate,
    NULL::text AS incomingnumber,
    NULL::numeric AS payedsum,
    NULL::numeric AS commitentsum,
    NULL::numeric AS overhead_sum,
    applicable,
    'salesreturn'::text AS type,
    'Возврат покупателя'::text AS document_name,
    -sum AS signed_sum
FROM salesreturn

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    store_id,
    NULL::numeric AS cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    NULL::timestamp AS commissionperiodstart,
    NULL::timestamp AS commissionperiodend,
    NULL::text AS rewardtype,
    NULL::timestamp AS incomingdate,
    NULL::text AS incomingnumber,
    NULL::numeric AS payedsum,
    NULL::numeric AS commitentsum,
    NULL::numeric AS overhead_sum,
    applicable,
    'retaildemand'::text AS type,
    'Розничная продажа'::text AS document_name,
    sum AS signed_sum
FROM retaildemand

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    store_id,
    NULL::numeric AS cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    NULL::timestamp AS commissionperiodstart,
    NULL::timestamp AS commissionperiodend,
    NULL::text AS rewardtype,
    NULL::timestamp AS incomingdate,
    NULL::text AS incomingnumber,
    NULL::numeric AS payedsum,
    NULL::numeric AS commitentsum,
    NULL::numeric AS overhead_sum,
    applicable,
    'retailsalesreturn'::text AS type,
    'Розничный возврат'::text AS document_name,
    -sum AS signed_sum
FROM retailsalesreturn

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    NULL::uuid AS store_id,
    NULL::numeric AS cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    commissionperiodstart,
    commissionperiodend,
    rewardtype,
    incomingdate,
    incomingnumber,
    payedsum,
    commitentsum,
    commission_overhead_sum AS overhead_sum,
    applicable,
    'commissionreportin'::text AS type,
    'Полученный отчет комиссионера'::text AS document_name,
    sum AS signed_sum
FROM commissionreportin

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    store_id,
    NULL::numeric AS cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    NULL::timestamp AS commissionperiodstart,
    NULL::timestamp AS commissionperiodend,
    NULL::text AS rewardtype,
    incomingdate,
    incomingnumber,
    NULL::numeric AS payedsum,
    NULL::numeric AS commitentsum,
    overhead_sum,
    applicable,
    'supply'::text AS type,
    'Приемка'::text AS document_name,
    -sum AS signed_sum
FROM supply

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    store_id,
    NULL::numeric AS cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    NULL::timestamp AS commissionperiodstart,
    NULL::timestamp AS commissionperiodend,
    NULL::text AS rewardtype,
    NULL::timestamp AS incomingdate,
    NULL::text AS incomingnumber,
    NULL::numeric AS payedsum,
    NULL::numeric AS commitentsum,
    NULL::numeric AS overhead_sum,
    applicable,
    'purchasereturn'::text AS type,
    'Возврат поставщику'::text AS document_name,
    sum AS signed_sum
FROM purchasereturn

UNION ALL

SELECT
    id,
    name,
    uuidhref,
    sum,
    vatsum,
    moment,
    created,
    updated,
    state_id,
    organization_id,
    agent_id,
    contract_id,
    project_id,
    NULL::uuid AS store_id,
    NULL::numeric AS cost,
    description,
    group_id,
    owner_id,
    vatincluded,
    vatenabled,
    commissionperiodstart,
    commissionperiodend,
    rewardtype,
    incomingdate,
    incomingnumber,
    payedsum,
    commitentsum,
    commission_overhead_sum AS overhead_sum,
    applicable,
    'commissionreportout'::text AS type,
    'Выданный отчет комиссионера'::text AS document_name,
    -sum AS signed_sum
FROM commissionreportout;

Главное правило для union all: в каждом блоке должно быть одинаковое количество колонок и совместимые типы. Если в вашей базе какого-то поля нет, временно замените его на NULL с нужным типом, например NULL::numeric AS overhead_sum.

Как настроить поля в DataLens​

После создания представления или SQL-датасета проверьте поля:

  • Дата документа — [moment];
  • Документ — [name];
  • Документ | URL — URL([uuidhref], [name]);
  • Тип документа — [document_name];
  • Проведен — [applicable];
  • Сумма — SUM([signed_sum] / 100);
  • НДС — SUM([vatsum] / 100);
  • Сумма без НДС — SUM(([signed_sum] - [vatsum]) / 100);
  • Себестоимость отгрузки — SUM([cost] / 100), если используете поле cost из demand;
  • Накладные расходы — SUM([overhead_sum] / 100), если используете поле overhead_sum;
  • Контрагент — поле из связанного справочника контрагентов, если он добавлен в датасет;
  • Организация — поле из связанного справочника организаций, если он добавлен в датасет.

Некоторым бизнесам нужно отделять НДС от суммы документа, чтобы в ОПиУ смотреть выручку и расходы без налога. В таком случае создайте вычисляемое поле Сумма без НДС: оно должно быть результатом вычитания двух других полей — суммы документа и НДС.

Для фактического ОПиУ обычно нужно отфильтровать только проведенные документы: Проведен = true. Непроведенные документы можно оставить в отдельном контрольном чарте, чтобы видеть, какие продажи или закупки не попали в отчет.

Настройка полей датасета ОПиУ в DataLens

Как собрать чарт ОПиУ​

ОПиУ удобно начинать с помесячного графика:

  1. Нажмите Создать чарт из датасета.
  2. Выберите линейную или столбчатую диаграмму.
  3. Перетащите Дата документа на ось X и задайте группировку месяц.
  4. Перетащите Сумма на ось Y.
  5. Добавьте Тип документа в цвета.
  6. Добавьте фильтр по дате, организации и признаку проведения.

Настройка комбинированной диаграммы ОПиУ в DataLens

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

Пример дашборда ОПиУ с графиком и сводной таблицей в DataLens

Как связать с группами товаров​

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

Для этого нужно дополнять отчет позициями документов и группами товаров. Логику групп лучше подготовить заранее: как это сделать, мы разобрали в статье про группы товаров и услуг в МоемСкладе.

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