How load the subreport data in a single database hit in SSRS?

Question

I am using SSRS, the situation is that I am loading users in the Main report and I have created a sub-report to display the user addresses (there can be multiple addresses per user). Now after adding the sub-report the report becomes considerable slow. My guess is that this is due to the fact that the Main report needs to fetch data for sub-report for each user in the list.

What I want to achieve is to load all the data for the sub-report in a single database hit and then just filter the records in the application. Is there a way to do this in SSRS reports?

If any other information is required then please let me know. Thanks for your time.

Not really - the closest you're going to get is caching the subreport data, and that may have its own complications (like generating all of the necessary cached reports). Do you need to be using the subreports? could you just build the subreport contents into the main report? — kyzen

Chris Latta Chris Latta · Accepted Answer · 2014-03-19T05:17:56

If you want to do that then you should use grouping to achieve it. For example, have as your dataset query some Sql like:

SELECT User.Name, Address.StreetNumber, Address.Street, Address.City
FROM User
INNER JOIN Address ON User.Id = Address.UserId
ORDER BY User.Name

Then on your tablix add a Group (right-click on the handles on the right of the tablix to add a group) to have a row showing the user details then in the Detail group add the addresses (you can add more detail rows if needed to format it nicely again by using the tablix handles).

This will show the user then show all the addresses for the user, then show the next user and so forth.

How load the subreport data in a single database hit in SSRS?

2 Answers