Dynamic Headers with Multi-value Parameter

Hi Bernice​,

To add the missing piece to the grouping suggestion above: the reason the header always shows the first department is that a page header cannot see the current group directly, so =Fields!Department.Value (or First()) is evaluated once for the whole dataset. The standard trick is to put the department in a textbox inside the group and have the page header read that textbox through ReportItems, which is evaluated per page.

1. Group the tablix by Department with a page break

Row Groups > Add Group > Parent Group > Group by: [Department]
  -> tick "Add group header"
Group Properties > Page Breaks
  -> Between each instance of a group = checked
  -> (optional) Page numbering: Reset page number = checked
Advanced mode (Row Groups dropdown > Advanced) > static row of the
group header > RepeatOnNewPage = True, KeepWithGroup = After

2. Put the department in the group header in a textbox named, for example, txtDept with =Fields!Department.Value. You can hide it if you don't want it in the body (Hidden = True still works for ReportItems as long as it is on the page).

3. Page header textbox:

="Department: " & First(ReportItems!txtDept.Value)

4. Optional extras: set the group's PageName to =Fields!Department.Value, so when you export to Excel each department lands on its own sheet, and use Globals!PageNumber / Globals!TotalPages in the footer with the reset option to get "Page 1 of 3" per department.

Also make sure the dataset filters on the parameter with IN (or the dataset query uses WHERE Department IN (@Department)), so Select All returns all 14 departments in one run.

So my overall approach would be:

Dataset: WHERE Department IN (@Department)
-> Group tablix by Department, page break between instances
-> txtDept in the repeating group header
-> Page header: First(ReportItems!txtDept.Value)
-> PageName + reset page numbers per department

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.