nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Subreports with Scripted Dataset using Handiler class

Pandiyan

Hi Friends, 
I need help from any BIRT experts in subreport on Scripted Data Source. 
 
The requirement likes to show the Employee information and employee additional information. 
The Employee has many transactions like leave information, Daily Task Information and much other addition information. All addition transaction information is stored in separate tables. The report can show 5000 records for each addition information for an employee. 
 
The report output like, 
1. Employee details 
a. If leave flag is ?Yes? then needs to show leave information otherwise no needs to show. 
b. If Daily Task flag is ?Yes? then the daily activity needs to show otherwise no need to show. 
The Implementation: 
Points 1: 
I have created multiple data sets like 1. Employee_DS, 2. Emp_Leave_DS 3. Emp_Task_DS and so... and I have created Hander java class for each DS and implemented beforeOpen(), Open and fetch() methods. 
1. beforeOpen():get Input Parameter Value and assign to local property. 
2. Open(): fetching the data from DB as based on employee Id and assign to global variable 
3. Fetch(): a. reading the global variable data b. setting row data 
 
Point 2: 
My rpt design are like, using many details rows for each additional information. 
1. First details row, display employee details 
2. Second detail row, Leave information and the visibility condition if (leave_flag ==?Y?) {false;}else{true;} 
3. Third and fourth detail row used for other additional info and the 2?nd step is continued for all other additional information. 
The Problem is: 
The report is get generated with employee details which is data from Employee_DS? the additiona DS handler classes all methods are get called but the additional information are not display on report. 
 
Note: I can?t use single data set because the requirement to show 5000 additional information for each employee . 
 
please help me to correct my code and I don?r know why the additional information are not display even the rows are setting .

Find more posts tagged with

Comments

kclark

Can you post your rptdesign for me to look at please?

Pandiyan

Thanks Kclark, 
 
I have posted the sample rpt design file. The handler class I have open() and fetch() methods are overridden from ScriptedDataSetEventAdapter.Java. 
 
Open method: fetch data from DB and added to global variable 
Fetch Method: read the global variable data and set row value. 
 
 
RPT File 
 
