Jasper report missing first row

Question

I'm generating a report using JasperReports and suppose to export it into several formats. But currently im only working with excel reports.

Here is my controller code.

    InputStream in = reportTemplate.getTemplate(reportInquery.getTemplateFile());
    JasperPrint print = JasperFillManager.fillReport(in, null,
                new JRResultSetDataSource(reportDao.getReportData(reportInquery)));
    resp = HttpConfig.setHeaders(reportInquery, resp);
    Exporter exporter = reportOption.getRenderOptions(reportInquery.getFormat(), resp.getOutputStream(), print);
    exporter.exportReport();

In my configuration factory, excel report configures as follows, after calling getRenderOptions method.

    public Exporter exporterOptions(OutputStream outputStream, JasperPrint print) {
    JRXlsExporter exporter = new JRXlsExporter();
    exporter.setExporterInput(new SimpleExporterInput(print));
    OutputStreamExporterOutput outputStreamExporterOutput = new SimpleOutputStreamExporterOutput(outputStream);
    exporter.setExporterOutput(outputStreamExporterOutput);
    SimpleXlsReportConfiguration configuration = new SimpleXlsReportConfiguration();
    configuration.setOnePagePerSheet(true);
    configuration.setDetectCellType(true);
    configuration.setMaxRowsPerSheet(100);
    configuration.setRemoveEmptySpaceBetweenColumns(true);
    configuration.setRemoveEmptySpaceBetweenRows(true);
    exporter.setConfiguration(configuration);
    return exporter;
}

To create JRResultSetDataSource I have used oracle OracleCachedRowSet in above getReportData method.

public RowSet getReportData(ReportInqueryDTO reportInquery) {
    try {
        String query = getQueryById(reportInquery.getTemplateId());
        Map<String, String> params = new HashMap<>();
        params.put("fromDate", reportInquery.getFromDate());
        params.put("toDate", reportInquery.getToDate());
        params.put("appName", this.applicationName);

        RowSet rowSet = namedParameterJdbcTemplate.query(query, params, new ResultSetExtractor<RowSet>() {
            @Override
            public RowSet extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                OracleCachedRowSet rs = new OracleCachedRowSet();
                rs.populate(resultSet);
                return rs;
            }
        });


        rowSet.beforeFirst();
        return rowSet;
    } catch (Exception exception) {
        log.error("getReportData failed.");
    }
    return null;
}

