Dynamic Headers with Multi-value Parameter

Hi Bernice​, 

Can you share a sample file or include screenshots of what you are after? 

What visual are you using? Is this a Power BI report or a paginated report? 

Generally, a table visual can only have one value in the header row, it can't support multiple values there, and the default summarization is first. 

Some more details about what exactly you're trying to do would help. 

If the reports are run individually, the department name is correctly displayed in the header and the footer and everything is good.  It is just when trying to run under the select all option for multi-parameters, that it doesn't work.

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.