Sorry, next year Santa wont be able to come to town

.. cause I've eaten his reindeers. In case you ever come to Sweden, this is one of the things you should not miss out. You may not want to tell your kids what they are eating right now, but once they got hooked in, they would not care anyway. At the minimum I'm now able to answer the question on "What is the most important question in a human's life?". It's simple: "Can I eat it?". This question works perfectly well for cows or sheep and of course also for crocodiles, emu or kangaroos. So far I'm reluctant to try to eat Gators, as I do not want to end in a movie (or upset some of the folks in Orlando, for that matter). There is a whole world to eat - except for some weird stuff, of course - so bring it on!

From the feedback I received so far, the new Mondrian role feeding system seems to work pretty well. There was only one tiny bit missing: Mondrian reacts absolutely unhappy, if it encounters roles it cannot understand. If your roles come from a external source, like LDAP, you usually have more roles there as are defined in Mondrian. Even in a standalone, all in-memory solution, the platform roles very likely form a super-set of the defined Mondrian roles. To make Mondrian happy without too much manual work (or heaven forbid: work on every report-definition), we now have a role-filtering system for both the OLAP4J and Mondrian datasources. The filter is a classical deny-then-accept filter usually found in firewalls. You can remove known bad roles and you can have a known good roles list. All the filter-rules are enabled via the global config stored in the famous "classic-engine.properties" file. For the BI-Server, this file can be found in the "pentaho/WEB-INF/classes" directory. For the Pentaho Report Designer, the file is in the "resources" directory.

# Enables or disables the filter code. Is disabled by default. org.pentaho.reporting.engine.classic.extensions.datasources.mondrian.role-filter.enable=true

Filtering is controlled via 4 sets of properties. Deny wins over accept. You can either filter by static comparison or via reg-exp matching. There can be multiple keys per category, just make the "name" at the end unique. This greatly helps with long-term maintenance as a 10.000 chars regexp is no fun to edit each time someone adds or removes a role. In case there is no accept rule in your configuration, a default accept-all rule gets active.

org.pentaho.reporting.engine.classic.extensions.datasources.mondrian.role-filter.reg-exp.accept.<name>=*rolename* org.pentaho.reporting.engine.classic.extensions.datasources.mondrian.role-filter.reg-exp.deny.<name>=*regexp suitable for String.matches(..)* org.pentaho.reporting.engine.classic.extensions.datasources.mondrian.role-filter.static.accept.<name>=*rolename* org.pentaho.reporting.engine.classic.extensions.datasources.mondrian.role-filter.static.deny.<name>=*regexp suitable for String.matches(..)*

Filtering is only active, if the role is read from a field, and if the value read is an array. You can use the "env::roles-array" safely to read the roles from the server and the filter rules will make sure that Mondrian is happy too. The mapping between environment names and data-row names (and as side-effect, the definition whether a environment property can be interpreted as array) can also be defined in the global configuration. The following code snipplet shows the default configuration.

# # Defines, which environment properties are published as fields in a report. The mapping is a # environment-name to data-row column name mapping. # # Any environment property name that ends with "-array" will be read without the "-array" suffix # and will be interpreted as CSV string that can be parsed into an array of strings. org.pentaho.reporting.engine.classic.core.env-mapping.serverBaseURL=env\:\:serverBaseURL org.pentaho.reporting.engine.classic.core.env-mapping.pentahoBaseURL=env\:\:pentahoBaseURL org.pentaho.reporting.engine.classic.core.env-mapping.solutionRoot=env\:\:solutionRoot org.pentaho.reporting.engine.classic.core.env-mapping.hostColonPort=env\:\:hostColonPort org.pentaho.reporting.engine.classic.core.env-mapping.username=env\:\:username org.pentaho.reporting.engine.classic.core.env-mapping.roles=env\:\:roles # # The roles-array env-key is a virtual key. This returns the roles as parsed string-array. org.pentaho.reporting.engine.classic.core.env-mapping.roles-array=env\:\:roles-array

