Home Analytics Designing reports
The community of Analytics developers are invited to follow and participate in the developer network.
OpenText technical experts collaborate with supported customers and partners in the Magellan product area.

Dynamic Table based off dynamic data set

Hey,


 


We are using OS BIRT 4.2.2.


 


I'm trying to make a report where we display the full data set result in a table. The data set SQL will be different depending on a report parameter.


 


Currently I have the following.


 


Data Set - Before Open Script


this.queryText = 'SELECT * FROM ' + params["rpTable"].value


 


Data Set - After Open Script


importClass(org.eclipse.birt.report.engine.script.internal.instance.DataSetInstance);

//Do not execute when dataset is previewed

if( this instanceof DataSetInstance ){


cmd = this.getColumnMetaData( );

colCount = cmd.getColumnCount( );


 


if (params["rpCounter"].value != colCount)

{

params["rpCounter"].value = colCount


for ( i = 1; i <= colCount; i++ )

{


if (i < colCount) {

params["rpColumn"].value = params["rpColumn"].value + cmd.getColumnName(i) + ',';

} else if (i == colCount){

params["rpColumn"].value = params["rpColumn"].value + cmd.getColumnName(i);

}

}

}


}


 


Table - On Prepare Script


var columnList = params["rpColumn"].value. split(",");


 numColumns = params["rpCounter"].value;

 i=1;

 importPackage( Packages.org.eclipse.birt.report.model.api );

 elementFactory = reportContext.getDesignHandle().getElementFactory()

 var mytable = reportContext.getDesignHandle().findElement("test");

 var colbinds = mytable.getColumnBindings();

 var cs1 = []; StructureFactory.createComputedColumn();

 

 

 while (i <= numColumns){

 cs1 = StructureFactory.createComputedColumn( );

 cs1.setName(columnList[i-1]);

 cs1.setExpression( "dataSetRow[\"" + columnList[i-1] + "\"]" );

 colbinds.addItem( cs1 );

 

 //second parameter is before(-1) or after(1)

 mytable.insertColumn(i+1,-1);

 //get first detail row

 mydetail = mytable.getDetail( ).get( 0 );

 //get first column and add detail data item

 var tcell = mydetail.getCells( ).get( i );

 var mydata = elementFactory.newDataItem( null );

 mydata.setResultSetColumn( columnList[i-1]);

 tcell.getContent( ).add( mydata );

 //get header and add label

 var myheader = mytable.getHeader( ).get( 0 );

 tcell = myheader.getCells( ).get( i );

 var mylabel = elementFactory.newLabel( null );

 mylabel.setText( columnList[i-1] );//$NON-NLS-1$

 tcell.getContent( ).add( mylabel );

 i++;

 }

 


 


This will work if I do a drill down to the same report keeping the parameters. (Basically a refresh of the page)


 


The reason that I think this works with the refresh is because the table script does not have the data set data (param rpColumn) so it doesn't populate the table.


 


So is there a better way to do this, or maybe a way to get the table script to get the data set data without a refresh, or a way to make the report refresh with parameters after it loads the first time?


 


Thanks for any Help!