<?xml version="1.0" encoding="UTF-8"?> 
<report xmlns="<a class='bbc_url' href='http://www.eclipse.org/birt/2005/design'>http://www.eclipse.org/birt/2005/design"</a> version="3.2.23" id="1"> 
<property name="createdBy">Eclipse BIRT Designer Version 4.2.0.v20120611 Build <4.2.0.v20120612-1721></property> 
<property name="units">in</property> 
<property name="iconFile">/templates/blank_report.gif</property> 
<property name="bidiLayoutOrientation">ltr</property> 
<property name="imageDPI">96</property> 
<data-sources> 
<script-data-source name="sampleSDS" id="7"/> 
</data-sources> 
<data-sets> 
<script-data-set name="EmpDs" id="8"> 
<property name="eventHandlerClass">com.testbirtjavapro.handler.EmpHandler</property> 
<list-property name="resultSetHints"> 
<structure> 
<property name="position">1</property> 
<property name="name">EMP_ID</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">EMP_NAME</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">3</property> 
<property name="name">SALARY</property> 
<property name="dataType">decimal</property> 
</structure> 
<structure> 
<property name="position">4</property> 
<property name="name">DEPT_ID</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">5</property> 
<property name="name">JOB_DESC</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">6</property> 
<property name="name">Leave_SW</property> 
<property name="dataType">string</property> 
</structure> 
</list-property> 
<list-property name="columnHints"> 
<structure> 
<property name="columnName">EMP_ID</property> 
<text-property name="displayName">EMP_ID</text-property> 
</structure> 
<structure> 
<property name="columnName">EMP_NAME</property> 
<text-property name="displayName">EMP_NAME</text-property> 
</structure> 
<structure> 
<property name="columnName">SALARY</property> 
<text-property name="displayName">SALARY</text-property> 
</structure> 
<structure> 
<property name="columnName">DEPT_ID</property> 
<text-property name="displayName">DEPT_ID</text-property> 
</structure> 
<structure> 
<property name="columnName">JOB_DESC</property> 
<text-property name="displayName">JOB_DESC</text-property> 
</structure> 
<structure> 
<property name="columnName">Leave_SW</property> 
<text-property name="displayName">Leave_SW</text-property> 
</structure> 
</list-property> 
<structure name="cachedMetaData"> 
<list-property name="resultSet"> 
<structure> 
<property name="position">1</property> 
<property name="name">EMP_ID</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">EMP_NAME</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">3</property> 
<property name="name">SALARY</property> 
<property name="dataType">decimal</property> 
</structure> 
<structure> 
<property name="position">4</property> 
<property name="name">DEPT_ID</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">5</property> 
<property name="name">JOB_DESC</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">6</property> 
<property name="name">Leave_SW</property> 
<property name="dataType">string</property> 
</structure> 
</list-property> 
</structure> 
<property name="dataSource">sampleSDS</property> 
</script-data-set> 
<script-data-set name="LeaveDs" id="9"> 
<property name="eventHandlerClass">com.testbirtjavapro.handler.LeaveHandler</property> 
<list-property name="resultSetHints"> 
<structure> 
<property name="position">1</property> 
<property name="name">Tot_Leave</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">Leave_Balance</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">3</property> 
<property name="name">From_Date</property> 
<property name="dataType">date</property> 
</structure> 
<structure> 
<property name="position">4</property> 
<property name="name">To_Date</property> 
<property name="dataType">date</property> 
</structure> 
<structure> 
<property name="position">5</property> 
<property name="name">Leave_Desc</property> 
<property name="dataType">string</property> 
</structure> 
</list-property> 
<list-property name="columnHints"> 
<structure> 
<property name="columnName">Tot_Leave</property> 
<text-property name="displayName">Tot_Leave</text-property> 
</structure> 
<structure> 
<property name="columnName">Leave_Balance</property> 
<text-property name="displayName">Leave_Balance</text-property> 
</structure> 
<structure> 
<property name="columnName">From_Date</property> 
<text-property name="displayName">From_Date</text-property> 
</structure> 
<structure> 
<property name="columnName">To_Date</property> 
<text-property name="displayName">To_Date</text-property> 
</structure> 
<structure> 
<property name="columnName">Leave_Desc</property> 
<text-property name="displayName">Leave_Desc</text-property> 
</structure> 
</list-property> 
<list-property name="parameters"> 
<structure> 
<property name="name">Emp_Id</property> 
<property name="dataType">integer</property> 
<property name="position">1</property> 
<expression name="defaultValue" type="javascript">null</expression> 
<property name="isInput">true</property> 
<property name="isOutput">false</property> 
</structure> 
<structure> 
<property name="name">Dept_id</property> 
<property name="dataType">integer</property> 
<property name="position">2</property> 
<expression name="defaultValue" type="javascript">null</expression> 
<property name="isInput">true</property> 
<property name="isOutput">false</property> 
</structure> 
</list-property> 
<structure name="cachedMetaData"> 
<list-property name="resultSet"> 
<structure> 
<property name="position">1</property> 
<property name="name">Tot_Leave</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">Leave_Balance</property> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="position">3</property> 
<property name="name">From_Date</property> 
<property name="dataType">date</property> 
</structure> 
<structure> 
<property name="position">4</property> 
<property name="name">To_Date</property> 
<property name="dataType">date</property> 
</structure> 
<structure> 
<property name="position">5</property> 
<property name="name">Leave_Desc</property> 
<property name="dataType">string</property> 
</structure> 
</list-property> 
</structure> 
<property name="dataSource">sampleSDS</property> 
</script-data-set> 
</data-sets> 
<styles> 
<style name="report" id="4"> 
<property name="fontFamily">sans-serif</property> 
<property name="fontSize">10pt</property> 
</style> 
<style name="crosstab-cell" id="5"> 
<property name="borderBottomColor">#CCCCCC</property> 
<property name="borderBottomStyle">solid</property> 
<property name="borderBottomWidth">1pt</property> 
<property name="borderLeftColor">#CCCCCC</property> 
<property name="borderLeftStyle">solid</property> 
<property name="borderLeftWidth">1pt</property> 
<property name="borderRightColor">#CCCCCC</property> 
<property name="borderRightStyle">solid</property> 
<property name="borderRightWidth">1pt</property> 
<property name="borderTopColor">#CCCCCC</property> 
<property name="borderTopStyle">solid</property> 
<property name="borderTopWidth">1pt</property> 
</style> 
<style name="crosstab" id="6"> 
<property name="borderBottomColor">#CCCCCC</property> 
<property name="borderBottomStyle">solid</property> 
<property name="borderBottomWidth">1pt</property> 
<property name="borderLeftColor">#CCCCCC</property> 
<property name="borderLeftStyle">solid</property> 
<property name="borderLeftWidth">1pt</property> 
<property name="borderRightColor">#CCCCCC</property> 
<property name="borderRightStyle">solid</property> 
<property name="borderRightWidth">1pt</property> 
<property name="borderTopColor">#CCCCCC</property> 
<property name="borderTopStyle">solid</property> 
<property name="borderTopWidth">1pt</property> 
</style> 
</styles> 
<page-setup> 
<simple-master-page name="Simple MasterPage" id="2"> 
<page-footer> 
<text id="3"> 
<property name="contentType">html</property> 
<text-property name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property> 
</text> 
</page-footer> 
</simple-master-page> 
</page-setup> 
<body> 
<table id="10"> 
<property name="width">7.947916666666667in</property> 
<property name="dataSet">EmpDs</property> 
<list-property name="boundDataColumns"> 
<structure> 
<property name="name">EMP_ID</property> 
<text-property name="displayName">EMP_ID</text-property> 
<expression name="expression" type="javascript">dataSetRow["EMP_ID"]</expression> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="name">EMP_NAME</property> 
<text-property name="displayName">EMP_NAME</text-property> 
<expression name="expression" type="javascript">dataSetRow["EMP_NAME"]</expression> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="name">SALARY</property> 
<text-property name="displayName">SALARY</text-property> 
<expression name="expression" type="javascript">dataSetRow["SALARY"]</expression> 
<property name="dataType">decimal</property> 
</structure> 
<structure> 
<property name="name">DEPT_ID</property> 
<text-property name="displayName">DEPT_ID</text-property> 
<expression name="expression" type="javascript">dataSetRow["DEPT_ID"]</expression> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="name">JOB_DESC</property> 
<text-property name="displayName">JOB_DESC</text-property> 
<expression name="expression" type="javascript">dataSetRow["JOB_DESC"]</expression> 
<property name="dataType">string</property> 
</structure> 
</list-property> 
<column id="39"> 
<property name="width">1.59375in</property> 
</column> 
<column id="40"> 
<property name="width">2.6875in</property> 
</column> 
<column id="41"> 
<property name="width">1.1041666666666667in</property> 
</column> 
<column id="42"> 
<property name="width">0.9791666666666666in</property> 
</column> 
<column id="43"> 
<property name="width">1.5833333333333333in</property> 
</column> 
<header> 
<row id="11"> 
<cell id="12"/> 
<cell id="14"/> 
<cell id="16"/> 
<cell id="18"/> 
<cell id="20"/> 
</row> 
</header> 
<group id="78"> 
<property name="groupName">EMP_Name</property> 
<expression name="keyExpr" type="javascript">row["EMP_NAME"]</expression> 
<structure name="toc"> 
<expression name="expressionValue" type="javascript">row["EMP_NAME"]</expression> 
</structure> 
<property name="hideDetail">false</property> 
<header> 
<row id="79"> 
<cell id="80"/> 
<cell id="81"/> 
<cell id="82"/> 
<cell id="83"/> 
<cell id="84"/> 
</row> 
<row id="92"> 
<cell id="93"> 
<label id="13"> 
<text-property name="text">EMP_ID</text-property> 
</label> 
</cell> 
<cell id="94"> 
<label id="15"> 
<text-property name="text">EMP_NAME</text-property> 
</label> 
</cell> 
<cell id="95"> 
<label id="17"> 
<text-property name="text">SALARY</text-property> 
</label> 
</cell> 
<cell id="96"> 
<label id="19"> 
<text-property name="text">DEPT_ID</text-property> 
</label> 
</cell> 
<cell id="97"> 
<label id="21"> 
<text-property name="text">JOB_DESC</text-property> 
</label> 
</cell> 
</row> 
</header> 
<footer> 
<row id="85"> 
<cell id="86"/> 
<cell id="87"/> 
<cell id="88"/> 
<cell id="89"/> 
<cell id="90"/> 
</row> 
</footer> 
</group> 
<detail> 
<row id="22"> 
<property name="height">0.96875in</property> 
<cell id="23"> 
<data id="24"> 
<property name="resultSetColumn">EMP_ID</property> 
</data> 
</cell> 
<cell id="25"> 
<data id="26"> 
<property name="resultSetColumn">EMP_NAME</property> 
</data> 
<table id="98"> 
<property name="dataSet">LeaveDs</property> 
<list-property name="paramBindings"> 
<structure> 
<property name="paramName">Emp_Id</property> 
<simple-property-list name="expression"> 
<value type="javascript">row["EMP_ID"]</value> 
</simple-property-list> 
</structure> 
<structure> 
<property name="paramName">Dept_id</property> 
<simple-property-list name="expression"> 
<value type="javascript">row["DEPT_ID"]</value> 
</simple-property-list> 
</structure> 
</list-property> 
<list-property name="boundDataColumns"> 
<structure> 
<property name="name">Tot_Leave</property> 
<text-property name="displayName">Tot_Leave</text-property> 
<expression name="expression" type="javascript">dataSetRow["Tot_Leave"]</expression> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="name">Leave_Balance</property> 
<text-property name="displayName">Leave_Balance</text-property> 
<expression name="expression" type="javascript">dataSetRow["Leave_Balance"]</expression> 
<property name="dataType">integer</property> 
</structure> 
<structure> 
<property name="name">From_Date</property> 
<text-property name="displayName">From_Date</text-property> 
<expression name="expression" type="javascript">dataSetRow["From_Date"]</expression> 
<property name="dataType">date</property> 
</structure> 
<structure> 
<property name="name">To_Date</property> 
<text-property name="displayName">To_Date</text-property> 
<expression name="expression" type="javascript">dataSetRow["To_Date"]</expression> 
<property name="dataType">date</property> 
</structure> 
<structure> 
<property name="name">Leave_Desc</property> 
<text-property name="displayName">Leave_Desc</text-property> 
<expression name="expression" type="javascript">dataSetRow["Leave_Desc"]</expression> 
<property name="dataType">string</property> 
</structure> 
</list-property> 
<column id="127"/> 
<column id="128"/> 
<column id="129"/> 
<column id="130"/> 
<column id="131"/> 
<group id="132"> 
<property name="groupName">LEAVE_DESC_GROUP</property> 
<expression name="keyExpr" type="javascript">row["Leave_Desc"]</expression> 
<structure name="toc"> 
<expression name="expressionValue" type="javascript">row["Leave_Desc"]</expression> 
</structure> 
<property name="hideDetail">false</property> 
<header> 
<row id="146"> 
<cell id="147"> 
<label id="101"> 
<text-property name="text">Tot_Leave</text-property> 
</label> 
</cell> 
<cell id="148"> 
<label id="103"> 
<text-property name="text">Leave_Balance</text-property> 
</label> 
</cell> 
<cell id="149"> 
<label id="105"> 
<text-property name="text">From_Date</text-property> 
</label> 
</cell> 
<cell id="150"> 
<label id="107"> 
<text-property name="text">To_Date</text-property> 
</label> 
</cell> 
<cell id="151"> 
<label id="109"> 
<text-property name="text">Leave_Desc</text-property> 
</label> 
</cell> 
</row> 
</header> 
<footer> 
<row id="139"> 
<cell id="140"/> 
<cell id="141"/> 
<cell id="142"/> 
<cell id="143"/> 
<cell id="144"/> 
</row> 
</footer> 
</group> 
<detail> 
<row id="110"> 
<cell id="111"> 
<data id="112"> 
<property name="resultSetColumn">Tot_Leave</property> 
</data> 
</cell> 
<cell id="113"> 
<data id="114"> 
<property name="resultSetColumn">Leave_Balance</property> 
</data> 
</cell> 
<cell id="115"> 
<data id="116"> 
<property name="resultSetColumn">From_Date</property> 
</data> 
</cell> 
<cell id="117"> 
<data id="118"> 
<property name="resultSetColumn">To_Date</property> 
</data> 
</cell> 
<cell id="119"> 
<data id="120"> 
<property name="resultSetColumn">Leave_Desc</property> 
</data> 
</cell> 
</row> 
</detail> 
<footer> 
<row id="121"> 
<cell id="122"/> 
<cell id="123"/> 
<cell id="124"/> 
<cell id="125"/> 
<cell id="126"/> 
</row> 
</footer> 
</table> 
</cell> 
<cell id="27"> 
<data id="28"> 
<property name="resultSetColumn">SALARY</property> 
</data> 
</cell> 
<cell id="29"> 
<data id="30"> 
<property name="resultSetColumn">DEPT_ID</property> 
</data> 
</cell> 
<cell id="31"> 
<data id="32"> 
<property name="resultSetColumn">JOB_DESC</property> 
</data> 
</cell> 
</row> 
</detail> 
<footer> 
<row id="33"> 
<cell id="34"/> 
<cell id="35"/> 
<cell id="36"/> 
<cell id="37"/> 
<cell id="38"/> 
</row> 
</footer> 
</table> 
</body> 
</report> 
 
