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.

Its about time .. a better date parameter handling in PRD-3.7

For a long time date-parameters were probably the most creepy new feature that we had since finishing the Citrus release a year ago. Unlike a text or a number, the absolute value of dates is dependent on the runtime environment.

Yes, you may have guessed it: Timezones - I just hate them. Not that they give you jetlag when traveling, they also mess up date parsing when you are not very very careful.

First, Java does not have a sane date system. It basically just inherits the low-tech mess of C/C++ and tries to disguise that by calling the milliseconds since 1.1.1970 a "Date" object. But at the end of the day you don't deal with dates (the day-month-year part, not the time within a day) - you deal with timestamps.

And thus the date "2010-05-30" parsed with the UTC timezone yields something different than parsing the date "2010-05-30" in "Eastern Time". And if you're not careful your database will happily jump on the wagon and will return a few hours more or less of your data. I can tell you: the IRS loves randomized balance sheets.

Up until now, the parameter handling for date parameters was as random as it could be. In theory, date parameters are supposed to be passed in as standardized ISO formats. The ISO format yyyy-MM-dd'T'HH:mm:ss,SSSZ is used to transport timestamps in a locale and timezone independent fashion. On the BI-Server, however, dates were always passed around using the format 'yyyy-MM-dd' ignoring all time or timezone information. This made it impossible to use time or timestamp parameters there. Ultimately this led to case PRD-2624. Cutting off user input is bad - but it is worse when such bugs stay for a while, as at that point people develop workarounds which (due to the magic of "stay backward compatible and do not break existing reports") block us from implementing a clean fix. So we create a workaround for the workarounds. Bah! Complexity is the enemy of a good design.

Beginning with PRD-3.7 and BI-Server 3.7 date parameters for PRPTs follow a better schema. Each date-parameter can define whether the date given is a client-side, a server-side date or whether it should be interpreted in a fixed timezone (UTC, for instance) regardless of the client's or server's location.

(XActions and all other components work around that problem by declaring everything as string and thus offload the problem into the user space. Use JavaScript to parse your date. The target audience there are sys-admins and technically skilled users anyway - they are used to pain.)

Existing reports now parse the time-stamps correctly and thus at least enable the use of time and timestamp parameters. The timezone for those reports always defaults to server-side processing - and the parameter UI correctly takes that into account. Of course URLs that have the old short dates specified are still accepted - but you may see a deprecation warning in the logs now. And last but not least, the date picker now also allows you to enter time information into the text field if your data-format pattern allows it.

The Pentaho Wiki contains the complete story on how dates should be passed around.