Multiple Tablix that are different from each other with multiple lines of data per tablix using the same dataset in one report

Question

I have an SSRS report that contains 3 Tablix using the SAME dataset. The first 2 Tablix have an Employee Filter of "E" and the 3rd Tablix has an Employee Filter of "D". Each of the 3 Tablix have a parent group of EmployeeName.

When I run the report for 1 person, everything shows up as desired on one page showing only the information for the one employee in each respective tablix. When I choose 2 employees, each tablix shows each employee one after the other instead of 1 employee on one page, the second employee on page 2 etc.

How do I get one employees information per page for all 3 tablixs.

I have tried the ListView and Rectangle with the same results.

I want to get one employees information per page for all 3 tablixs.

Alan Schofield Alan Schofield · Accepted Answer · 2019-05-17T16:56:40

I find the easiet way to do this is with sub-reports.

Make a backup of your current report before you start...

Now edit the current report so it only accepts a single employee as oyu know that works OK. Save the report and call it something like subEmpReport

Next create a new blank report.

Now create a dataset that just contains a list of unique Employees selected. I'll assume that this is an ID field called employeeID for simplicity, and that your employee parameter is called empID.

So the query for your dataset (called dsEmpList for this example) would be something like

SELECT DISTINCT employeeID 
    FROM myEmployeeTable 
    WHERE employeeID = @empID

This will create a parameter for you, set this parameter up just like on your original report, so that it shows a list of employees and is multi-valued.

Now create a table on your report design and sets it's dataset to dsEmpList (the dataset we just created).

Remove all rows and columns from the table except one, so you'll just have a single 'cell' left. Expand this cell so it's the width of your report.

Now right click the cell and do Insert => Sub report. Right-click this new subreport placeholder and choose properties. Set the subreport to subEmpReport we created at the start. GO to the parameters tab in the same dialogue box and set the parameter name to the name of the parameter in your subreport. Set the value from the drop down which should just be employeeID (this is the field name from the dsEmpList created earlier.

Besides a bit of formatting that should be it.

When the report runs, the 'master' report will run, creating a row in the table for each employee record in dsEmpList, each row will contain your subreport and each sub report will have the employeeID passed in.

Multiple Tablix that are different from each other with multiple lines of data per tablix using the same dataset in one report

2 Answers