0

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?

Legion
  • 3,922
  • 8
  • 51
  • 95

1 Answers1

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

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • This almost works. The problem is it seems to be aggregating everything by currency. So instead of getting all records in USD on one sheet, I get a single record for the set on the USD sheet. How do I get it to display all the records? – Legion Sep 04 '20 at 16:11
  • You have probably created a row group in place of the original "details" instead of creating it as a parent of the details group. Either create a new tablix and start again or add a child row to your current row group and remove the grouping on this child group, this way it will show one row per record. If this doesn't help, post your report design and a sample of your dataset and I'll make the answer more specific. – Alan Schofield Sep 04 '20 at 23:13