Расчетная система АССОИ

ОСНОВНЫЕ ПОЛОЖЕНИЯ

Все взаимодействие с расчетной системой ведется посредством запросов. Запросы формируются формами, отчетами или вручную пользователями. Обязательными параметрами запроса являются:

• объект (предприятие или укрупненный объект типа дивизиона),

• время (год и период),

• строка (из «дерева» в базе)

• колонка (из таблицы в базе или по определению в колсете темы).

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

То есть например запрос «Уралэлектромедь, Январь 2013, строка «Запасы – m112222», колонка Б1» является первичным, так как все его параметры описывают период, объект, строку и колонку явно хранящиеся в базе данных.

Кроме первичных выделяют соответственно комплексные запросы, которые для своего вычисления требуют дополнительных преобразований. В АССОИ комплексные запросы могут быть определены несколькими способами, среди которых основными являются суммы (суммовые разделы, групповые суммы) строк и формулы. Формулами могут быть заданы объекты, периоды, строки, колонки.

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

В данном документе подробно описаны формулы строк и колонок в синтаксисе boo – основной используемый вариант формул в АССОИ.

В запросе и строка и колонка, период и объект могут одновременно оказаться формулами, в этом случае сначала обрабатываются формулы касающиеся времени (год/период), затем объектов, потом колонок и в последнюю очередь строчные.

ФОРМУЛЫ

Формула представляет собой обычное выражение (подобное Excel), включающее в себя:

1. Арифметические операторы - +,-,*,/ и т.д. – обрабатываются обычным образом

2. Числовые (или в редких случаях строчные) константы – 1000, 1.5, «строка»

3. Логические операторы – and, not, or – обрабатываются обычным образом в условных выражениях

4. Скобки для группировки выражений – (…) – обычные, любой степени вложенности

5. Скобки для объявления вложенных функций {…} – используются в редких случаях (например в функции f.if) и будут рассмотрены ниже

6. Вызов функций, предусмотренных в системе (округление, f.If и т.д.)

7. Подзапросы

Подзапросы – ключевой момент формул. Они являются аналогами обращений к другим ячейкам в формулах Excel с той разницей, что подзапрос может обращаться к любому элементу базы АССОИ. При этом как и в случае с Excel подзапросы обычно задаются не полными координатами, а относительными - относительно исходного запроса.

Каждый подзапрос состоит из набора смещений исходного запроса и заканчивается символом «?» что означает «вычислить данный подзапрос и подставить сюда значение вычисления».

Порядок смещений фиксирован:

1. Смещение строки ( $[КОДСТРОКИ] )

2. Смещение колонки ( @[КОДКОЛОНКИ])

3. Смещение года (.Y[ГОД])

4. Смещение периода (.P[ПЕРИОД])

5. Смещение объекта (.toobj ( [ИД или ОБЪЕКТНОЕ ВЫРАЖЕНИЕ] ) )

6. Ограничение счета (.conto ( [СЧЕТА]) )

7. Ограничение контрагента (.altdiv, .altobj, .altgrp …)

Подзапрос может содержать любой набор смещений из этого списка, например строка и год, но их взаимное расположение должно соответствовать указанному в списке, то есть:

Правильно: $m111@Б1?

Неправильно: @Б1$m111?

Также бессмысленно включать в формулу несколько смещений одного типа (иногда встречается как попытка выразить суммы или что-то подобное):

$m111222$m111322? – это выражение бессмысленно, в новой версии АССОИ приведет к ошибке, в старой будет эквивалентно последнему элементу - $m111322?

Рассмотрим пример формулы:

В этом выражении:

1. f.round ( …) - вызов функции округления до целого для значения в скобках

2. $m120110? – подзапрос, который указывает, что следует вычислить значение, заменив в исходном запросе строку, содержащую формулу, на строку с кодом m120110

