Эффективность многих формул Exact Online Excel для торговых операций
У нас есть электронная таблица Excel, в которой указаны все финансовые периоды по горизонтали (13, один год плюс один месяц) и по вертикали список кодов статей. Пересекающиеся ячейки содержат проданное количество за этот период статьи с использованием транзакций Главной книги в Exact Online.
Список кодов товаров, количества на складе и продаж заполняется с помощью следующего запроса:
select code_attr
, '=i_eol_itm_description(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' rownum
, '=I_EOL_ITM_SALES_PRICE_VALUE(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' dateend
, '=I_EOL_ITM_COSTS_PRICE_VALUE(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' datestart
, '=I_EOL_ITM_SALES_UNIT_DESCRIPTION(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' assortment_glrevenue_code_attr
, '=$C{D,.,.,.+2,.}-$C{D,.,.,.+1,.}' assortment_glrevenue_type_attr
, '=0' assortment_glrevenue_balanceside_attr
, '=I_EOL_STOCK_CURRENT_QUANTITY(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' ispurchaseitem
, '=I_EOL_STOCK_PLANNING_IN(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' issalesitem
, '=I_EOL_STOCK_PLANNING_OUT(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' isstockitem
, '=I_EOL_STOCK_PROJECTED_QUANTITY(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' assortment_glrevenue_balancetype_attr
, '=$C{D,.,.,.-1,.}*$C{D,.,.,.-5,.}' assortment_glrevenue_description
...
from exactonlinexml..items
order
by code_attr
С 750 статьями и 13 периодами к транзакциям в Главной книге обращаются тысячи раз. Поиск информации о транзакциях из Главной книги в Exact Online через XML API выполняется медленно при поиске транзакций статей, поскольку сначала необходимо загрузить все транзакции из журнала продаж, а затем отфильтровать транзакции без кода статьи (50%) и суммировать количество продаж.
Время выполнения составляет 45 минут и даже хуже, если API Exact Online работают медленно.
Как я могу улучшить время, необходимое для заполнения электронной таблицы?
1 ответ
Время выполнения было сокращено до нескольких минут для указанного объема данных с помощью триггера на уровне модели. Этот триггер запускает оператор SQL, который использует Exact Online REST API для получения суммированных объемов информации о транзакциях GL:
create or replace table itmrev@inmemorystorage
as
select itemcode
, mnd
, sum(qty) qty
from ( select itemcode
, year(date) * 12 + month(date) mnd
, quantity qty
from TransactionLines
where journalcode = '70'
and itemcode is not null
and date > to_date('20160101', 'yyyymmdd')
)
group
by itemcode
, mnd
Тогда вместо использования Excel I_EOL_GLTXN формула, я использовал следующую формулу:
select code_attr
, '=i_eol_itm_description(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' rownum
, '=I_EOL_ITM_SALES_PRICE_VALUE(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' dateend
, '=I_EOL_ITM_COSTS_PRICE_VALUE(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' datestart
, '=I_EOL_ITM_SALES_UNIT_DESCRIPTION(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' assortment_glrevenue_code_attr
, '=$C{D,.,.,.+2,.}-$C{D,.,.,.+1,.}' assortment_glrevenue_type_attr
, '=0' assortment_glrevenue_balanceside_attr
, '=I_EOL_STOCK_CURRENT_QUANTITY(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' ispurchaseitem
, '=I_EOL_STOCK_PLANNING_IN(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' issalesitem
, '=I_EOL_STOCK_PLANNING_OUT(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' isstockitem
, '=I_EOL_STOCK_PROJECTED_QUANTITY(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' assortment_glrevenue_balancetype_attr
, '=$C{D,.,.,.-1,.}*$C{D,.,.,.-5,.}' assortment_glrevenue_description
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & m$11)' assortment_glcosts_code_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & n$11)' assortment_glcosts_type_att
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & o$11)' assortment_glcosts_balanceside_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & p$11)' assortment_glcosts_balancetype_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & q$11)' assortment_glcosts_description
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & r$11)' assortment_glpurchase_code_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & s$11)' assortment_glpurchase_type_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & t$11)' assortment_glpurchase_balanceside_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & u$11)' assortment_glpurchase_balancetype_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & v$11)' assortment_glpurchase_description
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & w$11)' assortment_glpurchasepricedifference_code_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & x$11)' assortment_glpurchasepricedifference_type_attr
, '=SUM($C{D,.,.,.-12,.}:$C{D,.,.,.-1,.})' assortment_glpurchasepricedifference_balanceside_attr
, '=$C{D,.,.,.-1,.}/12' assortment_glpurchasepricedifference_balancetype_attr
from exactonlinexml..items
order
by code_attr
i_sql_select_scalar извлекает точное количество продаж онлайн из небольшой таблицы с итогами.
Добавление all_rows в качестве подсказки выполнения к формулам, таким как I_EOL_STOCK_CURRENT_QUANTITY принудительно загружает все элементы в память как один большой пакет в первой формуле. Хотя для вычисления первой формулы требуется больше времени, она значительно быстрее всех формул. Вместо 750 поисков отдельного элемента (XML API иногда занимает до 600 мс каждый), теперь заполнение информации об элементе в Excel занимает примерно 1 минуту.