CODEWITHSUNDEEP
jasper-reportsdatasource
Kevin Cruijssen
Kevin Cruijssen

Reputation: 9326

Jasper(Soft) : Use multiple Oracle-Database datasources for single report

I have a Jasper server which has a few templates in one of the folders. I also have an application with a button, and when you click the button it will show a list of all the jasper documents from that folder.
Currently a single parameter (an id) is used in all these Jasper reports, which each use their own query based on what they need.

Now I've added a new jasper template, which has to use a different datasource based on a year parameter, in this same folder.
What I could do is just add this year to the parameter list I send to Jasper for every template in this folder, in which case it will use the id for all the existing templates, and the year for this new one.
However, I prefer to not deploy a new version of this application for the sake of an added jasper template, so I'd like to do the following instead:

  1. Use the parameter id with datasource 1 to query for the year I need
  2. Use this year as parameter with datasource 2 to create the query for the actual jasper report template

So my question: Is this possible? Can a single jasper report have multiple oracle-DB connection datasources? And can I then query for the year in one datasource, to use in the other datasource?

EDIT:

Although the duplicated link gave me a good direction, I'm currently again stuck with the same problem.
What I've done is the following:

  1. I've created the main-report which contains nothing more than a sub-report, and has an id parameter (which will be used to query for the field YEAR in datasource_host_main).
  2. I've created a sub-report with the actual text and lists and such based on a query on datasource_host_sub (with a year parameter).

Here is the code for Main:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.1.final using JasperReports Library version 6.3.1  -->
<!-- 2017-04-10T17:05:30 -->
<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="Main" language="groovy" pageWidth="595" pageHeight="842" columnWidth="515" leftMargin="40" rightMargin="40" topMargin="20" bottomMargin="20" uuid="3830f8f8-db92-4043-bcbe-4af8cbc24623">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="datasource_host_main"/>
    <property name="com.jaspersoft.studio.unit." value="pixel"/>
    <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="ireport.jasperserver.url" value="my_jasper_server_host/jasperserver-pro/"/>
    <property name="ireport.jasperserver.user" value="superuser|"/>
    <property name="ireport.jasperserver.reportUnit" value="/users/user_1/reports/Systeem/Main"/>
    <property name="ireport.jasperserver.report.resource" value="/users/user_1/reports/Systeem/Main_files/main.jrxml"/>
    <parameter name="id_parameter" class="java.lang.Integer">
        <parameterDescription><![CDATA[id_parameter]]></parameterDescription>
        <defaultValueExpression><![CDATA[1]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[select dataYear 
from MY_TABLE
where id = $P{id_parameter}]]>
    </queryString>
    <field name="dataYear" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="28" splitType="Stretch"/>
    </title>
    <detail>
        <band height="210" splitType="Stretch">
            <subreport>
                <reportElement x="6" y="10" width="504" height="200" uuid="e130bc4e-f114-4bd1-b408-dc9ce261b18c"/>
                <subreportParameter name="dataYear">
                    <subreportParameterExpression><![CDATA[$F{dataYear}]]></subreportParameterExpression>
                </subreportParameter>
                <!-- This below doesn't work: I want to connect to a different datasource in the sub-report -->
                <connectionExpression><![CDATA[oracle.jdbc.driver.OracleDriver.getConnection("jdbc:oracle:thin:@datasource_host_sub:1234:ab", "USERNAME", "PASSWORD")]]></connectionExpression>
                <subreportExpression><![CDATA["subreport.jrxml"]]></subreportExpression>
            </subreport>
        </band>
    </detail>
    <columnFooter>
        <band/>
    </columnFooter>
    <pageFooter>
        <band height="39"/>
    </pageFooter>
</jasperReport>

Subreport:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.1.final using JasperReports Library version 6.3.1  -->
<!-- 2017-04-10T16:37:07 -->
<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="subreport" language="groovy" pageWidth="595" pageHeight="842" columnWidth="515" leftMargin="40" rightMargin="40" topMargin="20" bottomMargin="20" uuid="3830f8f8-db92-4043-bcbe-4af8cbc24623">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="datasource_host_sub"/>
    <property name="com.jaspersoft.studio.unit." value="pixel"/>
    <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="ireport.jasperserver.url" value="my_jasper_server_host/jasperserver-pro/"/>
    <property name="ireport.jasperserver.user" value="superuser|"/>
    <property name="ireport.jasperserver.reportUnit" value="/users/user_1/reports/Systeem/Main"/>
    <property name="ireport.jasperserver.report.resource" value="/users/user_1/reports/Systeem/Main_files/subreport.jrxml"/>
    <parameter name="dataYear" class="java.lang.Integer">
        <parameterDescription><![CDATA[dataYear]]></parameterDescription>
        <defaultValueExpression><![CDATA[YEAR( )]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[select NOTES
from MY_TABLE
where extract(year from to_date(DATE, 'dd-MM-yy')) = $P{dataYear}]]>
    </queryString>
    <field name="NOTES" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="140" splitType="Stretch"/>
    </title>
    <detail>
        <band height="60" splitType="Stretch">
            <textField>
                <reportElement x="0" y="18" width="510" height="30" uuid="ed29caa8-a839-4457-a270-68bfedcfadb8"/>
                <textFieldExpression><![CDATA["Test: " + $F{NOTES}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <columnFooter>
        <band/>
    </columnFooter>
    <pageFooter>
        <band height="20"/>
    </pageFooter>
</jasperReport>

The Main report uses datasource_host_main, and the sub-report uses datasource_host_sub. Because a Jasper report has only one .properties file with one jasperDatasourceUri, I can only define datasource_host_main. But how can I define datasource_host_sub for the sub-report? As you can see in the mainreport code I tried to do something like this:

<connectionExpression><![CDATA[oracle.jdbc.driver.OracleDriver.getConnection("jdbc:oracle:thin:@datasource_host_sub:1234:ab", "USERNAME", "PASSWORD")]]></connectionExpression>

But unfortunately it gives this error:

net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression for source text: oracle.jdbc.driver.OracleDriver.getConnection("jdbc:oracle:thin@datasource_host_sub:1234:ab", "USERNAME", "PASSWORD")

What should I put here instead to make it work (both are Oracle databases), and is it even possible to do it like this?

As I said in my original question:

Can a single jasper report have multiple oracle-DB connection datasources? And can I then query for the year in one datasource, to use in the other datasource?

Upvotes: 0

Views: 1681

Answers (1)

dada67
dada67

Reputation: 5083

In JasperReports Server you can use a different data source/DB connection for a subreport or subdataset by setting the com.jaspersoft.jrs.data.source property as the data source repository path at (sub)report or subdataset level.

In your case, you would have the report unit use a data source that connects to datasource_host_main, and the following property in the subreport JRXML:

<property name="com.jaspersoft.jrs.data.source" value="/datasources/host_sub_datasource"/>

where /datasources/host_sub_datasource is the repository path of a data source resource that connects to datasource_host_sub.

Outside JasperReports Server, the same thing can be achieved with data adapters (which replace JasperReports Server data sources). The property to set is net.sf.jasperreports.data.adapter, and it needs to point to a path/resource/URI for a data adapter XML file.

Upvotes: 1

Related Questions