3. [email protected]? – второй подзапрос, который указывает, что надо вычислить значение, сместив строку на строку с кодом m120112, колонку заменить на колонку с кодом Pd, а год уменьшить на 1

В итоге формулу следует читать так: «вычислить округленное значение суммы значения по строке m120110 и значения прихода (Pd) по строке m120112 за предыдущий год».

Как видно из описания подзапросов, они заданы относительно исходного запроса и в этом примере в обоих подзапросах остаются без изменений период и объект.

Для простоты предположим, что строки m120112 и m120110 первичные, равно как и колонка Pd.

Допустим на входе у нас такой запрос:

Встретив среди параметров формулу (у строки), система не пытается сразу вычислить значение по базе, а пытается заменить формулу более простыми запросами. В данном случае получится (курсивом отмечается не измененная часть):

Так как строки m120112 и m120110 первичные, то и оба получившихся подзапроса первичные и соответственно можно вычислить их значения из базы.

Допустим, что

[объект=352, год=2013, период=16, колонка=Б1, строка= m120110] = 756.4 т.р.

[объект=352, год=**2012, период=16, колонка=Pd, строка= m120112**] = 223.2 т.р.

И теперь система вычисляет итоговое значение:

То есть значение строки m200305, для которой была написана формула – 980 т.р.

Есть и более сложные случаи формул, которые мы кратко обозначим:

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

2. Запрос или подзапрос содержит сразу несколько формул – и для строки и для колонки например, в этом случае происходит комбинация формул в той очередности, которая описана выше (объект, время, колонка, строка) – сначала система выполняет ВЕСЬ НАБОР ЗАМЕН ПАРАМЕТРОВ, а потом ВЫЧИСЛЯЕТ формулы в порядке приоритета. Следует избегать таких запросов если нет точной уверенности что вы понимаете какие именно в результате получатся значения, так как несколько формул, выполняемых одновременно могут дать в каких-то случаях сложно предсказуемый результат.

Из соображений читаемости формул и совместимости с АССОИ в версии 2010-2012 годов следует все подзапросы окружать справа и слева хотя бы одним пробелом:

ЭЛЕМЕНТЫ ПОДЗАПРОСОВ

Синтаксис формул будем рассматривать на условном унифицированном примере где для наглядности представим, что может запрашивать система (какой объект, год, период, строку, колонку…) и во что преобразуется исходный запрос при применении той или иной формулы.

Смещение $(строка)

Данная конструкция позволяет сменить строку в запросе, например:

В данном случае это означает, что при обращении в систему выдать данные по строке, содержащей формулу, такая конструкция перенаправит запрос и выдаст данные из строки m250500.

Смещение @(колонка)

Данная конструкция позволяет сменить колонку в запросе, например:

В данном случае это означает, что при обращении в систему выдать данные по колонке, содержащей формулу, такая конструкция перенаправит запрос и выдаст данные из колонки OKF.

В данном случае это означает, что при обращении в систему выдать данные по строке, содержащей формулу, и колонке Б1 система выдаст данные из строки m220751 и привязанные к колонке с кодом Pd.

Смещение .P(период)

Данная конструкция позволяет сменить период в запросе, например:

В данном случае это означает, что при обращении в систему выдать данные по строке, содержащей формулу, в периоде 14 (апрель) система выдаст данные из строки m1122110 и привязанные к другому периоду – в данном случае 251 (основная версия плана на год).

Конструкция задающая период .P251 может быть соответственно и .P1 и .P13 и т.д., что означает, что данные в строке будут выведены за 3 месяца и за март соответственно и т.д.

Формульный период

Кроме обычных периодов точно также могут использоваться «формульные периоды», значения которых начинаются со знака минус «-», например -201, -303 и т.д., например:

Преобразование периода 2 в 306 выполняется по внутренней логике прописанной в периоде -303. Значения этих формул следует смотреть в описаниях периодов в базе данных.

Смещение .Y(год)