Comments

  • Perhaps the example here might be helpful:  http://developer.actuate.com/community/forum/index.php?/topic/24254-creating-column-dynamically/?p=132987


     


    The example dynamically creates a table based on the query.


    The number of columns are also dynamic based on which table you select from the parameter screen.


    Hopefully this is similar to what you are trying to accomplish.


     


    If not, could you provide a sample of what you are trying to accomplish?


    Matthew L.
    Developer
    OpenText
  • Matthew,


     


    That looks great!


     


    Although I am having some issues changing that to work in my environment when using our data source from the library. I have it working when I did a hard code of the data source.


     


     


    This is the part that I don't want to hard code


        var odaDataSource = elementFactory.newOdaDataSource("DataSource", "org.eclipse.birt.report.data.oda.jdbc" );  //OdaDataSourceHandle

            odaDataSource.setProperty( "odaDriverClass", "org.eclipse.birt.report.data.oda.sampledb.Driver" );  //Add odaDriverClass public connection property

            odaDataSource.setProperty( "odaURL", "jdbc:classicmodels:sampledb" );  //Add odaURL public connection property

            odaDataSource.setProperty( "odaUser", "" );  //Add odaUser public connection property

            odaDataSource.setProperty( "odaPassword", "" );  //Add odaPassword public connection property

        reportDesignHandle.getDataSources( ).add( odaDataSource );  //SlotHandle - Add report item to the slot with the given element handle

     


    I've tried to use the idea from http://stackoverflow.com/questions/27967471/birt-access-content-of-dataset-from-beforefactory


    To make it use the data source properties from the library file.


     


    Any idea on how to make it use the data source from the library?


     


    Thanks for the help!


  • Here is a code snippet that finds an rptlibrary, locates a referenced data source from within the rptlibrary, collects the driver class, url, user name, and password information to be used in a dynamically generated ODA Data Source.


     


    I've attached an example zip that includes both an rptdesign and rptlibrary that uses this method for reference.



    importPackage(Packages.org.eclipse.birt.data.engine.api.querydefn);

    var libraryHandle = reportContext.getDesignHandle().findLibrary("(Topic_37058)OS_BIRT_2.6.2_RunDataSetFromWithinLibrary.rptlibrary"); //Find rptlibrary file
    if(libraryHandle){ //If libraryHandle exists
    var dataSourceHandle = libraryHandle.findDataSource("Classic Models Data Source"); //Find Data Source
    if(dataSourceHandle){ //If dataSourceHandle exists
    var dataSourceDriverClass = dataSourceHandle.getProperty("odaDriverClass").toString(); //Get odaDriverClass public connection property
    if(dataSourceDriverClass){dataSourceDriverClass = dataSourceDriverClass.toString();} //Get value if not null
    var dataSourceURL = dataSourceHandle.getProperty("odaURL").toString(); //Get odaURL public connection property
    if(dataSourceURL){dataSourceURL = dataSourceURL.toString();} //Get value if not null
    var dataSourceUser = dataSourceHandle.getProperty("odaUser").toString(); //Get odaUser public connection property
    if(dataSourceUser){dataSourceUser = dataSourceUser.toString();} //Get value if not null
    var dataSourcePassword = dataSourceHandle.getProperty("odaPassword"); //Get odaPassword public connection property
    if(dataSourcePassword){dataSourcePassword = dataSourcePassword.toString();} //Get value if not null

    //New OdaDataSource based on properties from OdaDataSource in a rptlibrary
    var odaDataSource = new OdaDataSourceDesign( "MyNewDataSource" ); //New OdaDataSourceDesign
    odaDataSource.setExtensionID( "org.eclipse.birt.report.data.oda.jdbc" ); //Set the data source extension id as defined by ODA driver
    odaDataSource.addPublicProperty( "odaDriverClass", dataSourceDriverClass); //Add odaDriverClass public connection property
    odaDataSource.addPublicProperty( "odaURL", dataSourceURL); //Add odaURL public connection property
    odaDataSource.addPublicProperty( "odaUser", dataSourceUser); //Add odaUser public connection property
    odaDataSource.addPublicProperty( "odaPassword", dataSourcePassword ); //Add odaPassword public connection property
    }
    }

    Matthew L.
    Developer
    OpenText
  • This is great, thanks for the help.

  • Glad I could help.


    If other questions come up, just let me know.


    Matthew L.
    Developer
    OpenText
  • So now I need to get this to work with a JNDI dataSource. Does the below look like it should work. Setting the odaJndiName instead of the odaDriverClass/odaURL/odaUser/odaPassword.


     


    if(libraryHandle){  //If libraryHandle exists

        var dsrc = libraryHandle.findDataSource("JNDI_DataSource");  //Find Data Source

        if(dsrc){  //If dataSourceHandle exists

        

        var odaDataSource = elementFactory.newOdaDataSource("DataSource", "org.eclipse.birt.report.data.oda.jdbc" );  //OdaDataSourceHandle

            odaDataSource.setProperty( "odaJndiName", dsrc.getProperty("jndiName") );  //Add odaJndiName public connection property

        reportDesignHandle.getDataSources( ).add( odaDataSource );  //SlotHandle - Add report item to the slot with the given element handle

        }


    }


  • @Matthew L. said:
    Perhaps the example here might be helpful:  http://developer.actuate.com/community/forum/index.php?/topic/24254-creating-column-dynamically/?p=132987

     

    The example dynamically creates a table based on the query.

    The number of columns are also dynamic based on which table you select from the parameter screen.

    Hopefully this is similar to what you are trying to accomplish.

     

    If not, could you provide a sample of what you are trying to accomplish?

    Do you happen to have the rptdesign file. I am not able to see the attachment in the forum.

Sign In or Register to comment.