Include Excel formula in block generated from SQL

Question

Running Invantive Control for Excel, I want to fill a block with data from Exact Online. However, one of the columns should be the multiplication of the rate listed in a settings Excel worksheet with the amount in another column.

When both values are available in SQL, I can just do a multiplication. Similarly, by hand I can (after synchronization) add a column to the right of the block with Excel the formula.

But I seem unable to automatically include an Excel formula in the cells of the block.

How should I do this?

score 2 · Accepted Answer · answered Mar 19 '19 at 08:50

In the query associated with an Invantive Control block you can include formulas in three variants:

select pjt.code
,      pjt.description
,      pjt.budgetedamount
,      '=2*3' formula1
,      '=b4*2' formula2
,      '=$C{D,.,.,.-1,.}*2' formula3
from   exactonlinerest..projects pjt
order
by     pjt.code

The column formula1 has a static Excel formula which is evaluates to 6. The Excel formula must follow the Excel formula syntax in English, so use sum() instead of som().

The column formula2 uses a value taken from an Excel expression outside of the block, in this case from the cell B4.

The column formula3 uses the most advanced type of formula with so-called Cell Reference Expressions. Commonly used cell reference expressions are available in the building block menu as shown in picture (Dutch):

However, when you synchronize the model like this, formula3 will raise an error since you need to make explicit that a value may contain a column expression. For this reason, go to the tab 'Fields' of the block and check the checkboxes in column 'Formula' and 'Replace Column Expressions':

The result of synchronization will be something shown in picture:

Include Excel formula in block generated from SQL

1 Answers1