Данная конструкция позволяет сменить год в запросе, например:

В данном случае это означает, что при обращении в систему выдать данные по строке, содержащей формулу и году со значением 2013 такая конструкция перенаправит запрос и выдаст данные из строки m1122110 и привязанные к 2012 году, т.е. на 1 год назад (что задано конструкцией .Y-1).

Конструкция задающая период .Y-1 может быть соответственно и .Y-2 и .Y-3 и т.д., что означает, что данные в строке будут выведены на 2 и на 3 года ранее, соответственно и т.д. Год можно указать и «вперед». Например .Y1 - в этом случае для 2013 года будет взят 2014.

Конструкция .Y может быть указана и с конкретным годом. Например, .Y2013, тогда при любом годе в исходном запросе будет использоваться именно 2013 год.

Смещение .toobj(объект)?

Данная конструкция позволяет сменить объект в запросе, например:

В данном случае это означает, что при обращении в систему выдать данные по строке, содержащей формулу и объекту (в данном случае предприятию) имеющему ID**=352 система выдаст данные из строки m204137 и привязанные к предприятию с ID=357**

Смещение .conto(счет)

Пример:

Данная конструкция позволяет задать сумму по младшим объектам с ограничением на их тип. В данном случае это сумма только по основным производственным филиалам.

Отметим, что конструкция .conto на сегодняшний момент ограничивает тип младшего объекта и чаще всего это счет. Но когда будет обновленная модель это и будет ТОЛЬКО СЧЕТ, а не фильтр младших объектов.

Смещение .altgrp(группа контрагента)

Пример:

Данная конструкция позволяет задать значение по группе контрагентов в запросе. В данном случае G1 – код группы «Директорат», в которую входят основные предприятия УГМК.

Смещение .altobjfilter(конкретный контрагент)

Пример:

Данная конструкция позволяет задать значение в запросе по конкретному контрагенту. В данном случае 1788 – это ИД предприятия Газпромбанк.

Смещение .altdiv(дивизион контрагента)

Пример:

Данная конструкция позволяет задать значение по дивизиону контрагентов в запросе. В данном случае BANKS - код дивизиона Банки.

КОНСОЛИДАЦИЯ ПОДЗАПРОСОВ

Консолидация .consobj (список кодов предприятий)

ФУНКЦИЯ f.If

Синтаксис функции

Функция условного перехода f.if ( условие, { запрос если ДА }, { запрос если НЕТ } )

Пример:

Данная конструкция задает Условие к параметру запроса системы. В данном примере задается условие к колонке PLANC через константу colin. Затем, в фигурных скобках, через запятую задаются значения того, во что преобразуется в итоге запрос системы как в случае выполнения, так и в случае невыполнения данного условия. В нашем примере при выполнении условий произойдет произойдёт перенаправление запроса к строке m220300, а при невыполнении условий произойдет перенаправление запроса при котором система выдаст данные из колонки Pd и строки m220300.

Пример 8 Если условие выполняется

Пример 9 Если условие не выполняется

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

В данном случае условием формируемого запроса к системе является то, что колонка не будет иметь код PLANC, то есть условие подразумевает все колонки кроме данной. Из примера также видно, что запрос, в который должна преобразовать система при невыполнении условия { запрос если НЕТ }, может отсутствовать и следовательно, при невыполнении условия значения будут нулевые. Конструкция в данном случае будет выглядеть так:

Конструкция f.if ( условие, { запрос если ДА } )

Например:

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

Написать про вложенность, порядок обработки системой

Написать про способы соединения, запятую, and or

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

Конструкция colin

Условие задаваемое к колонке описывается через синтаксическую конструкцию colin

Конструкция year

Условие задаваемое к году, описывается через синтаксическую конструкцию year:

