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.

Related posts:

  1. Formula-Functions for simplified parametrization in Pentaho Reporting Lemonade
  2. Smokin’ new Parameter: Formulas, Hidden Parameters, and relaxed Validation
  3. Fixed rules: More on Parameter processing
This entry was posted in Parameter on by .
Thomas

About Thomas

After working as all-hands guy and lead developer on Pentaho Reporting for over an decade, I have learned a thing or two about report generation, layouting and general BI practices. I have witnessed the remarkable growth of Pentaho Reporting from a small niche product to a enterprise class Business Intelligence product. This blog documents my own perspective on Pentaho Reporting's development process and our our steps towards upcoming releases.

6 thoughts on “New Formula Functions – power to the parameters!”

  1. Martin

    Hi Thomas, could you post some examples for use of =SINGLEVALUEQUERY(“query”; “result-column”; [query-timeout]) ?

    Best Regards

    Martin Stangeland

  3. Domenico

    Hello,
    how can i compare the result of the following formula ARRAYLEFT([parameter_list];1) with a number?
    Actually, even if it gives me the current integer, I did not managed to compare it with another integer.
    What am i doing wrong?

    Thanks
    bye

  4. Peter

    It is possible to Hide/Show an report parameter with javascript (Global Scripting), based on status of other parameter.

    For example:
    I have a drop down parameter named “Filter by” with “Period” and “Dates” elements.

    If I choose the “Period” element from “Filter by” parameter, then I want to display a drop down with periods. If I choose the “Dates” element from “Filter by” parameter I want to display two date pickers named “From” – “To”.

    Thanks for your support!

    1. ThomasThomas Post author

      No, at the moment the report parameters are 100% static. Our ‘official’ workaround for that – at the moment – is to use CDE to create a parameter-provisioning dashboard/page and let the report run as one of the components there.

  5. Phanie

    Morning all, i am having a serious issue with the output on excel (since this is going to be reuse for calculation). I explain, i have many queries done and i found out that to display them all on the master report, i either got to use singlevaluequery or the subreport. however with the subreport i found it difficult to display on excel without control over the hidden cell.

Comments are closed.