set Parameter default using SQL

Is there a way I can I set my parameter default using SQL ??

I have 2 input date parameters (Start_Date and End_Date) that are binded to a Dataset. The dataset is then displayed on my report.

I would like to set the default date to a date selected from a table such as:

"Select Max(PERIOD_END_DATE) from PERIOD_DATES where PERIOD_END_DATE < sysdate"

These parameters have a date picker so the user can also select a different date.

I am running BIRT 3.7.0

Find more posts tagged with

Comments

HSMorales

johnw

You can, but its not easy. Since parameter display comes into play before report execution, the only way I can think of is a complex expression that would use a direct JDBC to set it.

Tubal

I had to do something like this for one of my reports where I auto generated some tables based on a dataset.

Here's a dirty test I did on the classicmodels database. I think I copied the code from one of Jason W.'s or John's posts somewhere, but I don't recall where I got it now.

It connects to a jdbc source outside of the dataset like John was saying. Look at the default value script in the report parameter.

Thanks.

HSMorales

I can't seem to make this work. I possibly have a syntax error but I'm not sure where.

This is the script I am using:

importPackage( Packages.org.eclipse.birt.report.model.api );
importPackage(Packages.java.lang);
importPackage(Packages.java.util);
importPackage(Packages.org.eclipse.birt.data.engine.api);
importPackage(Packages.org.eclipse.birt.report.model.api);
importPackage(Packages.org.eclipse.birt.data.engine.api.querydefn);
importPackage(Packages.org.eclipse.birt.data.engine.core);
importPackage(com.actuate.reportcast.exceptions.AuthenticationException);
var myconfig = reportContext.getReportRunnable().getReportEngine().getConfig();
var de = DataEngine.newDataEngine( myconfig, null );
var dsrc = reportContext.getDesignHandle().findDataSource("FDS_RM"); //we use this so we can just copy all the settings
var odaDataSource = new OdaDataSourceDesign( "TempDatSource" ); //this is our new data source
odaDataSource.setExtensionID( "oracle.jdbc.OracleDriver.jdbc" );
odaDataSource.addPublicProperty( "odaURL", dsrc.getProperty("odaURL").toString() ); //copying URL from Data Source
odaDataSource.addPublicProperty( "odaDriverClass", dsrc.getProperty("odaDriverClass").toString()); //copying driver from Data Source
odaDataSource.addPublicProperty( "odaUser", dsrc.getProperty("odaUser").toString() ); //copying user from Data Source
odaDataSource.addPublicProperty( "odaPassword", ""); //no password
var odaDataSet = new OdaDataSetDesign( "Tmp Data Set" ); //making our new Data Set
odaDataSet.setDataSource( odaDataSource.getName( ) ); //setting our Data Source
odaDataSet.setExtensionID( "oracle.jdbc.OracleDriver.jdbc.JdbcSelectDataSet" );
var strSQL = "Select Max(SUB_PD_END) from FDS_RM.RM_PROCESS_DATES where SUB_PD_END < sysdate"; //this is the query to our new data set
odaDataSet.setQueryText( strSQL );
de.defineDataSource( odaDataSource );
de.defineDataSet( odaDataSet );
queryDefinition = new QueryDefinition( );
queryDefinition.setDataSetName( odaDataSet.getName() );
queryDefinition.setAutoBinding(true);
var pq = de.prepare( queryDefinition );
var qr = pq.execute( null );
var ri = qr.getResultIterator( );
var rsmd = qr.getResultMetaData( );
var defaultvalue = ri.getValue(rsmd.getColumnName(1));
ri.close( );
qr.close( );
de.shutdown( );
defaultvalue;

My Datasource name is FDS_RM
The Datasourse Driver Class it uses is : oracle.jdbc.OracleDriver (v10.2)

If I use this script for the default on a variable I get the msg:

ReportDesign (id = 1):
+ There are errors evaluating script "importPackage( Packages.org.eclipse.birt.report.model.api );

----Script----

Wrapped org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot find or process the oracle.jdbc.OracleDriver driver's data source extension configuration.
Cannot find the ODA dataSource extension (oracle.jdbc.OracleDriver).
Check the workspace log file for any problems with loading the extension bundle and its dependencies. (<inline>#29) (Element ID:1)