Jaspersoft Studio Subreport with Tables

[ianfouldsx-act.co.uk]

Hi, I'm hoping someone can point me in the right direction here, I'm having problems with subreports that contain tables.

The reports are not neccesarily complex, the datasource in all cases is MS SQL and all the reports and tables use the same source but with different queries.

I have managed to link the main report and subreport and get data to be displayed for simple cases, i.e. no tables using $P{REPORT_CONNECTION} in the Data Source Expresson in the top level report. Using an SQL select for the results in the detail section all is working well. However when I introduce a table into the sub report it just does not work when run as a subreport, everything works OK when the subreport is viewed as a stand-alone but not when run from the main report. Again just to emphasise, Main report -> subreport is OK and data is shown, main report -> subreport -> table does not display any results.

For the Dataset definition of the table in the subreport I have tried all of the obvious things, 'Use the same connection as the master report, $P{REPORT_CONNECTION}, blah blah, clearly there is some guru magic required here to get it work.

The whole scenario of subreports not working out of the box seems utterly bizare to me, if a report is designed with a datasource and datasets surely by default it should use these definitions. However I can see a flaw in this in that when a subreport is imported into JasperServer it is stored just as a resource with no definition as is a normal report. I would suggest that the datasource deletion that must occur when a subreport is run is because of this design flaw in the JasperServer, if subreports had definitions just like normal reports you wouldn't have this constant headache for developers. Subreports in my mind should just run without requiring any bizarre changes from running as a stand-alone report.

Any help would be appreciated, this has been causing me grief now for several days and I'm beginning to think about looking for another BI system.

Many thanks.

[rajesh.sirsikar]

Hi Ian,

I tried recreating your issue, but in Jaspersoft Studio 6.0.0, I am able to see teh table data present in Subreport in Main report.

I have attached 2 report files here. They use inbuilt datasource so it would be easier for you to test it.

Thanks,

Rajesh S

 

[ianfouldsx-act.co.uk]

Hi Rajesh

Many thanks for your reply and test reports. They did indeed work when I tried them, I also managed to get another test report working without too much effort as well using your example as a base model.

I think my problem was using the incorrect data source linkage from the main report to the sub report, then experimenting with connections to the tables all of which ended in a total mess.

So for anyone else having similar nightmares with this, I'd like to summarise what Rajesh sorted out for me and what I am trying to do:

1. Create a main report with an MS SQL datasource, this main report does not list any records its self, just pulls in the subreports.

2. All the reports reports in my scenario are using the same MS SQL datasource.

3. The main report Dataset is filled with the query: 'SELECT NULL AS Empty;', this returns one record only ** Important**

4. I have 2 subreports, each in a seperate detail band in the report **important if you have more than one subreport**

5. The subreports connections are linked with: Connection Expression   $P{REPORT_CONNECTION}

6. The tables in the subreports also use the same SQL connection. The table Datasets in each subreport are linked with 'Use the same connection used to fill the master report'.

Rajesh once again thank you very much, this is all now working as I intended. It's a pity we can't attach files to posts as your test case would be invaluable I think as a simple demonstration of linking a main report to a sub report with tables.

 

Regards

[rajesh.sirsikar]

Thanks Ian,

Please upvote my answer, which will enable it to be visible to users looking for similar answers.

Thanks

Rajesh S