Properties from the Server's session can be accessed via session::attribute-name. The value stored in the HTTP-Session must be a String. Example: if you have a string stored by the name magic-attribute, then you can access that string via the environment property name session::magic-attribute.

Back to the start - MDX parametrization revisited

After a million or so complaints about the complexity of building parametrized queries with the PARAMETER function within MDX, today I indulged to the demands of the crowd.

Beginning with Milestone 2 of the Pentaho Report-Designer 3.6,we now support parameter injection via the ${parameter} syntax. Parameter values injected via that syntax will not be checked in any way, so it is the designer's responsibility to ensure that everything is quoted correctly to cause no harm or to break the query. With great powers comes great responsibility.

The ${parameter} syntax for MDX is not just a toString() conversion. It follows the MessageFormat syntax and thus allows to format Date and Number objects properly before inserting them into the MDX query. An extended format rule allows to produce quoted MDX-string literals by specifying the subformat <string. These strings start and end with a double-quote and all double-quote characters found in the original string get escaped according to the MDX grammar.

So now I can finally answer the question on how to parametrize a Date-Axis from a Date-parameter. To produce a member string like [2009].[10].[4] from a parameter called dateparam use [${dateparam,date,"yyyy"}].[${dateparam,date,"MM"}].[${dateparam,date,"dd"}] in your MDX query.

I still haven't found out how to do the same with the PARAMETER function.

Support for the PARAMETER function will remain there (as in theory it is a good idea to have prepared/explicit parameter).


You can test this functionality with either the latest CI build or with the upcoming Milestone 2 of the Report-Designer 3.6.

Pentaho Reporting 3.6 Milestone 1

Finally we compiled all recent changes together to ship the milestone release of the next release of the Pentaho Reporting System. The release, as usual, contains the Pentaho Reporting Engine, the Pentaho Report Designer and the Pentaho Reporting Plugin for the BI-Server. This release centres around making the XAction-less mode more viable. The heart of the changes is made up by the new formula-parameter system. Formula parameters provide the ability to

• compute display-values for list-parameters
• post-process user-input
• derive new parameters out of the existing input
• validate the user-input against business logic rules

Parameter can now be hidden, so that they do not show up in the user-interface. Hidden parameters in combination with the post-processing formula provide the derived parameter ability. The other major shortcoming of the 3.5 release circled around the ability to define the various security settings for SQL and Mondrian/MDX datasources. All data-sources now can define values for the username/password and Mondrian/OLAP4J role properties. These settings can be either given as static values, hardcoded by the report-author, or can be read from a field or parameter at runtime, and thus can originate from a hidden/computed parameter. To read values from the server-side session, the ENV-formula has been extended to look for session-attributes. A formula like =ENV("session:my-attribute") will look at the session of the server and search for a attribute named "my-attribute". If the attribute is a string, it will be returned and can be used in the report. ENV can now also be used to retrieve Mondrian-roles from the platform. Roles can be returned as String (ENV("roles")) or as array (ENV("roles-array")). And last bug not least, to reduce the manual work when setting up data-sources, all well-known environment properties are now automatically imported into the data-row. Environment-Properties like "roles" therefore available as a report-field called "env::roles". Which environment properties are considered "well-known" is controlled via the global report-configuration, so it should be easy to adapt this schema to your organization's defaults. To see these changes in your BI-Server installation, you will have to update the reporting-plugin and the various reporting-jar files. The download for the reporting-plugin contains detailed instructions on how this is done. We will stop with the development work on this release somewhere within the next 2 to 3 weeks. The final GA release should come shortly afterwards. Pentaho Reporting 3.6 will be part of the 3.5.2 bug-fix release of the BI-Server. The purpose of this milestone release is to gather as much feedback on the real-world viability of both the formula-parameters and the security setting enhancements as possible. When test-driving this release, please give us feedback on how well we hit the requirements of your organization and how easy it is to get reports up and running in your environment. Don't hesitate to tell us all about any repetitive steps you have to take to get reports up and running, so that we can take steps to eliminate or at least minimize those. And now grab your copy from Sourceforge at http://sourceforge.net/projects/jfreereport