0

*I'm using SSRS 2017.

I've got a SSRS report with two parameters: a datetime and an integer (ID value). It uses the ID value as a filter in the main tablix in the body, and also for expressions in the header and footer.

I need to change the report so that it can accept multiple integer ID values (passing in from app) and essentially run the report multiple times (once for each ID value). I need the header and footer to correspond with each different ID value, as opposed to being the same on each page because they're using the First function to get just the values from the first row of the dataset.

What's the best way to get one PDF that's essentially my current report run multiple times--once for each ID?

I appreciate the help.

kfinn360
  • 25
  • 6
  • you need to look at sub-reports. You should be able to use your current report as a subreport and call it repeatedly from a master report, once for each value in your m,aster reports dataset. I've posted plenty of sub-report answers like this. Here's one of them which might help. https://stackoverflow.com/questions/53212190/ssrs-create-as-many-tables-as-needed-using-one-dataset-based-on-a-dataset-fiel/53212832#53212832 – Alan Schofield May 20 '20 at 15:55
  • It looks like the sub-report strategy won't work for me because I need the header and footer of the report being called as a sub-report to display, which appears not to be possible in SSRS. The other approach of using a row group on the ID with a page break afterward works well for the body of the report but I need to get the header and footer values to change for each ID. Any thoughts on how to make that happen? – kfinn360 May 20 '20 at 20:28
  • I think (not tested) you can reference the page contents in the header/footer. So in the header you could have an expression like `=FIRST(ReportItems!CustomerNameTextBox.Value)` Where `CustomerNameTextBox` is the name of the textbox in the body of the report. – Alan Schofield May 20 '20 at 20:45
  • That approach worked well--thank you for the suggestion! The only thing I'm still struggling with is filtering the main tablix by the ID parameter, which in this case can be one OR multiple ID values. I would appreciate any thoughts you have on how best to filter a tablix so that it returns rows for multiple specified ID values. – kfinn360 May 22 '20 at 01:03
  • When you say "Main Tablix", is this in the subreport or in th emain report that you are using as a loop? It might be worth editing the question and showing asome images of the current design and what you expect the output to look like. `Also note` that these methods will run the subreport multiple times but will not produce separate PDF's etc, it will be one continuous report with page breaks. – Alan Schofield May 22 '20 at 10:19
  • Probably need to edit your question ans show you report design and dataset query. Generally though, I leave parameters as text datatype even if they are numeric and then in my dataset query it simple `SELECT * FROM myTable WHERE myColumn IN (@myParameter)`. Simple as that, SSRS will take care of the multivalued parameter and insert it comma separate for you without any work on your part. – Alan Schofield May 22 '20 at 23:46

0 Answers0