SSRS: Master-detail report with two datasources

Question

I have two local data sources that I can push into the report. Works no problem. But how do I set up the report? One data source contains a list of employees, and info about them. The other contains a bunch of working hours for each employee.

I would like to use a table for the list of employees, and then have another table for the working hours beneath each employee (with their working hours).

Is this even possible? Do I have to use a Sub-Report? Would I have to merge them into one datasource? =/

Sirotek

Answer 1

As far as I can tell, it is impossible to nest one dataset inside of another one without using a subreport.

This means you need to do one of two things:

1. Refactor your two datasources into a single datasource. For example, perform a join between the employees and the working hours for each employee. You can then use the grouping properties of the Table object to format the list the way you want it.

2. If joining the two data sources is not practical, you can use subreports to accomplish what you want. Create a subreport containing the working hours data source and give it a parameter for the current employee. Filter the working hours by this parameter.

   In your parent report, you can place the subreport in the list, and pass the employee ID for the current row as a parameter.

   Note that there are a few formatting quirks involved with using subreports. I've been able to work around them in most cases, but the preferred method would definitely be number one above.

Oxalate

But if I join those tables, it will be A LOT of repeated data in those rows. Does that matter? Or does SSRS cope with that ok? How is it to add a subreport into a table? I would really like to use a table for the first datasource, cause it is so much easier to make the formatting right etc. But how is it to put a subreport in a table cell? Do you join together a bunch of them? Or how do you do it? – Sirotek

Answer 2

Adding a new answer to make sure a notification is sent for it.

Using a subreport is the easiest method when it works. You can simply drag and drop a subreport onto a table cell, and it will fill that cell's content area. Right-clicking on the subreport will allow you to edit the parameters for the subreport. Like most values in SSRS, the parameters can be set to expressions which use the fields in the table.

Within the subreport, just filter your results to show only the records associated with the employee who was passed in as a parameter.

The other route you can take is to merge your two datasources using a join. This will give you data something like this:

employee1 time1.1
employee1 time1.2
employee1 time1.3
employee1 time1.4
employee2 time2.1
employee2 time2.2
employee2 time2.3

You can then create a group on the repeated columns (employee in this example), and enable the HideDuplicates property on those columns. The result will look like this:

employee1 time1.1
          time1.2
          time1.3
          time1.4
employee2 time2.1
          time2.2
          time2.3

Oxalate

But, like I asked in a comment to the other question, how is performance when doing this? I'm thinking that when you join those two tables you will end up with one very huge table with a lot of repeated data. But on the other hand, it will only have one table and one report to deal with... and not two tables and a sub report repeated a gazillion times... – Sirotek

Try it, and find out :) From your description of the problem, I doubt that the joined data would take dramatically longer to fetch than fetching the two independently. But it could be an issue if the extra columns are unusually big. – Oxalate

Hmm... maybe I might just try to join them then. Cause it seems to be a bit tricky to get a sub report nicely inside of a table. And the columns arn't really that big. Just regular numbers and dates and some text. – Sirotek

Answer 3

To set up multiple datasources...you need to place two separate list objects into the report. Go to the designer, and in the toolbox you can put a new "list" into it. Then, you can do another report. Link that second list to your secondary dataset, which you implement through the secondary datasource.

It's a little bit of a stretch, but the basic idea is that each list object in the report can only be linked to one datasource.

Volitant

Well, that I know. But that will just result in two lists after each other wont it? I need the secondary list to come after each item in the first list. And to only include those elements that are relevant to that item... – Sirotek

To be able to link them like that, you will need to have them in the same datasource, I believe. Intertwining two datasources in a single list object won't work, which is really what you would need to do to group them on one another. – Volitant

Answer 4

In the report document class there is a property called "Database", which has a collection of tables. You can use "SetDataSource" on each one of those tables, in order to place the separate lists of objects into the report.

Plath