Can I use a list to repeat two tablixes on a report page?

Question

See image below. Warehouse and Customer are dropdown lists populated via query. Year is a text field. The two tables display data from datasets driven by the report parameters. Is there a way to repeat the two tables based on each member of the Customer dropdown? Preferbly with a pagebreak after the 2nd table.

Answer 1

I normally do this using subreports. You could either create a single subreport that contains both tables or individual subreports. The individual approach might help with page breaks etc so that's the way I'd go.

Step 1: Create a report for your first table. As you don't state what each table does, I'll make some up for the sake of illustration.

The key is to create a subreport that displays just the info you need in a single table. So in your case this might mean we only need to pass in a single parameter, CustomerID. You might need to pass in more such as Warehouse but I don't know...

In my made up scenario, let's assume the report shows customer contacts so we create a subreport (let's call it subCustomerConacts). It has a single parameter pCustomerID and a single dataset dsCustomerContacts. The query might looks something like SELECT * FROM CustomerContacts WHERE CustomerID = @pCustomerID. Add whatever tables/textbox etc you need to display your data.

Test this subreport works by manually typing in a CustomerID

Step 2: Create a report for your second table. Do exactly the same again, creating a new subreport. Let call this subCustomerOrders. Repeat as above until you end up with another report that can display order details (or whatever you need).

Finally, create you main report. This is basically what you have described in you question in terms of parameters etc.

Now to add this bit that will call you subreports. Create a dataset (let's call it dsCustomerLoop) that contains each customer from your parameter something like SELECT DISTINCT CustomerID FROM myCustomers WHERE CustomerID IN (@myCustomerParameter)

Add a table to your report, 1 column wide and stretch it so it's wide enough to accommodate you subreports.

Set the dataset to point to dsCustomerLoop

Right-Click the cell in the detail row and do "Insert Row -> Inside Group - Below". You should not have two detail rows.

Next, right click the top detail row and do "Insert -> Subreport"

Right-Click the newly inserted subreport control and choose "properties".

Choose your first SubReport form the drop-down list Click parameters on the left, Click "Add" and select the CusomterID parmeter, set it's value to the CustomerID field.

Repeat this process on the seconds row, choosing your seconds subreport.

You may want to also add a 3rd row to the table, you could insert a rectangle into this with page breaks set to force a new page after each seconds subreport.

That's It. When the report runs it will produce two rows per customer, each row containing a subreport.

I hope this is clear enough, I've rushed through it a bit but if anything is unclear, let me know and I'll provide a clearer solution.