Pentaho Reporting’s Metadata DataSources now with more scripting power

Pentaho Reporting is rather flexible. No, astonishingly flexible. Calculations for styles, attributes and even queries make sure that there is always a way to tweak a report exactly the way you need it. But until now, there were a few things that were not exactly easy. Custom Data-sources and their method of calculated queries were one of them.

With today’s code drop, this story has fundamentally changed. The Pentaho Metadata data-source is the first data-source that combines static queries with optional scripting features. There is no need to have a “Custom-Metadata” datasource anymore. No need to cram your query calculation logic into a tiny field on the report’s query-attribute.

Here it is: The new and greatly improved Pentaho Metadata Data-Source:

The scripting extensions hide beneath the two new tabs on the top and on the query-text area. Those who don’t need scripting or who are scared of having to program can safely continue with their life – just ignore the scripting-tabs and you will be safe in your static world. But if you feel you are born to greatness and therefore you cannot be limited to predefined queries, then follow me on a exciting journey into the land of power-scripting.

The scripting extensions are split into a global script and a per-query script. The global script can be used to define shared functions or global variables that can be seen by all query-scripts. The init()function can also be used to change the configuration of the data-source itself. The per-query-scripts allow you to (a) customize the query string, (b) calculate the “additional fields”  information for the query-caching and (c) allows you to post-process the returned table-model.

The original “Custom-*-Datasources” always had the problem that you can only add one of these datasources per report or subreport. Now that we combine scripts with named queries, we can have a independent script for each of the queries and of course as many datasources as necessary.

The scripting backend uses the JSR-223 (javax.script) scripting system. By default we ship with both JavaScript and Groovy support. There are quite a few JSR-223 enabled languages out there, so if you have a favourite language, just drop it into the classpath on both the Pentaho Platform server and the Pentaho Report Designer and you are ready to code.

The picture above shows you the global script pane. For the two languages (JavaScript and Groovy) we ship with, we have a prepared template script that contains some documentation as well as the empty function declarations of the functions we expect to be able to call.

It is absolutely OK to delete any function you dont need – in that case Pentaho Reporting simply ignores that part. This will help you to keep your scripts small and simple. You can load scripts from external sources as well, but be aware that it is your responsibility to make these external scripts available to the report at runtime. So if you load a script from a local directory, you have to ensure that the same works on the BI-Server (or your production environment) too.

For the upcoming Pentaho Reporting 3.9.0 release, the Pentaho Metadata datasource is the only datasource with this sort of scripting. Once that design has proven itself and I have more feedback on how well it works, we will add this scripting system to all major data-sources in Pentaho Reporting 4.0.

This entry was posted in Advanced Topic, Development 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.

4 thoughts on “Pentaho Reporting’s Metadata DataSources now with more scripting power

  1. Justin Barrett

    This sounds like a pretty cool feature, but is there any documentation on how to use it?! I can’t seem to find jack on the Pentaho website.

  2. Jay Myerscough

    Thomas, can the global scripting be used to set the jndi to be used? I’d like to set the jndi name to be that of a parameter within the url.

    If not, have you any tips on how this can be achieved?

    Cheers

  3. Thomas Morgner

    The “init(dataRow)” section of the script has access to the dataFactory and has access to the parameter of the first query to be executed on that datasource.
    The current data-factory instance is available via the “dataFactory” object. For SQL data-factories, this will be a SQLReportDataFactory class.

    Your data-factory has to be configured to use JNDI in PRD before you can modify the configuration via the script.

    [code]
    // In groovy, use “def” instead of “var”.

    var o = JNDdataFactory.getConnectionProvider();
    // if you run inside the platform, then your provider will be a different class than when running outside of the platform
    if (“org.pentaho.reporting.platform.plugin.connection.PentahoJndiDatasourceConnectionProvider”.equals (o.getClass().getName())
    {
    o.setJndiName(dataRow.get(“your-parameter”));
    }
    else
    {
    o.setConnectionPath(dataRow.get(“your-parameter”));
    }
    [/code]

    (Disclaimer: I dont have a compiler at hand right now to test that code, but I am confident that this works as advertised.)

  4. John More

    As with any information source; but, especially anything to do with the Pentaho Suite dates are critical. Being a newbie to actually using Pentaho community releases I find it exceedingly difficult to find meaningful current release information. While I have found this forum to be more useful on reporting than the official community forum it is frustrating to read an article and then try to find the defined function in the product only to discover after long and arduous research. the reference was to an older release and it is no longer supported or is supported in a entirely different way in the current . A simple header at the beginning of each article simply stating the date of creation and the referenced version would be exceedingly helpful.

Comments are closed.