Search code examples
csvjasper-reports

Different csv files as datasource in JasperReports

I'm trying to generate report in Jaspersoft Studio. My goal is to use two different CSV files in one report. Here is what I want to achieve:

enter image description here

Everything works fine when I don't use second CSV. When I'm generating table with another CSV I have to specify Dataset connection (Before it I have created dataset with name DR and this dataset has proper data from CSV).

enter image description here

I tried without connection and with JRCsvDatasource but it doesn't work. I have not found any examples of how to do this. When I don't specify connection table is empty. When I add JRCsvDataSource it throws exception:

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Unknown column name: FID. at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:596) at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$20(ReportController.java:571) at com.jaspersoft.studio.editor.preview.view.control.ReportController$5.run(ReportController.java:449) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)

I can't set column names for JRCsvDataSource in Jaspersoft Studio and I think that this can be real reason of not working this solution.

So the problem is with connection between Table and Dataset. How to specify connection between table and new dataset?

Solution

  • In essence:

    • You need two data adapters, one per CSV file(I called them CSV_DA_1 and CSV_DA_2).
    • Link the first adapter to the report's Main dataset.
    • Export the second one to file(CSV_DA_2.xml) and point the table dataset's Default Data Adapter to it. enter image description here Only this way Studio will pick up both data adapters.
    • Not sure why, but I needed to manually set the queryString's language to csv inside the table dataset.

    The end result(.jrxml) may look like this:

    <?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.4.0.final using JasperReports Library version 6.4.1  -->
<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="Report" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="6fe5ff80-463c-48a5-870b-b9eb72c8c9f3">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="CSV_DA_1"/>
    <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>
    <style name="Table 1_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 1_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 1_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="TableDataset" uuid="e4a79faf-d2c6-42ff-9afd-1b73b938e128">
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="different_csv/CSV_DA_2.xml"/>
        <property name="net.sf.jasperreports.data.adapter" value="CSV_DA_2.xml"/>
        <queryString language="csv">
            <![CDATA[]]>
        </queryString>
        <field name="fruitStand" class="java.lang.String"/>
        <field name="fruitId" class="java.lang.String"/>
        <field name="fruitSold" class="java.lang.Integer"/>
    </subDataset>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <field name="number" class="java.lang.Integer"/>
    <field name="name" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="30" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="278" height="30" uuid="675dfea9-c792-4aa2-b5cc-1506b6de4cf6"/>
                <text><![CDATA[number]]></text>
            </staticText>
            <staticText>
                <reportElement x="278" y="0" width="277" height="30" uuid="77fcc5b3-4d31-47bc-a682-3059757ff1fb"/>
                <text><![CDATA[name]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="100" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="278" height="30" uuid="aa065144-9ec4-4658-a0c5-150cdea07ae1"/>
                <textFieldExpression><![CDATA[$F{number}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="278" y="0" width="277" height="30" uuid="5284bbc5-f1e9-47f2-9b87-a0a7f7e4011c"/>
                <textFieldExpression><![CDATA[$F{name}]]></textFieldExpression>
            </textField>
            <componentElement>
                <reportElement x="150" y="40" width="200" height="60" uuid="5e2faee2-cc09-469b-a5eb-917ddfdccbdf">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                    <property name="com.jaspersoft.studio.table.style.table_header" value="Table 1_TH"/>
                    <property name="com.jaspersoft.studio.table.style.column_header" value="Table 1_CH"/>
                    <property name="com.jaspersoft.studio.table.style.detail" value="Table 1_TD"/>
                </reportElement>
                <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="TableDataset" uuid="886ce5d8-f55a-4573-8d7e-c47f04fa818f"/>
                    <jr:column width="66" uuid="f118bba1-a9b0-4b6e-91e5-b532f5ab3f86">
                        <jr:columnHeader style="Table 1_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="66" height="30" uuid="c89e45e3-1513-44fd-932f-7dcb457fdf88"/>
                                <text><![CDATA[fruitStand]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table 1_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="66" height="30" uuid="b1211b0b-5321-42fd-bc44-3d13a291e1e1"/>
                                <textFieldExpression><![CDATA[$F{fruitStand}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="66" uuid="d2ad8e66-e7c8-44be-ae3d-d806bca7e9fa">
                        <jr:columnHeader style="Table 1_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="66" height="30" uuid="d0d7dbbb-2b2c-42f2-ad18-85bbbc1e8cc7"/>
                                <text><![CDATA[fruitId]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table 1_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="66" height="30" uuid="b1df8795-8505-4533-8dad-d74c339bcc74"/>
                                <textFieldExpression><![CDATA[$F{fruitId}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="66" uuid="26903850-beb5-440c-bb69-4d4dc3d0f5dc">
                        <jr:columnHeader style="Table 1_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="66" height="30" uuid="dac2afb9-0f76-4f94-b98f-0336fd2e3a05"/>
                                <text><![CDATA[fruitSold]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table 1_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="66" height="30" uuid="2f1f473e-04e2-4e96-a9de-a87e901c2c35"/>
                                <textFieldExpression><![CDATA[$F{fruitSold}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>

    And the output: enter image description here