Alternative approach to parameters in Invantive Control to control query outcome

Question

I would like to use parameters in Invantive control.

For example, I would like to retrieve only the hours, of Exact Online Project management which, are in the given data parameters.

Answer 1

There are three often used approaches:

1. Use parameters in the model editor.
2. Use Excel values.
3. Use data from the databases involved.

Model Parameters

To use parameters in the model editor, you define them in the model editor:

• Select a unique code.
• Possibly provide a default value (always string, use to_... or casting to change it in the queries).
• Define list-of-values providing a quick pick.

The unique code is in general all in uppercase and in the format P_..., but as long it is a legal identifier anything should work.

To use them in one or more block queries or triggers:

• Use the building blocks button.
• Or type $P{CODE} in the SQL or trigger for Excel.

The values can be entered using the parameters button (the green funnel in the Control ribbon):

Please note that parameters are always bound as parameters, and not lexicographically substituted so you can NOT say: select * from $P{TABLE_NAME}.

Please note that parameters can be dependent on each other, so in the query for the parameter you can use another parameter. Such as first choosing the country of a project and then showing a list of projects in that country. But be wise, avoid recursion and other overly complex scenarios; the user will not easily understand it.

Excel values

To use Excel values, you can define them as follows:

• Enter a value somewhere in Excel.
• Optionally define a named range to make it easier to change the location.

You can of course assign lists as normal in Excel as a pop-up or other validations. Also cell locking works fine.

To use the actual value in a query or trigger of Invantive Control you can use the building blocks in the query editor or use something like select * from table where code = $X{projectcode} or select * from table where code = $X{B2&C2&D2}. The last one shows that you can also use other type of Excel expressions.

Note that Excel parameters are also bound as parameters to the query, but that they are also typed, so the following query can be different depending on the general format of the Excel cell:

select *
from   table
where  code like $X{CELL}

When cell is a text, the database or Exact Online in this case will retrieve:

select *
from   table
where  code like :ex0

With ex0 being a text such as '8%'. But when cell is a percentage, the contents might still display in Excel as '8%', but the actual query will be with identical to the outcome of:

select *
from   table
where  code like 0.08

Caused me some headaches, but typing is in general a useful feature, especially with dates.

Parameter using database data

Option 3 is practically not feasible with Exact Online, since they are little possibilities to create your own tables and/or fields.

On other platforms such as Oracle you might want to enter new rows in Invantive Control in Excel and them upload them on sync to provide parameters. Especially handy in case of complex risk models.