Dynamic Table based off dynamic data set

hallj05
edited February 11, 2022 in Analytics #1
<p>Hey,</p>
<p> </p>
<p>We are using OS BIRT 4.2.2.</p>
<p> </p>
<p>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.</p>
<p> </p>
<p>Currently I have the following.</p>
<p> </p>
<p><strong>Data Set - Before Open Script</strong></p>
<p>this.queryText = 'SELECT * FROM ' + params["rpTable"].value</p>
<p> </p>
<p><strong>Data Set - After Open Script</strong></p>
<p>importClass(org.eclipse.birt.report.engine.script.internal.instance.DataSetInstance);<br>
//Do not execute when dataset is previewed<br>
if( this instanceof DataSetInstance ){<br><br>
cmd = this.getColumnMetaData( );<br>
colCount = cmd.getColumnCount( );</p>
<p> </p>
<p>if (params["rpCounter"].value != colCount)<br>
{<br>
params["rpCounter"].value = colCount<br><br>
for ( i = 1; i <= colCount; i++ )<br>
{<br><br>
if (i < colCount) {<br>
params["rpColumn"].value = params["rpColumn"].value + cmd.getColumnName(i) + ',';<br>
} else if (i == colCount){<br>
params["rpColumn"].value = params["rpColumn"].value + cmd.getColumnName(i);<br>
}<br>
}<br>
}</p>
<p>}</p>
<p> </p>
<p><strong>Table - On Prepare Script</strong><br><br>
var columnList = params["rpColumn"].value. split(",");<br><br>
 numColumns = params["rpCounter"].value;<br>
 i=1;<br>
 importPackage( Packages.org.eclipse.birt.report.model.api );<br>
 elementFactory = reportContext.getDesignHandle().getElementFactory()<br>
 var mytable = reportContext.getDesignHandle().findElement("test");<br>
 var colbinds = mytable.getColumnBindings();<br>
 var cs1 = []; StructureFactory.createComputedColumn();<br>
 <br>
 <br>
 while (i <= numColumns){<br>
 cs1 = StructureFactory.createComputedColumn( );<br>
 cs1.setName(columnList[i-1]);<br>
 cs1.setExpression( "dataSetRow[\"" + columnList[i-1] + "\"]" );<br>
 colbinds.addItem( cs1 );<br>
 <br>
 //second parameter is before(-1) or after(1)<br>
 mytable.insertColumn(i+1,-1);<br>
 //get first detail row<br>
 mydetail = mytable.getDetail( ).get( 0 );<br>
 //get first column and add detail data item<br>
 var tcell = mydetail.getCells( ).get( i );<br>
 var mydata = elementFactory.newDataItem( null );<br>
 mydata.setResultSetColumn( columnList[i-1]);<br>
 tcell.getContent( ).add( mydata );<br>
 //get header and add label<br>
 var myheader = mytable.getHeader( ).get( 0 );<br>
 tcell = myheader.getCells( ).get( i );<br>
 var mylabel = elementFactory.newLabel( null );<br>
 mylabel.setText( columnList[i-1] );//$NON-NLS-1$<br>
 tcell.getContent( ).add( mylabel );<br>
 i++;<br>
 }<br>
 </p>
<p> </p>
<p>This will work if I do a drill down to the same report keeping the parameters. (Basically a refresh of the page)</p>
<p> </p>
<p>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.</p>
<p> </p>
<p>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?</p>
<p> </p>
<p>Thanks for any Help!</p>

Comments

  • <p>Perhaps the example here might be helpful:  <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/24254-creating-column-dynamically/?p=132987'>http://developer.actuate.com/community/forum/index.php?/topic/24254-creating-column-dynamically/?p=132987</a></p&gt;
    <p> </p>
    <p>The example dynamically creates a table based on the query.</p>
    <p>The number of columns are also dynamic based on which table you select from the parameter screen.</p>
    <p>Hopefully this is similar to what you are trying to accomplish.</p>
    <p> </p>
    <p>If not, could you provide a sample of what you are trying to accomplish?</p>
    Warning No formatter is installed for the format ipb
  • <p>Matthew,</p>
    <p> </p>
    <p>That looks great!</p>
    <p> </p>
    <p>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.</p>
    <p> </p>
    <p> </p>
    <p>This is the part that I don't want to hard code</p>
    <p><span style="font-size:10px;">    var odaDataSource = elementFactory.newOdaDataSource("DataSource", "org.eclipse.birt.report.data.oda.jdbc" );  //OdaDataSourceHandle<br>
            odaDataSource.setProperty( "odaDriverClass", "org.eclipse.birt.report.data.oda.sampledb.Driver" );  //Add odaDriverClass public connection property<br>
            odaDataSource.setProperty( "odaURL", "jdbc:classicmodels:sampledb" );  //Add odaURL public connection property<br>
            odaDataSource.setProperty( "odaUser", "" );  //Add odaUser public connection property<br>
            odaDataSource.setProperty( "odaPassword", "" );  //Add odaPassword public connection property<br>
        reportDesignHandle.getDataSources( ).add( odaDataSource );  //SlotHandle - Add report item to the slot with the given element handle</span><br>
     </p>
    <p>I've tried to use the idea from <a data-ipb='nomediaparse' href='http://stackoverflow.com/questions/27967471/birt-access-content-of-dataset-from-beforefactory'>http://stackoverflow.com/questions/27967471/birt-access-content-of-dataset-from-beforefactory</a></p&gt;
    <p>To make it use the data source properties from the library file.</p>
    <p> </p>
    <p>Any idea on how to make it use the data source from the library?</p>
    <p> </p>
    <p>Thanks for the help!</p>
  • <p>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.</p>
    <p> </p>
    <p>I've attached an example zip that includes both an rptdesign and rptlibrary that uses this method for reference.</p>
    <pre class="_prettyXprint _lang-js">
    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
    }
    }
    </pre>
    Warning No formatter is installed for the format ipb
  • <p>This is great, thanks for the help.</p>
  • <p>Glad I could help.</p>
    <p>If other questions come up, just let me know.</p>
    Warning No formatter is installed for the format ipb
  • <p>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.</p>
    <p> </p>
    <p>if(libraryHandle){  //If libraryHandle exists<br>
        var dsrc = libraryHandle.findDataSource("JNDI_DataSource");  //Find Data Source<br>
        if(dsrc){  //If dataSourceHandle exists<br>
        <br>
        var odaDataSource = elementFactory.newOdaDataSource("DataSource", "org.eclipse.birt.report.data.oda.jdbc" );  //OdaDataSourceHandle<br>
            odaDataSource.setProperty( "odaJndiName", dsrc.getProperty("jndiName") );  //Add odaJndiName public connection property<br>
        reportDesignHandle.getDataSources( ).add( odaDataSource );  //SlotHandle - Add report item to the slot with the given element handle<br>
        }<br><br>
    }</p>
  • @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.