В данном случае, если при обращении системы к параметру год, он будет больше чем 2010 (то есть 2011, 2012 и т.д.), то система будет преобразовывать запрос по формуле $m1111240@DELTA? и если год будет 2010 и меньше (2009, 2008 и т.д.), то система будет преобразовывать запрос по формуле $m211250@Б1? - $m211250@Б2?.

Отметим, что здесь могут применяться любые арифметические знаки «<», «<=» «=», «>»…

Конструкция periodin

Условие задаваемое к периоду, описывается через синтаксическую конструкцию periodin:

В данном случае система будет преобразовывать запросы исходя из условия, что период будет равен periodin ( 4 )

Условие задаваемое не к просто периоду, а к сумме описывается через синтаксическую конструкцию issummperiod :

чтобы номера не перечислять это суммовые типа 3, 4, 5, 6 мес. …

Условие задаваемое ко всем плановым периодам описывается через синтаксическую конструкцию isplanperiod :

Конструкция objin

Условие задаваемое к ИД предприятия, описывается через синтаксическую конструкцию objin:

В данном случае система будет преобразовывать запросы исходя из условия, что ИД предприятия будет равно 529.

Конструкция grpin

Условие задаваемое к группе текущего предприятия описывается через синтаксическую конструкцию grpin:

Конструкция pathin

pathin - применяется для соответствия целым подразделам дерева

Конструкция contoin

contoin работает только если в качестве объекта на входе младший объект (деталь). В этом случае проверяет счет (тип) младшего объекта (детали)

Конструкция treeallin

treeallin** **проверяет, что в текущей строке и выше по дереву в группе, метке, теге или коде есть указанные строки.

Например treeallin("Au") вернет «Да» для всех строк в которых или выше по дереву есть группа Аu, тег Аu, метка Аu или код Аu

Конструкция pathin

pathin** **проверяет на соответствия целым подразделам дерева

В этом случае задать значение, высчитанное формулой для всех узлов дерева кроме заданных кодом "m112281", "m112285", "m112800"

Конструкция treetagin

treetagin проверяет, что в теге есть указанные строки.

Конструкция rowallin

rowallin проверяет, что в строках есть указанные строки.

ФУНКЦИИ ПОДЗАПРОСОВ

Функция f.choose

f.choose задает условие к значению. Если значение, высчитанное по первому запросу ($OFS@OFK?) будет равно нулю, то считать по второму запросу ($OFS@OFK.torootobj()?)

Функция f.round

f.round позволяет округлить значение, которое будет сформировано после выполнения системой запроса, например:

В данном случае значение которое будет посчитано при выполнении запроса $r211710@Pd? - $r260135410@SUMMA? - $r260135410@SUMMACALC? будет округлено до целого значения.

Функция MCOUNT

Данная конструкция позволяет задать количество месяцев в периоде исходного запроса, например:

В данном случае это означает, что при обращении в систему выдать данные частного между строкой m140958 и t110300 и еще поделить их на количество месяцев, которые содержатся в указанном периоде. Так, если период (3) , то поделить, в данном случае нужно на 9 месяцев, если период на входе (11,12,13… ) или любой другой месячный, то MCOUNT=1 и т.д.

Функция f.checklimit

q.remapcolumns( ("In", ("m230"), "DZn" ), "NORESULT" )?

ИЗМЕНЕНИЕ ПОРЯДКА ВЫЧИСЛЕНИЙ ФОРМУЛ

При вычислении формул для текущей ячейки, у которой строка и колонка являются формульными, применяется правило приоритетов: выполняется формула того элемента (строки или колонки), который имеет более высокий приоритет. По умолчанию строка имеет приоритет 0, колонка - 1.

При необходимости можно изменить приоритет вычислений формул строк и колонок. В старой системе для этого у формульной колонки устанавливалсь метка /DOSUM/. В новой системе в таблице core.Cols устанавливается значение 1 для поля DoSum. В этом случае для всех строк по текущей колонке устанавливается приоритет 2 в вычислениях.

