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.

Creating parametrized reports or combining several of these reports via drill-down-links is not necessarily the most enjoyable of all activities when it comes to writing reports.

A drill-down report is simply a report, that has a click-able link somewhere. The link itself then points to another report and also contains all the parameters to actually see that report run.

Within the Pentaho Platform, there are two URLs that are responsible for showing a report:

1. For XAction driven reports: Call the XAction handler with a suitable XAction and all the parameters

http://localhost:8080/pentaho/ViewAction?solution=samples&path=getting-started&action=HelloWorld.xaction


2. For PRPT-Reports: Call the Reporting-Plugin’s content handler with the report file and the parameters.

http://localhost:8080/pentaho/content/reporting/reportviewer/report.html?solution=samples&path=getting-started&name=HelloWorld.prpt


URLs are usually added via an style-expression on the links::url style-property with a formula similar to this one:

="http://www.google.com/search?q=" & URLENCODE([field])


This works reasonable well for Strings and Numbers. Complex types like Arrays or Date objects, however, need to be specially formatted. The new PARAMETERTEXT function provides a easy option to get raw data objects into the right format for parametrization in drill-down reports.

="http://localhost:8080/pentaho/content/reporting/reportviewer/report.html?solution=samples&path=getting-started&name=HelloWorld.prpt¶meter=" & URLENCODE(PARAMETERTEXT([field]))


To format values inside a formula via a format-string, we now offer the MESSAGE function and extended the TEXT function to allow more control when creating strings.

The MESSAGE function uses a java.text.MessageFormat to format values into text.

=MESSAGE("{0,number} chicken crossed the road on {1,date,short} to {2}"; ( 100 * 50 + 10); DATEVALUE(20, 5, 2005); "to visit grandma")

The TEXT function simply converts a value into text.  The optional second parameter specifies a number or date format to convert the value.

=TEXT(NOW(); "dd-MM-yyyy")


With Citrus, we added static parameters. Although this already brought us closer to our goal to total world domination for our reporting tool (Ya know, you have to have some small goals to make a life worthwhile) it is not enough to solve the more complex problems. Therefore, the Lemonade release pushes the frontier a bit further and allows a whole range of new features.

Hidden Parameters

By selecting a tiny little check-box, parameters can now be excluded from the UI-generation. The parameter itself still behaves like any other parameter and thus takes part in the formula-calculations and is part of the validation.

Parameter Validation can be loosened

When this flag is set to false, the validator will accept other values than the ones that have been specified in a list-parameter. With relaxed validation, you can easily write WHERE conditions, that accept partial matches or contain “LIKE” clauses. For the sake of security, we none-the-less default this feature to strict-checks.

Display-Formula

Every List-Parameter can now have computed display-texts. This way, you are no longer limited by what your datasource can produce, and even a simple data-source like an XML file or a static table-datasource can produce better looking prompts.

Post-Processing Formula

A post-processing formula processes the parameter-input before it gets validated or passed into the report. The post-processing can either be defined on the parameter itself or can be defined as an extra field by using a hidden parameter. The computed value will still be validated by the engine and thus using formula’s does not necessarily mean you have to fear that illegal input comes through. Formulas allow you to access runtime information, like the current user’s roles or the user’s name via the ENV(..) function.

All these features are available in the CI-build. To make use of them in the platform, you will have to upgrade the reporting-engine and the reporting-plugin of the 3.5-GA BI-server.

Next stop: Dynamic mondrian roles and dynamic security information for JDBC datasources.