Multiple datasets for large paginated report

Question

I have a very large report that utilized 33 separate data sets (all from the same source).

This report generates 33 pages into a single PDF file.

It takes over an hour to run, is there anyway to make it run faster?

Thanks

bradsy · Answer

Better to have fewer datasets. You can have one dataset or a couple of datasets. Then for each table, filter down to the data you want. This applies to the paginated reports in the service:&nbsp;https://learn.microsoft.com/en-us/sql/reporting-services/report-design/add-a-filter-report-builder-and-ssrs?view=sql-server-ver16&nbsp;Or at the dataset level:&nbsp;https://www.mssqltips.com/sqlservertip/2597/dataset-and-tablix-filtering-in-sql-server-reporting-services/

bradsy · Answer

Interesting why you would need 33 different data sets from the same data source. Each dataset will query the data source, possibly with multiple connections. There might be queing or other things going on because only so many connections can be made at one time. I suggest to look at the report design and why you need that many datasets.

If there are any aggregations or filtering done in the report, consider to move those to the datasets\queries to reduce the amount of data retrieved.

Here is an article that might help. https://learn.microsoft.com/en-us/power-bi/guidance/report-paginated-data-retrieval

kimomakano · Answer

Thanks for the quick reply.Each data set in the report populates a different table, the each table is a separate page of the report.We used to create this in Crystal Reports but now are required to us Power BI

kimomakano · Answer

Thanks for&nbsp; asking.&nbsp;It is a bizarre report for sure.The report has 33 separate pdf pages, each page summarizes different aspect of data with differing columns.It is too complex to use a single data set

anonymous · Answer

Hi ,
Based on the description, try to optimize the queries to select only the required columns.
&nbsp;
Best Regards,
Wisdom Wu
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Multiple datasets for large paginated report

9 Replies

Related content

Paginated Report

Paginated Report background Image multiple pages

Paginated Report error while loading dataset

Paginated Report Error while loading dataset

paginated reports from two or more datasets