Data Source Expression for running on Jasperserver?

[udo.sander]

Hello,

i want to run a report with a subreport on Jasperserver.

For the subreport, i want to use a data source defined on the jasperserver in /datasources/myDatasourceName.

What do i have to enter in the "Data Source Expression" property of the subreport?

Thank you for your help!

Udo

[kcollins]

Try this:

 

Create a separate parameter named $P{SUBREPORT_CONNECTION} and set the type to java.sql.Connection.  Note that you may have to manually enter the datatype if it isn't available in the drop down menu.  Uncheck the 'Use as prompt' box.

 

Use the following expression to create the jdbc connection:  java.sql.DriverManager.getConnection("jdbc:postgresql://dbhost:5432/database","dbusername","dbpassword")  * Modify this to match your datasource

 

From the palette in the main report, drag the subreport element onto your report. 

 

The subreport wizard will appear.  On the first page, choose the path to your subreport and click 'next'.

 

On the second page, select 'don't use any connection or datasource' and click 'next'.

 

Click 'next' through the parameters section on the third page.

 

Choose an option for your subreport expression and click 'Finish'.

 

Click on the subreport element to highlight it and edit the Parameters property by clicking the (...) button.  Add the following parameters:

 

SUBREPORT_CONNECTION = $P{SUBREPORT_CONNECTION}

[udo.sander]

Hello  kcollins,

thanks for your reply.

I already managed to set the connection as you described, using  java.sql.DriverManager.getConnection(...). But this results in hard-coding the connection in every report.

What i want to achieve is not to enter jdbc-url, user and password in the connection parameter.
Instead, for the subreport i want to reference a datasource defined on the Jasper Server Console in /datasources/myDatasourceName (the same way you can specifiy a datasource for the main report). In case we would have to change the connection, we could then change it once on the Jasper Server, and all reports referencing this datasource would automatically use the changed connection.

See attached screenshot.

Is there a way to achieve this?

 

Thank you for your help!

Udo

[ntatlock]

Here is how I got it working using a MS SQL connection for a subreport, which removes the need to hardcode the connection:

1. create the data source on jasper server you want to use in the sub report

2. in the main report, create a parameter $P{yourDataSourceParam} with Class type: com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.JdbcReportDataSource

and default value expression: com.jaspersoft.jasperserver.api.engine.jasperreports.util.RepositoryUtil.getThreadRepositoryContext().getRepository().getResource(null,"/Data_Sources/YourDataSource")

3. Now you can call functions on this parameter and pass in the connection to your subreport...like this

4. On your main report, select the subreport and remove all the expression entries.  Create a parameter that you will pass to the subreport named 'REPORT_CONNECTION', with expression like this:  java.sql.DriverManager.getConnection($P{yourDataSourceParam}.getConnectionUrl(),$P{yourDataSourceParam}.getUsername(),$P{yourDataSourceParam}.getPassword())

5. :)