Access Birt 2.5.1 DataSet from Java Code

I have a web application to generate Birt 2.5.1 charts and it is working fine. Now I have to access the dataset of the chart and display it in an excel file. The latter part of displaying in an excel file I can do using Apache POI Excel Library.

However I am unable to access the dataset through Javacode.

My working source code to generate the report is-

IReportEngine birtReportEngine = BirtEngine.getBirtEngine();
IReportRunnable design = null;
String reportPath==getContext().getServletContext().getRealPath("/reports")+ "/reportchart.rptdesign";
design = birtReportEngine.openReportDesign(reportPath);

IRunAndRenderTask task = birtReportEngine.createRunAndRenderTask(design);

IRenderOption options = new RenderOption();
options.setOutputFormat(outputFormat);
options.setOutputStream(reportOutput);
Map appContext = task.getAppContext();
appContext.put(EngineConstants.APPCONTEXT_CLASSLOADER_KEY,
GenerateReportActionBean.class.getClassLoader());
task.setAppContext(appContext);
task.setRenderOption(options);
task.run();
task.close();

I can access the report handle and the chart object by

ExtendedItemHandle eih = (ExtendedItemHandle) design.getDesignHandle().getDesignHandle().findElement("NewChart");
ChartWithAxes barChart = (ChartWithAxes) eih.getReportItem().getProperty(
"chart.instance");

Now I want to access the dataset.

Kindly help.

Thanks in advance,

Kiran Vaidya

Find more posts tagged with

Comments

cypherdj

hi,

if your report design also contained the data set as a table, you could simply generate the excel file by using an excel emitter in your render task.

Otherwise, I'm guessing you could start by using a data extraction task, and pass the data set name as parameter. this will then allow you to iterate through the returned records, which you will then need to somehow inject into an excel document. or you could simply generate a csv file, which can be opened in excel as well.

hope that helps,
cedric

kiranv

I managed to find out the solution. As I told you my requirement was to display the dataset in excel. 
 
I can now access the dataset by- 
 
EList eList1 = barChart.getSampleData().getBaseSampleData(); 
EList eList2 = barChart.getSampleData().getOrthogonalSampleData(); 
 
BaseSampleData obj1[] = (BaseSampleData[]) eList1.toArray(); 
OrthogonalSampleData obj2[] = (OrthogonalSampleData[]) eList2.toArray(); 
String str1 = obj1[0].getDataSetRepresentation(); 
String str2 = obj2[0].getDataSetRepresentation(); 
So now I have the orthogonal and base sample data as a comma separated string. Using Apache POI lib i can generate the data in excel. 
 
What do you exactly mean by "generate the excel file by using an excel emitter in your render task" 
 
If you have the source code please post it. 
 
regards, 
Kiran

kiranv

what i was retrieving was the sample data and not the runtime dataset