My problem is, excel always comes with first record missing from, where the cursor of the resultSet is pointed. (Ex: Here rowSet.beforeFirst() means before 1st index. I'm missing the first record in this scenario.)

I'm stuck with this issue couple of days. I want to know if this is a jasper issue how can I resolve it. If it can not be resolved, I want to know how to add a cached rowset dynamically before the first record?

I have couple of jrxml files and, one of it is added below.Every file follows the same format.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.0.final using JasperReports Library     version 6.0.0  -->
<!-- 2014-12-30T15:20:10 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="TTT_Call_Transfers_Report" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="b61676f8-0366-4125-996c-7564d0f77eb4">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="NewDataAdapter"/>
<style name="Table_TH" mode="Opaque" backcolor="#F0F8FF">
    <box>
        <pen lineWidth="0.5" lineColor="#000000"/>
        <topPen lineWidth="0.5" lineColor="#000000"/>
        <leftPen lineWidth="0.5" lineColor="#000000"/>
        <bottomPen lineWidth="0.5" lineColor="#000000"/>
        <rightPen lineWidth="0.5" lineColor="#000000"/>
    </box>
</style>
<style name="Table_CH" mode="Opaque" backcolor="#BFE1FF">
    <box>
        <pen lineWidth="0.5" lineColor="#000000"/>
        <topPen lineWidth="0.5" lineColor="#000000"/>
        <leftPen lineWidth="0.5" lineColor="#000000"/>
        <bottomPen lineWidth="0.5" lineColor="#000000"/>
        <rightPen lineWidth="0.5" lineColor="#000000"/>
    </box>
</style>
<style name="Table_TD" mode="Opaque" backcolor="#FFFFFF">
    <box>
        <pen lineWidth="0.5" lineColor="#000000"/>
        <topPen lineWidth="0.5" lineColor="#000000"/>
        <leftPen lineWidth="0.5" lineColor="#000000"/>
        <bottomPen lineWidth="0.5" lineColor="#000000"/>
        <rightPen lineWidth="0.5" lineColor="#000000"/>
    </box>
</style>
<subDataset name="Dataset1" uuid="54db10cf-a696-4f7d-b642-96871feb617d">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="NewDataAdapter"/>
    <queryString language="SQL">
        <![CDATA[]]>
    </queryString>
    <field name="DATE_OF_TRANSFER" class="java.sql.Timestamp"/>
    <field name="AGENT_ID" class="java.lang.String"/>
    <field name="AGENT_NAME" class="java.lang.String"/>
    <field name="CTALK_CONTACT_ID" class="java.lang.String"/>
    <field name="ASSOCIATED_QUEUE_ID" class="java.lang.String"/>
</subDataset>
<queryString language="SQL">
    <![CDATA[]]>
</queryString>
<background>
    <band splitType="Stretch"/>
</background>
<title>
    <band height="63" splitType="Stretch">
        <staticText>
            <reportElement x="160" y="16" width="222" height="30" uuid="d89e2553-d3fb-4360-b797-4f69b60938a5"/>
            <text><![CDATA[TTT Call Transfers Report]]></text>
        </staticText>
    </band>
</title>
<detail>
    <band height="253" splitType="Stretch">
        <componentElement>
            <reportElement x="0" y="0" width="555" height="253" uuid="c229d6c3-18b7-4de2-a669-01e11d25c642"/>
            <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
                <datasetRun subDataset="Dataset1" uuid="e0262612-dc81-4d84-abf8-9b52609ba792">
                    <datasetParameter name="REPORT_DATA_SOURCE">
                        <datasetParameterExpression><![CDATA[$P{REPORT_DATA_SOURCE}]]></datasetParameterExpression>
                    </datasetParameter>
                </datasetRun>
                <jr:column width="40" uuid="a91061c3-7760-43d2-bdc1-091f0e712aaa">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="40" height="30" uuid="6afcdf24-958c-49f4-9ef3-b757ffd1e8de"/>
                            <text><![CDATA[DATE_OF_TRANSFER]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:columnFooter style="Table_CH" height="30"/>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="40" height="30" uuid="84a5e4b7-30e0-409c-b2ab-e13c1dd02950"/>
                            <textFieldExpression><![CDATA[$F{DATE_OF_TRANSFER}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
                <jr:column width="40" uuid="c7b5a8eb-04b6-4cd6-9328-e1e8ce11a2fc">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="40" height="30" uuid="d7cc290a-6918-4d47-8f55-b43761bee59b"/>
                            <text><![CDATA[AGENT_ID]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:columnFooter style="Table_CH" height="30"/>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="40" height="30" uuid="b5f2b8bf-9132-4447-9209-f1fcb3b873c4"/>
                            <textFieldExpression><![CDATA[$F{AGENT_ID}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
                <jr:column width="40" uuid="d2427379-2c72-4e33-9b46-ade8727dbed5">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="40" height="30" uuid="bdcb01c5-5073-4a10-b498-e2d1a2cf7c78"/>
                            <text><![CDATA[AGENT_NAME]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:columnFooter style="Table_CH" height="30"/>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="40" height="30" uuid="e259ef09-f446-491c-88dd-f7694197e060"/>
                            <textFieldExpression><![CDATA[$F{AGENT_NAME}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
                <jr:column width="40" uuid="81fe9b33-57aa-48b2-914b-e3e9b45cf6a1">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="40" height="30" uuid="ef5e379b-65de-4ea0-a5cb-13368efa16f0"/>
                            <text><![CDATA[CTALK_CONTACT_ID]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:columnFooter style="Table_CH" height="30"/>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="40" height="30" uuid="5f49f1a9-379a-4d02-9e55-fc6a865aa828"/>
                            <textFieldExpression><![CDATA[$F{CTALK_CONTACT_ID}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
                <jr:column width="40" uuid="f6e8b2a6-925b-41b5-9043-1ffa215871f1">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="40" height="30" uuid="b55c7599-a237-43f1-aff5-464717e3c917"/>
                            <text><![CDATA[ASSOCIATED_QUEUE_ID]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:columnFooter style="Table_CH" height="30"/>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="40" height="30" uuid="0d2d53d0-1cb2-4d64-a09d-b0eeacca694d"/>
                            <textFieldExpression><![CDATA[$F{ASSOCIATED_QUEUE_ID}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
            </jr:table>
        </componentElement>
    </band>
</detail>

Thanks in advance.

Could you check whether the RowSet returned contains all retrieved rows? Actually the question is where you lose the row in the RowSet filling or the RowSet reflecting in Jasper DataSource? — StanislavL, Dec 30 '14 at 06:18
@StanislavL In the commented loop it contains all records. So the data is missing after adding to Jasper Datasource. — Maleen Abewardana, Dec 30 '14 at 06:29

score 7 · Answer 1 · edited May 23 '17 at 12:02

Please post your jrxml file.

The problem might not be in your code.

In Jasper, the record pointer in the data source is incremented by every element that receives it (so, for exemple, if you in the report you have a table and you set the datasource of the table as being the datasource of the report it will skip the first record. If this is the case, you have to pass the datasource from the report as a parameter to the table).

UPDATE:

1.Send your datasource from the server as a parameter, and fill the report with a different one (can be empty).

2.Decalre a new parameter in the report of same type as your bean collection, let's name it 'DS1'.

3.Set TableDatasource to use the $P{DS1} parameter.

See my response to How to show JRBeanCollectionDataSource data with help of Table component? for an example.

score 2 · Accepted Answer · edited May 23 '17 at 12:10

As Laura mentioned in this answer the record pointer in the data source is incremented by every element that receives it. Since I'm passing the result set, I thought no point in passing result set again as a parameter. Finally I decided to add an empty record at the beginning of the result set.

In getReportData method in above question, you can do it by, following below method.

    RowSet rowSet = namedParameterJdbcTemplate.query(query, params, new ResultSetExtractor<RowSet>() {
            @Override
            public RowSet extractData(ResultSet resultSet) throws SQLException, DataAccessException {

                OracleCachedRowSet rs = new OracleCachedRowSet();
                rs.populate(resultSet);

                // Have to add a empty row, because jasper is not displaying
                // the first row of report.
                rs.setReadOnly(false);
                rs.beforeFirst();
                rs.moveToInsertRow();

                int numCol = rs.getMetaData().getColumnCount();
                for (int i = 1; i < numCol + 1; i++) {
                    // Add null inserted row to each column
                    rs.updateNull(i);
                }

                rs.insertRow();
                rs.beforeFirst();
                return rs;
            }
        });

score 1 · Answer 3 · edited Dec 18 '20 at 22:33

As they already said, the problem might not be in your code.

The record pointer in the data source is incremented by one when you pass your dataSet into a ResultSet. I solved this problem creating a ResultSet with the properties: ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE. In the approach, you can only execute your query in your database and do rs.beforeFirst.

Connection conn = ...;
Statement stmt = null;
ResultSet rset = null;
try {
    stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    rset = stmt.executeQuery(sql);
    if(rset != null){
        rset.beforeFirst();
        return rset;
    } else return null;
} catch (SQLException e) {
    e.printStackTrace();
}

The method beforeFirst() will set the record pointer before the first row. So, you can do:

rs.beforeFirst(); 
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
jp = JasperFillManager.fillReport(jasperFile, parametersJasper,jrRS);

score 0 · Answer 4 · edited Jul 18 '19 at 07:46

My Actual Parameter is ds. And List is allDataListPlan (But 1 row is missing) So I have added dummyRecord with dummyList and added it in JasperFillManager.

Send your datasource from the server as a parameter, and fill the report with a different one (can be empty).
Declare a new parameter in the report of same type as your bean collection, let's name it 'dummy'.
Set TableDatasource to use the $P{dummy} parameter.

Example:

*ArrayList<ProductionOrder> **dummyList** = new ArrayList<ProductionOrder>();
                       **dummyList** .add(new ProductionOrder());
JRBeanCollectionDataSource **dummy** = new JRBeanCollectionDataSource(**dummyList**);
JRBeanCollectionDataSource ds = new JRBeanCollectionDataSource(allDataListPlan);

mapForJasperHeader.put("dummy", **dummy**);
mapForJasperHeader.put("ds_for_plan", ds);
String s = VaadinService.getCurrent().getBaseDirectory().getAbsolutePath();
JasperPrint jasperPrint = JasperFillManager.fillReport(s + "\\JasperFiles\\" + "PlanVsActual.jasper",
        mapForJasperHeader, **dummy**);*

Jasper report missing first row

4 Answers4

Linked