New Formula Functions - power to the parameters!

You may have heard rumours that the next release is all about parameter and drill-down. Rest assured, its true. During the last months I started reading a bit about the inquisition and their reliable methods of customer surveys ("Are you a happy catholic? - Yes, ouch!, burn! - choose one") I did keep an eye on what problems come up most.

Guess what. Parameters range pretty high on the scale. And more specifically, dealing with multi-selection parameters is problem zone number one. But hey, we all know that there is no problem that cannot be solved by simply adding more code.

So lets start with a cook-book:

0. Multi-select parameter return their values as arrays and the engine expects arrays when we get parameter values from the outside.

1. Count the number of selected values:

=COUNTA([mparam])

Based on that, check whether multi-selection parameters have a selection:

=IF(COUNTA([mparam]) == 0; "do something if empty"; "do something if not empty")

2. Array Manipulation:

Merge two arrays

=ARRAYCONCATENATE([mparam]; [mparam2])

or merge with static array values:

=ARRAYCONCATENATE([mparam]; {"value 1" | "value2"})

Extract four values from a array: (Leading, Trailing, in between)

=ARRAYLEFT([mparam]; 4);
=ARRAYRIGHT([mparam]; 4);
=ARRAYMID([mparam]; 2; 4);

and the highlight of the show:

3. Query a single value from a database

=SINGLEVALUEQUERY("query"; "result-column"; [query-timeout]) 

4. Query multiple values from a database

=MULTIVALUEQUERY("query"; "result-column"; [query-timeout]; [query-limit]) 

In combination with the hidden parameters with a post-processing function, this can be as powerful as a laser weapon in a medieval village.

On a side note: Post-processing and formula validation is a bit quirky in the current versions (PRD-3.5 to PRD-3.6). Stay tune for the next article to see how a well-defined version (like the one shipping in PRD-3.7) will clear up the fog of confusion.