Friday, September 29, 2006

BIRT: Passing Parameters from the Command Line for Scheduled Tasks

I received a comment about BIRT report scheduling earlier. Basically the person was having an issue with using a Date parameter in a BIRT report using the command line tools. So here, I am going to replicate the issue the best that I can and make a correction.

First thing I am going to do is create a basic report called ParameterTest. The query I will use for this report is the following:

SELECT
No_emp
FROM
Course_histories
WHERE
Cd_crs = 'CBNAF01'
and cd_ch_status = 'F'
AND dt_ch_compdt > ?

This query will search my course histories table for records where the completion date is above a particular parameter. For the record, the query will be run against an Oracle database, where the dt_ch_compdt field is a date field. I create a report parameter called DATE and bound it to my dataset. Now I save my report. One thing to note is I do not enter a default value for my report parameter so I can easily duplicate the issue. Otherwise, I will get a false sense of accomplishment if my command line parameters are misshapen.

So now that I have my report, I want to try to run it using the same type of format that the requestor did. So I run the following command from my Birt Runtime folder:

C:\birt_runtime\birt-runtime-2_1_0\ReportEngine>genReport.bat -o outp.html -p DATE=20051111 C:\Eclipse\workspace\BirtParameterTest\ParameterTest.rptdesign

C:\birt_runtime\birt-runtime-2_1_0\ReportEngine>ECHO off
Sep 29, 2006 11:46:02 AM org.eclipse.birt.report.engine.api.impl.EngineTask vali
dateScalarParameter
SEVERE: Parameter DATE doesnt allow a null value.
Sep 29, 2006 11:46:02 AM org.eclipse.birt.report.engine.api.ReportRunner runAndR
enderReport
SEVERE: Some required parameter values are not set or set to incompatible data t
ype.
org.eclipse.birt.report.engine.api.EngineException: Some required parameter valu
es are not set or set to incompatible data type.
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRe
nderTask.java:145)
at org.eclipse.birt.report.engine.api.ReportRunner.runAndRenderReport(Re
portRunner.java:237)
at org.eclipse.birt.report.engine.api.ReportRunner.execute(ReportRunner.
java:162)
at org.eclipse.birt.report.engine.api.ReportRunner.main(ReportRunner.jav
a:118)

Hmm, that didn’t work, which is the same error the user reported. Lets try making a small change to the way that the parameter is passed in…

C:\birt_runtime\birt-runtime-2_1_0\ReportEngine>genReport.bat -o outp.html -p "D
ATE=20051111" C:\Eclipse\workspace\BirtParameterTest\ParameterTest.rptdesign

C:\birt_runtime\birt-runtime-2_1_0\ReportEngine>ECHO off
Sep 29, 2006 11:53:02 AM org.eclipse.birt.report.engine.api.ReportRunner evaluat
eParameterValues
SEVERE: the value of parameter DATE is invalid
org.eclipse.birt.core.exception.BirtException: Can not convert the value of 2005
1111 to Date type.
at org.eclipse.birt.core.data.DataTypeUtil.toDate(DataTypeUtil.java:474)

at org.eclipse.birt.core.data.DataTypeUtil.toDate(DataTypeUtil.java:900)

at org.eclipse.birt.core.data.DataTypeUtil.toDate(DataTypeUtil.java:398)

at org.eclipse.birt.report.engine.api.ReportRunner.stringToObject(Report
Runner.java:704)
at org.eclipse.birt.report.engine.api.ReportRunner.evaluateParameterValu
es(ReportRunner.java:669)
at org.eclipse.birt.report.engine.api.ReportRunner.runAndRenderReport(Re
portRunner.java:191)
at org.eclipse.birt.report.engine.api.ReportRunner.execute(ReportRunner.
java:162)
at org.eclipse.birt.report.engine.api.ReportRunner.main(ReportRunner.jav
a:118)
Sep 29, 2006 11:53:02 AM org.eclipse.birt.report.engine.api.impl.EngineTask vali
dateScalarParameter
SEVERE: Parameter DATE doesnt allow a null value.
Sep 29, 2006 11:53:02 AM org.eclipse.birt.report.engine.api.ReportRunner runAndR
enderReport
SEVERE: Some required parameter values are not set or set to incompatible data t
ype.
org.eclipse.birt.report.engine.api.EngineException: Some required parameter valu
es are not set or set to incompatible data type.
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRe
nderTask.java:145)
at org.eclipse.birt.report.engine.api.ReportRunner.runAndRenderReport(Re
portRunner.java:237)
at org.eclipse.birt.report.engine.api.ReportRunner.execute(ReportRunner.
java:162)
at org.eclipse.birt.report.engine.api.ReportRunner.main(ReportRunner.jav
a:118)

Ha, we are getting closer, the error now indicates it accepted DATE, but the format is incorrect. BIRT accepts dates in a peculiar format, and by peculiar, I mean not in the standard Oracle date format that I am used to. While I have made mention of this in presentations on BIRT, I don’t believe I have ever written about it on here. So in order to pass in the date of 01-JAN-2006 in Oracle, in BIRT it needs to be formatted as “01/01/2006”. So lets make that change to the genReport call and see what happens…

