0

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

JM1
  • 1,595
  • 5
  • 19
  • 41
  • You need to provide some more information what are the fields your using, what are the primary and secondary keys? I'm leaning towards creating a view and using this as your data source but it's hard to say without more information. – Tony Roe Jul 12 '17 at 15:39
  • @Ceaser1980, Here are a few: Top left box: Student Names, Top row, yellow box: Total count, top right box: employee names. Row 2 red box: count of policies, Row 2 blue box: count of locations, row 2 pink box, count of discipline, row 3 boxes are various totals by grade. – JM1 Jul 12 '17 at 16:01

2 Answers2

1

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.

Russell Fox
  • 5,273
  • 1
  • 24
  • 28
0

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.

Tony Roe
  • 64
  • 3
  • @Ceasar1980, leadership would like to use this as a summation type report, so getting it all on one page is a requirement. Can a single dataset contain all of the different data I'm pulling for this, including totals for different datasets, some totaled by students, other by different criteria, etc? – JM1 Jul 12 '17 at 16:22