Наиболее часто данный прием используется для расчета суммовых показателей по исходным значениям, которые рассчитываются как произведение двух и более удельных показателей. Например, нужно рассчитать сумму по группе бензина на основе данных цен и количества по маркам бензина . Формула расчета Суммы = Количество бензина х Цена бензина. Исходные данные представлены в следующей таблице (исходные данные выделены цветом):

В случае применения приоритетов вычислений по умолчанию формула расчета суммы будет выглядеть следующим образом:

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

Если же изменить приоритет вычислений путем установки для колонки c3 метки /DOSUM/ или значения 1 для поля DoSum, то в этом случае порядок вычислений будет выглядеть следующим образом:

В результате изменения приоритета вычислений мы получили правильный результат.

УПРОЩЕННАЯ МОДЕЛЬ РАСЧЕТА ПОДИТОГОВ

Наиболее часто используемой в практических вычислениях является формула суммы дочерних элементов по дереву строк. Для этого у любого узла дерева, содержащего дочерние узлы, может быть установлен признак IsSum (метка 0SA в старой системе), в результате чего данная строка становится расчетной, которой сопоставляется виртуальная формула, равная сумме строк всех дочерних узлов, которые, в свою очередь, также могут быть суммовыми и формульными.

Данный механизм позволяет поддерживать в актуальном состоянии формулу расчета суммы дочерних строк при изменении структуры дерева. Вместе с теме, установка (снятие) признака IsSum требует сброса кеша контекстных формул.

Кроме базовой функциональности механизм суммирования дочерних строк расширяется за счет следующих механизмов:

1. Исключение из подсчета итогов строк с признаком NoSum (меткой 0NOSUM в старой системе).

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

3. Изменение знака значения по строке при наличии признака IsMinus (метка 0MINUS в старой системе).

4. Переопределение расчета суммовых итогов на основании дополнительных признаков строк и колонок.

Исключение из подсчета итогов строк с признаком NoSum

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

В этом случае для строки устанавливается параметр NoSum, после чего строка исключается из формирования суммовой формулы родительского узла.

Подсчет итогов по суммовым группам

Наряду с линейным подсчетом суммы по всем дочерним узлам применяется подсчет произвольного набора строк по суммовой группе. Суммирование производится по строке, по которой значение атрибута IsSum установлено в 1 и которой сопоставлено какое-либо значение суммовой группы. Сумма рассчитывается по первичным строкам, содержащим суммовую группу, по всему дереву строк.

В настоящее время подсчет строк осуществляется ТОЛЬКО по первичным строкам. Это ограничение связано с необходимостью исключить формирования циклических расчетов. По мере совершенствования расчетчика данное ограничение может быть снято.

Изменение знака значения по строке

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

Переопределение расчета суммовых итогов

В некоторых случает может потребоваться изменение области действия атрибута IsSum для строки. Для этого последовательно устанавливаются определенные атрибуты для строк и колонок, которые изменяют на противоположный возможность расчета суммы для текущей строки (по дочерним узлам или по суммовым группам).

Порядок переопределения сумм заключается в следующем:

1. Возможность расчета суммы по строке производится путем установления у СТРОКИ атрибута IsSum.

2. Отключение расчета суммы по строке для определенной колонки производится путем установления у КОЛОНКИ атрибута NoCalcSum (например, для исключение суммирования значений по колонке "Цена").

3. Включение расчета суммы по строке с атрибутом IsSum для колонки с атрибутом NoCalcSum производится путем установления у СТРОКИ атрибута IsCalcSum (данная возможность используется, если для одной колонки с расчетом удельных показателей для разнородных показателей нужно исключить возможность суммирования, а для однородных - включить).

4. Отключение расчета суммы по строке с атрибутами IsSum и IsCalcSum производится путем установления у КОЛОНКИ атрибута NoCalcSumHard (редко встречающаяся потребность, но тем не менее существующая).