C:\birt_runtime\birt-runtime-2_1_0\ReportEngine>genReport.bat -o outp.html -p "DATE=01/01/2005" C:\Eclipse\workspace\BirtParameterTest\ParameterTest.rptdesign

C:\birt_runtime\birt-runtime-2_1_0\ReportEngine>ECHO off
C:\birt_runtime\birt-runtime-2_1_0\ReportEngine>dir outp.html
Volume in drive C is C21A216
Volume Serial Number is 8CAE-547F

Directory of C:\birt_runtime\birt-runtime-2_1_0\ReportEngine

09/29/2006 11:56 AM 906,406 outp.html
1 File(s) 906,406 bytes
0 Dir(s) 32,806,092,800 bytes free

And sure enough, that corrected the problem. So the solution is to enclose your parameters in double-quotes, and to format any dates accordingly.

6 comments:

Charles Stahl said...

This issue is even more frustrating when trying to accept a user date as an interactive parameter with an Oracle backend. Here's a simple, though kludgy, solution:

For the parameter, set the input type to string rather than DateTime.

In your query, use a condition similar to this:
...to_char(a.ent_date,'MM/DD/YYYY') = ?1

You must match the expected format in the to_char argument to the expected user input.

Good luck, and somebody please fix this! :)

damodaram said...

I want to get the pdf report format, I changed the format as below



Set BIRT_HOME=C:\birt_runtime\birt-runtime-2_1_1\
C:\birt_runtime\birt-runtime-2_1_1\ReportEngine\genReport.bat -runrender -output "C:\birt_runtime\birt-runtime-2_1_1\ReportEngine\samples\output.pdf" -format pdf "C:\birt_runtime\birt-runtime-2_1_1\ReportEngine\samples\hello_world.rptdesign"

and I got the itext.jar also, till I am getting html report only, can you please help me out on this...

thanks in advance...
damodar

Praveena said...

Ive created reports using the BIRT Api in java. It works fine when i generate reports by running the code in the Eclipse environment. But when i run it from my compiled source without the Eclipse IDE it initializes the SamplePluginDB but doesnt generate the report. The task.run() call exits and doesnt give any error.

Heres the code that ive used
// Get the complete file name
String strRptDesign = url.getFile() ;

IReportEngine engine = null;
String strOutputFileName = "";
try
{
EngineConfig config = new EngineConfig();

String birtHome;

birtHome = System.getenv( "BIRT_HOME" );

if ( birtHome == null )
throw new BillException("BIRT_HOME not set in system properties") ;

config.setEngineHome( birtHome );
config.setLogConfig( FileHelper.sparkTempPath, Level.FINE );


// config.setEngineHome( "D:/utilities/birt-runtime-2_2_0/ReportEngine" );
// config.setLogConfig( "c:/temp", Level.FINE );

Platform.startup( config );

IReportEngineFactory factory = (IReportEngineFactory)Platform.createFactoryObject( IReportEngineFactory.EXTENSION_REPORT_ENGINE_FACTORY );
engine = factory.createReportEngine( config );
engine.changeLogLevel( Level.WARNING );

// Open a report design
IReportRunnable design;

// Get the design file name from the complete path
String strDesignFileName = strRptDesign.substring( strRptDesign.lastIndexOf( "/" ), strRptDesign.length() );

design = engine.openReportDesign( strRptDesign );

// Create task to run the report - use the task to execute the
// report and save to disk.
IRunAndRenderTask task = engine.createRunAndRenderTask( design );

HTMLRenderContext renderContext = new HTMLRenderContext();
renderContext.setImageDirectory( "image" );
HashMap contextMap = new HashMap();
contextMap.put( EngineConstants.APPCONTEXT_HTML_RENDER_CONTEXT, renderContext );
task.setAppContext( contextMap );

HTMLRenderOption options = new HTMLRenderOption();

strOutputFileName = FileHelper.sparkTempPathFile + "/" + strDesignFileName + ".pdf";

options.setOutputFileName( strOutputFileName );
options.setOutputFormat( "pdf" );
task.setRenderOption( options );

task.run();

task.close();

return strOutputFileName;
Im guessing that it is not able to find the batch file genReport.bat.

Can you please help me on this..

thanks in advance!
Praveena

Anonymous said...

Hi,

I have the exact same problem as damodaram above. I want a pdf file output instead of html. I changed my .bat file to the one that damodaram posted. Yet, it keeps pumping out the generated output in html instead of pdf. What do we need to do to fix this? Need urgent answer. Thanks.

sandy said...

hi

I want to get output report in pdf form as specified name concatenate with current date.
but it gives the same name as rptdesign and output format is html
also give cannot load database error
while i paste the mysql-connector-java-3.1.13-bin.jar in plugins\org.eclipse.birt.report.data.oda.jdbc_2.3.2.r232_v20090212

plz help me to sort out these problems asap

regards
sandy

Anonymous said...

Hi,

I am trying to run the report using parameters, but the report always takes the default value rather than the parameter value passed?

The same report seems to be working fine when run from eclipse. But using BIRT runtime it takes only the default value

Can you please help?