CODEWITHSUNDEEP
sql-serverreporting-servicessql-server-2016
JM1
JM1

Reputation: 1715

SSRS - How can multiple data sets be combined in SSRS?

The dashboard picture below shows the structure of what I need to create in SSRS. Each red box represents a different dataset, so there are 14 potential datasets that total different information.

Each query is totaled with a ROW_NUMBER which enables me to limit how many of the rows show on the report, so I don't know how to combine queries to reduce the number from 14 different datasets and keep this functionality.

This report is by school so each dataset will only need to show data for that school, and I need a page break per school.

Can a report like this be created with SSRS? Is so, what are some options on how to do it please? Would I use a list, table, etc?

This will not be published on a server and will only need to be run a few times a year as a .pdf. from a local machine. The data however is on a sql-server.

Thank you.

enter image description here

Upvotes: 0

Views: 5145

Answers (2)

Russell Fox
Russell Fox

Reputation: 5427

Yes, it can be done, but the performance might suffer. Create a small report for each section: no headers, no footers, just the stuff you want to appear on the dashboard. Then create a "master" report with a table - I'd create that first section as this report so the table has a data source - and then add the other reports as sub-reports into the cells of the master table. You can create links between main and sub-reports, like having summary information in a header and the detail information in a sub-report, but you don't have to.

You're a bit constrained about how to organize it visually because you can only merge cells horizontally, not vertically, but you could get around that by (shudder) creating a report with sub-reports and putting that as a sub-report into the master. But that will probably run as slow as a lizard in a freezer.

Hopefully this makes sense, but if you do little research on sub-reports you'll figure it out.

Upvotes: 1

Tony Roe
Tony Roe

Reputation: 64

It sounds like you have a number of totally different data sets so my first point would be is it appropriate to have these all on one report? However if it is then this answer seems to solve your issue

use two different datasets in single report

You can use two different datasets in different reports regions on one Report. For example, If you have two datasets, lets say DS1 showing EmployeeDetail and DS2 showing SalesInfo. You can use them separately in two tables one showing Employees Details and other showing Sales Info. If you plan to merge this data and use it in one table, These are option.

Recommended option : Re-write your query to create single dataset containing possible data you want to show in tabular fashion.

Would work but would be lot slower : Use DS1 in a table in Main Report where each row contains Epmloyee Detail, Wihtin that Row call a Sub-Report passing EmployeeDetail's key which is related to a column in SalesInfo. Create sub-report showing SaledInfo data, call this report in main report passing key value from DS1 to this sub-report.

Otherwise maybe considering splitting this into multiple reports.

Upvotes: 0

Related Questions