Modifying dataset query on-the-fly with java api

jtesdall

<p>I have BIRT working to the extent of calling the engine from JAVA and writing reports out to PDF or displaying in the viewer. We have the requirement of needing to add a WHERE clause to the query dynamically from our application. This WHERE clause will vary dramatically each time it is run by table and column so I am thinking parameters are not going to work unless I can set one parameter and set it to the complete WHERE clause. Ideally we would like to change the query in any way we want while maintaining the output columns. </p>
<p> </p>
<p>I have been fighting how to pull out the dataset queries, modify them and run the report using this modified query. We need to do this all within JAVA with the API's. BIRT has been easy to this point but now I am lost. Can someone point me in the right direction?</p>

jtesdall

<p>OK, I finally figured it out. For anyone else that wants to dynamically change the datasource connection properties or the dataset query from JAVA with the API's here is some code:</p>
<p> </p>
<div>design = engine.openReportDesign("C:\\BIRT\\birt-rcp-report-designer-4_4_1\\workspace\\Test1.rptdesign"); </div>
<div> </div>
<div>ReportDesignHandle designHandle = (ReportDesignHandle) design.getDesignHandle( );</div>
<div>ElementFactory designFactory = designHandle.getElementFactory( );</div>
<div> </div>
<div>// change data source properties</div>
<div>changeDataSource(designFactory, designHandle);</div>
<div> </div>
<div>// modify data set/query </div>
<div>changeDataSet(designFactory, designHandle);</div>
<div> </div>
<div> </div>
<div>
<div>/*</div>
<div>* Change the data set/query in the .rptdesign</div>
<div>*/</div>
<div>static void changeDataSet(ElementFactory designFactory, ReportDesignHandle designHandle) throws SemanticException</div>
<div>{</div>
<div> </div>
<div>   SlotHandle datasets = designHandle.getDataSets();</div>
<div>   SlotIterator iter = (SlotIterator) datasets.iterator();</div>
<div>   while (iter.hasNext()) {</div>
<div>   <span> </span>DesignElementHandle dsHandle = (DesignElementHandle) iter.next();</div>
<div>       if (dsHandle instanceof OdaDataSetHandle && dsHandle.getName().equals("MyDataSet")) {</div>
<div>       <span> </span>OdaDataSetHandle datasetHandle = (OdaDataSetHandle) dsHandle;</div>
<div>       <span> </span>System.out.println("Changing DataSet/Query: " + dsHandle.getName());</div>
<div>            <span> </span>System.out.println("Query Text: " + datasetHandle.getQueryText());</div>
<div>            <span> </span>// add WHERE Clause</div>
<div>            <span> </span>datasetHandle.setQueryText(datasetHandle.getQueryText() + " WHERE ID = 'xxxxx'");</div>
<div>       } else {</div>
<div>       <span> </span>System.out.println("dataSet Not Used: " + dsHandle.getName());</div>
<div>       }</div>
<div>   }</div>
<div>}</div>
<div> </div>
<div> </div>
<div>/*</div>
<div> * Change the data sources in the .rptdesign</div>
<div> */</div>
<div>static void changeDataSource(ElementFactory designFactory, ReportDesignHandle designHandle)</div>
<div>{</div>
<div> </div>
<div>    SlotHandle datasources = designHandle.getDataSources();</div>
<div>    SlotIterator iter = (SlotIterator) datasources.iterator();</div>
<div>    while (iter.hasNext()) {</div>
<div>        DesignElementHandle dsHandle = (DesignElementHandle) iter.next();</div>
<div>        if (dsHandle instanceof OdaDataSourceHandle && dsHandle.getName().equals("MyDataSource")) {</div>
<div>        <span> </span>System.out.println("Changing datasource ");</div>
<div>            try {</div>
<div>dsHandle.setProperty("odaDriverClass", "com.microsoft.sqlserver.jdbc.SQLServerDriver");</div>
<div>           dsHandle.setProperty("odaURL", "jdbc:sqlserver://MYSERVER\\SQLEXPRESS:1433;databasename=MyDB");</div>
<div>           dsHandle.setProperty("odaUser", "sa");</div>
<div>           dsHandle.setProperty("odaPassword", "mypassword");</div>
<div>} catch (SemanticException e) {</div>
<div>// TODO Auto-generated catch block</div>
<div>e.printStackTrace();</div>
<div>}</div>
<div>        } else {</div>
<div>        <span> </span>System.out.println("dataSource Not Used: " + dsHandle.getName());</div>
<div>        }</div>
<div>    }</div>
<div>}</div>
</div>
<p> </p>