SSRS dynamic change the report header textbox value based on tablix row group value change

Question

I'm writing a SSRS report contains 2 datasets:

CustomerAddressDS : select customer_id, customer_name, customer_address from tbl_customer_address where customer_id = @Internal_Parameter_Customer_ID

CustomerOrderDS : select customer_id, order_date, order_number from tbl_customer_orders where order_date = @Parameter_OrderDate

For some reason, I could not join both CustomerAddressDS and CustomerOrderDS into one SQL.

In the report, 2 parameters are set:

@Parameter_OrderDate are used for user to select a date for CustomerOrderDS

@Internal_Parameter_Customer_ID is an internal parameter and the default value will be referring to customer_id of dataset CustomerOrderDS

The report layout should be like:

In Report Header    
Customer ID    
Customer Name   
Customer Address

(Customer ID, Customer Name, Customer Address) are put into one textbox by using expression.

In Report Body:
A tablix bound to CustomerOrderDS to store the customer's daily orders :

 ORDER DATE          ORDER NUMBER
 YYYY-MM-DD                 XXXXX

 Total of the date:         XXXXX

The tablix is grouped by the customer ID and will be page break when changing customer ID. My question is that when changing customer ID in the tablix, how could I make the Customer ID/Name/Address on the report header changed? It seems aggregate function in the report header could not help in my case.

Any help would be highly appreciated.

Hannover Fist Hannover Fist · Accepted Answer · 2016-09-23T18:07:48

I had a similar issue where I needed data in the header that was related to a table. Unfortunately, since the header is not in the grouped area, this is not possible.

One way is to not use a header and create a header at the top of your table inside the group so it changes with your grouping.

Another way that I ended up using was to use the Global Page Name variable in the header (=Globals!PageName) and setting the Page Name property on the table.

You'd have a Page Name like:

=Fields!Customer_ID.Value & VBCRLF & 
 Fields!Customer_Name.Value & VBCRLF & 
 Fields!Customer_Address.Value

The VBCRLF is for a new line (Visual Basic Carriage Return Line Feed).

SSRS dynamic change the report header textbox value based on tablix row group value change

1 Answers