[Even the method's name is getSampleData() ]

anyways i am no where near the solution.. plz plz help me..

post the source code to generate the excel file by using an excel emitter in render task.

plz

thanks in advance
-kiran

cypherdj

Basically, what you can do is to: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
ReportDesignHandle rdh = (ReportDesignHandle) reportDesign.getDesignHandle();

ExtendedItemHandle eih = (ExtendedItemHandle) rdh.findElement(chartName);
</pre>
 
this will retrieve the extended item which contains your chart. 
 
Then you get the chart.instance and dataSet properties for this extended item like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (eih!=null){
Chart theChart = (Chart) eih.getReportItem().getProperty(ChartReportItemConstants.PROPERTY_CHART);
String dataSet = (String)eih.getProperty("dataSet");
</pre>
 
You will need to run the report to get the report document (set reportDocumentPath to where you want this temp file generated): 
<pre class='_prettyXprint _lang-auto _linenums:0'>
IRunTask rTask = reportEngine.createRunTask(reportDesign);
rTask.run(reportDocumentPath);
rTask.close();
IReportDocument reportDocument = reportEngine.openReportDocument(reportDocumentPath);
</pre>
 
With the data set name, you can then run a data extraction task to retrieve your data set at runtime: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
IDataExtractionTask deTask = reportEngine.createDataExtractionTask(reportDocument);

//tell the data extraction task to use the first result set
deTask.selectResultSet(dataSet);

IExtractionResults results = deTask.extract();
deTask.close();
</pre>
 
You can then use the IExtractionResults to iterate through your data set: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (results != null){
// get the iterator for our data set
IDataIterator iData = results.nextResultIterator();

if (iData!=null){
// get nb of column in the data set
int colCount = iData.getResultMetaData().getColumnCount();
// move to the next row
while (iData.next()){
for (int colIdx=0; colIdx<colCount;idx++){
Object curCol = iData.getValue(colIdx);

// output your current column of the current row in dataset
}
}

</pre>
 
I think this should pretty much give you the contents of your data set at runtime, as objects for each cell in your spreadsheet. 
 
In addition, you might want to get the column name and data type, which you can obtain from the result meta data: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
String colName = iterator.getResultMetaData().getColumnName(colIdx);
int dataType = iterator.getResultMetaData().getColumnType(colIdx);
String dataTypeName = iterator.getResultMetaData().getColumnTypeName(colIdx);
</pre>
 
I hope this helps, and best of luck, I'd be interested in some pointer on the POI code, I've not looked into this yet. 
Cedric

cypherdj

Oh, almost forgot to mention, if you simply want to get the columns which are bound to your data set, rather than the whole data set, use this property of the extended item: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
List<ComputedColumn> boundDataColumns = eih.getListProperty("boundDataColumns");
</pre>

kiranv

Thnx a lot. Its working perfectly fine! I found the complete source code @ 
<a class='bbc_url' href='http://wiki.eclipse.org/Data_Extract_(BIRT)_2.1'>Data Extract (BIRT) 2.1 - Eclipsepedia</a> 
 
Now to implement it with Apache POI, first download the libs. then to display the dataset as an excel sheet use the below source code- 
 
HSSFWorkbook wb = new HSSFWorkbook(); 
HSSFSheet sheet = wb.createSheet("new sheet"); 
 
try { 
if (iExtractResults != null) { 
iData = iExtractResults.nextResultIterator(); 
if (iData != null) { 
// Get metadata on retrieved results 
org.eclipse.birt.report.engine.api.IResultMetaData irmd = iData 
.getResultMetaData(); 
int colCount = irmd.getColumnCount(); 
System.out.println("Column Count =" + colCount); 
for (int j = 0; j < colCount; j++) { 
System.out.println("Column Name =" 
+ irmd.getColumnName(j)); 
System.out.println("Column Type =" 
+ irmd.getColumnTypeName(j)); 
System.out.println("Column Label =" 
+ irmd.getColumnLabel(j)); 
 
 
} 
 
HSSFRow rowHeading = sheet.createRow(0); 
rowHeading.createCell(0).setCellValue("Date"); 
rowHeading.createCell(1).setCellValue("Count"); 
int rowCount = 1; 
while (iData.next()) { 
HSSFRow row = sheet.createRow(rowCount++); 
// System.out.println(stringTokenizer.`); 
 
// Just disply the first two columns 
Object objColumn1; 
Object objColumn2; 
try { 
objColumn1 = iData.getValue(0); 
} catch (DataException e) { 
objColumn1 = new String(""); 
} 
 
row.createCell(0).setCellValue( 
iData.getValue(0).toString()); 
Cell cell=row.getCell(0); 
cell.setCellType(1); 
CreationHelper creationHelper = wb.getCreationHelper(); 
CellStyle style = wb.createCellStyle(); 
style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy")); 
cell.setCellStyle(style); 
try { 
objColumn2 = iData.getValue(1); 
} catch (DataException e) { 
objColumn2 = new String(""); 
} 
row.createCell(1).setCellValue( 
Double 
.parseDouble(iData.getValue(1) 
.toString())); 
Cell cell1=row.getCell(1); 
cell1.setCellType(0); 
 
//cell.setCellType(arg0) 
 
System.out.println(objColumn1 + " , " + objColumn2); 
} 
iData.close(); 
wb.write(reportOutput); 
} 
} 
} catch (Exception e) { 
e.printStackTrace(); 
}

kiranv

Another simple approach is to bind a grid with the dataset at designtime itself and then add a Dynamic Text to the detail section.

Then render the .rptdesign file as Excel through Java code

kiranv

now consider this interesting scenario.

my dataset(at design time) has 2 cols -

date SomeCount

2009-1-1 10
2009-1-15 30
2009-2-1 40
2009-2-14 20
..
..

..and so on

Now i can generate a graph from birt designer with the above dataset. however i can also group by date with interval as Month such that it will group all months together and do a sum of the counts for that month.

it will label each series on X-axis as Jan-2009, Feb-2009 and its value on Y-Axis will correspond to the sum.. So the generated graph is something like the attachment (graph.jpg)

So the runtime dataset is something like this

col1 col2

Jan-2009 40
Feb-2009 60

I tried my best but we cannot access this runtime dataset created by Birt

it will always give me the dataset at design time.. even if u generate the chart from birt designer and then use "Export Data" feature of Birt-viewer it will still give u the design time dataset and not the runtime dataset..

Any idea on how we can access this runtime dataset????

cypherdj

Hi Kiranv, 
 
this makes sense to me. Not what you'd like to see, but the behaviour seems the correct one. 
 
What is happening is that the data set in itself returns the raw data, without grouping, so when you perform a data extraction task for this data set, you get exactly the raw data, which is the purpose of the extraction task. Remember you are doing the grouping within the chart, based on your data set, and not the other way around. 
 
So, if you wanted your data to be grouped before the extraction task runs (that is, also before you generate the chart), you would need to perform the grouping within your data set, instead of within the chart. 
 
In effect, what you get from the extraction task is all down to how you design your data set. And for performance reasons, you might want to do the grouping in the query anyway, no point fetching 5000 rows if you can return 200 once they are grouped, providing you don't do any further data analysis in your report. 
 
Does that make sense? 
Regards, 
Cedric

kiranv

i do agree, it makes sense and thats the way it should be. However there should be some APIs provided by BIRT to access the runtime dataset as well, just like we can access the runTimeSeries in scripting..

I hope they come out with it in their next releases.
If however, there are existing APIs to access the dynamic dataset(runtime) then kindly share it with me.

Thanks,

Kiran