Data types and parameter passing from Report Designer 3.6 to the 3.5.2 platform
Introduction
Most of the time, using and passing parameters is likely a straightforward task when using Pentaho Report Designer 3.6 and BI Platform 3.5.2. This article will first focus on some minor areas where users will likely stumble. Specifically, the handling of dates and also the mechanics of how Report Designer 3.6 handles parameter passing to subreports will be covered.
Know your data types
Summary of the time and date data types supported by Report Designer 3.6.
Data Type |
Description and purpose |
|---|---|
Date |
Includes both the date ie: 2010-03-21 part as well as the time 10:53:30.000 part. If you use this in your WHERE clause it may be treated like a timestamp and match on nothing if you were trying to compare dates. |
Date (SQL) |
Includes only the date part. If you use this data type in a WHERE clause it will correctly match on the date part as opposed a timestamp as described above. |
Time |
Includes only the time portion of a date object. When used in a WHERE, only the time portion should be output. |
Timestamp |
May be a localized date object with date part and time part. |
In the post on 3-19-2010, Taqua wrote:
Certain JDBC-Drivers (MySQL, for example) tend to treat ordinary Date objects as timestamps, and then when you try to use them in a WHERE clause, they match nothing. A SQL-Date only matches the Date-part, a SQL-Time only the time-part and so on.
And given the fact that there is no way for us to actually "guess" the correct Date/Time type, we simply pass the problem on the user and let *you* decide what value-type you need.
In Will Gorman's book Pentaho Reporting 3.5 for Java Developers on Page 200: He says that the Date, Time and DateTime types all inherit from the numeric data type java.util.Date. Though names differ a little from the drop down list of types when creating parameters, these date and time types likely share a common ancestry of java.util.Date.
Special notes about xactions and Design Studio 3.5
Installation:
With the exception of the Mac OS X package of Design Studio 3.5 (Eclipse with the xaction designer plugin), all other packages use a 32bit version of Eclipse and Java.
If you run a 64bit flavor of Linux, Ubuntu 9.10 64bit for instance, you'll have to install the plugin manually. Ubuntu 9.10 proved to be inconsistent about the way plugins are installed. On some machines, when you unpack the actionsequence plugin zip file to /home/whateveruser/.eclipse/core-something-package/plugins directory, Eclipse may not find the plugin. If it does not find the plugin, you can unpack the plugin to /usr/lib/eclipse/plugins and Eclipse will be able to find it.
Passing dates:
Design studio 3.5 does not have any date or time data types that can be used as inputs or pushed out to a report. To pass dates you should pass a string data type to the report and cast / or formula modify it into the data type you actually need.
Revision 3-25-2010: Below you will find an example attached where a properly formatted string is passed to a PRPT file. As it turns out, the date_init date in the PRPT file magically casts the passed in string to a date and allows the report to march forward and use the date in SQL queries etc.
Gotchas to avoid (Click on thumbnail images to see a larger image)
Beware: Report Designer will try to initialize parameters immediately unless you prevent it
Report Designer tries to go right to work on initializing your parameters unless you check the box "Mandatory" when setting up a parameter. If you have a subreport that depends on this parameter and it exists in your master report, you will likely receive and error message saying that the parameter cannot be null. This can be a very tricky error to troubleshoot because in preview mode, you will select a valid parameter and your report will work.
The Outer Name and Inner Name must match (Case Sensitive)
The import parameter GUI can mislead you into believing that you can name the parameter being imported anything you want 
because it lets you type in anything you want. This is definitely not the case. Make sure that when you are importing a parameter, its outer and inner name and case match. This is extremely hard to troubleshoot because this error will not generate warnings or errors of any kind.
Be careful deleting a parameter, it will change the name of the Outer Name of the parameter below it.
This is more attention to detail that anything else. If you are in a hurry and fail to notice this, your dependent queries will fail and you might not notice right away what went wrong. There is no checking of any kind on the parameter box to see if inner and outer names match up.
Parameters names in xactions must match names of parameters in PRPT files.
If you have a parameter in your report called 'date_init', you'll need to have a matching parameter also named date_init in your xaction. It is not enough to match the data type and position/order of the parameter being passed in (ie, passed first, second or third.) Fortunately this error will slap you right away if you do it. Just go into xaction writing knowing this is a rule you have to follow. It is probably good practice as well.
A working example of passing a date date type from an xaction to a PRPT file
The solution turns out to be easier than I expected. In the attached xaction a JavaScript step is used to initialize a variable called date_init of type string to yesterday's date. date_init is then passed via SimpleReportingComponent step to Sample2.prpt.
Sample2.prpt has a parameter called date_init of type Date, with a format of yyyy-MM-dd, which matches the format of the string passed in by the xaction. The string is magically cast to the correct Date type, and then date_init is used in a SQL query. In my case the SQL backend is MySQL reached through the JDBC driver.