The Sample Handler Class: 
/** 
* 
*/ 
package com.testbirtjavapro.handler; 
 
import java.sql.ResultSet; 
import java.sql.SQLException; 
 
import org.eclipse.birt.report.engine.api.script.IReportContext; 
import org.eclipse.birt.report.engine.api.script.IUpdatableDataSetRow; 
import org.eclipse.birt.report.engine.api.script.ScriptException; 
import org.eclipse.birt.report.engine.api.script.eventadapter.ScriptedDataSetEventAdapter; 
import org.eclipse.birt.report.engine.api.script.instance.IDataSetInstance; 
 
public class EmpHandler extends ScriptedDataSetEventAdapter { 
IReportContext context; 
/** 
* Sample Code 
*/ 
@Override 
public boolean fetch(IDataSetInstance dataSet, IUpdatableDataSetRow row){ 
ResultSet empRs = (ResultSet)context.getGlobalVariable("empRs"); 
try { 
if (null != empRs || empRs.next()){ 
return false; 
} 
row.setColumnValue("EMP_ID", empRs.getLong(1)); 
row.setColumnValue("EMP_NAME", empRs.getString(2)); 
row.setColumnValue("SALARY", empRs.getLong(1)); 
row.setColumnValue("DEPT_ID", empRs.getLong(1)); 
row.setColumnValue("JOB_DESC", empRs.getString(1)); 
} catch (SQLException e) { 
e.printStackTrace(); 
}catch (Exception e) { 
e.printStackTrace(); 
} 
return true; 
} 
 
@Override 
public void open(IDataSetInstance dataSet) throws ScriptException{ 
ResultSet empRs = new EmpDAO().fetchAllEmp(); 
context.setGlobalVariable("empRs", empRs); 
} 
 
@Override 
public void beforeOpen(IDataSetInstance dataSet, IReportContext context){ 
this.context = context; 
} 
 
/** 
* @param args 
*/ 
public static void main(String[] args) { 
 
} 
 
} 
 
