Alternative to Sub Reports - can't nest Tables with different Datasets to achieve this

• giles.clapham

  SSCrazy

  Points: 2201

  More actions

  January 11, 2018 at 8:11 am

  #342364

  Hi,
  SSRS 2012, SQL Server 2012 Standard Edition
  I have 4 Data Sets that I want to layout on my reports grouped by the rows in the first Data Set.
  The 4 Datasets are:
  EmployeeDetails, in the 100s of rows
  Timesheets, in the 10000s of rows (roughly 14 per Employee)
  ActivityLog,  in the 10000s of rows (some Employees will have 100s others will have 10s)
  AccidentReport,  hopefully less than 1 per Employee.
  So I would like to see the Employee and then separate tables for the other 3 Datasets.
  I have tried to nest Tables, which I thought you could do in earlier versions but perhaps I was thinking of other Reporting tools, and I've also tried creating a List and dropping a Table in that and then using Filtering to only display the relevant lines.  Neither work over different Datasets
  I don't want to use Sub Reports because I want to keep deployment as simple as possible and the supporting Stored Procs will get called for each Employee (so 300+ additional calls)
  Having a single Stored Procedure to populate the Dataset and then grouping would be OK with small datasets, but I will get a cartesian product if I bring all 4 datasets into a single query and the resultant grid would be massive and complex.
  Has anyone resolved something similar as any advice would be very welcome.
  Thanks for your time,
  Giles