SSRS Report Repeat tablix inside list for each parameter value

Question

I have a tablix that I want to have repeated for each value of a parameter. Basically the parameter consists of a list of currencies and I want the tablix to be generated for each currency. It's the same table each time just filtered for a different currency for each of the parameter values. Each copy of the table has to be on it's own page so that when the user exports to excel each sheet is for a different currency.

I tried to use this solution: SSRS report repeat table for every parameter value

So I have two datasets, dsList and dsTablix.

The problem is once I put the tablix control inside the list control the tablix dataset gets set to dsList and I can't change the tablix's dataset back to dsTablix. Now the report doesn't run at all because the tablix refers to fields from dsTablix but it's dataset property is set to dsList.

How do I keep the tablix pointing to dsTablix while it's inside of a list?

Answer 1

You don't need to put one tablix inside another to achieve this.

Assuming your dsTablix dataset query looks something like this

SELECT * FROM myTable WHERE CurrencyID in (@CurrencyID)

Then simply add a tablix, add as many details rows as you need to display all the data and set the textbox to whatever you need.

Then just add a rowgroup that groups on CurrencyID (or whatever fields you need to split by) . In the rowgroup properties set page breaks to 'between each instance'. You can also set the page name in the group property to whatever you want and this will be the sheet name in Excel. So you might set the page name to something like =Fields!CurrencyName.Value