/** 
* 
*/ 
package com.testbirtjavapro.handler; 
 
import java.sql.ResultSet; 
import java.sql.SQLException; 
 
import org.eclipse.birt.report.engine.api.script.IReportContext; 
import org.eclipse.birt.report.engine.api.script.IUpdatableDataSetRow; 
import org.eclipse.birt.report.engine.api.script.ScriptException; 
import org.eclipse.birt.report.engine.api.script.eventadapter.ScriptedDataSetEventAdapter; 
import org.eclipse.birt.report.engine.api.script.instance.IDataSetInstance; 
 
public class LeaveHandler extends ScriptedDataSetEventAdapter { 
IReportContext context; 
/** 
* All Code are sample 
*/ 
@Override 
public boolean fetch(IDataSetInstance dataSet, IUpdatableDataSetRow row){ 
ResultSet empRs = (ResultSet)context.getGlobalVariable("leaveRs"); 
try { 
if (null != empRs || empRs.next()){ 
return false; 
} 
row.setColumnValue("Tot_Leave", empRs.getLong(1)); 
row.setColumnValue("Leave_Balance", empRs.getString(2)); 
row.setColumnValue("To_Date", empRs.getDate(3)); 
row.setColumnValue("To_Date", empRs.getDate(4)); 
row.setColumnValue("Leave_Desc", empRs.getString(5)); 
} catch (SQLException e) { 
e.printStackTrace(); 
}catch (Exception e) { 
e.printStackTrace(); 
} 
return true; 
} 
 
@Override 
public void open(IDataSetInstance dataSet) throws ScriptException{ 
Integer empId = (Integer)dataSet.getInputParameterValue("EMP_ID"); 
Integer deptId = (Integer)dataSet.getInputParameterValue("DEPT_ID"); 
ResultSet empRs = new LeaveDAO().fetchLeaveDetails(empId,deptId); 
context.setGlobalVariable("leaveRs", empRs); 
} 
 
@Override 
public void beforeOpen(IDataSetInstance dataSet, IReportContext context){ 
this.context = context; 
} 
 
/** 
* @param args 
*/ 
public static void main(String[] args) { 
 
} 
}

kclark

Can you build a simple sample report with data that I can use to test it please?

Pandiyan

Thanks Kclark, 
 
I have posted sample rptdesign file and 2 handler class. 
 
The sample rpt file has 2 dataset actually the original requirement has 6 more data set with 6 tables. The EmpDs has main table and all other are inside EmpDs table. The report is getting generated with one subreport, but other subreport data are not displayed on output of the pdf file. 
 
Note: The data are fetch from DB at runtime in beforeOpen() method if you wants to generate sample code then we can use with ArrayList instead of ResultSet. 
 
 
 
<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="112832" data-time="1356723660" data-date="28 December 2012 - 12:41 PM">
Can you build a simple sample report with data that I can use to test it please? </blockquote>