How to display report parameter Label field in RDLC reports

While working on customer issue recently, I came across following scenario where there was a problem in displaying the report parameter label filed in the RDLC reports.

Scenario and issue:

Let's say we have report parameter called “Customer” in the RDLC report which get values from the “DataSet1” as shown below

We have a text box with expression in the report to display parameter “Customer” value field and label field as shown below.

If we run the report, we see only the value field instead of label field as show below.

We can clearly see that Parameters!Customer.Label  is not holding the Label Value and only holds the Parameter value. We see this behavior only in RDLC reports because of we don’t bind the dataset to the Label field with in the RDLC report.

To display the parameter label field in the RDLC report we can use the Built-In Lookup function.

We need to use Lookup to retrieve the values from the specified dataset for a name/value pair where there is a 1-to-1 relationship. For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region.

Syntax

 Lookup(source_expression, destination_expression, result_expression, dataset)

Example:

 =Lookup(Fields!SaleProdId.Value, Fields!ProductID.Value,  Fields!Name.Value, "Product")

Replaced the “Parameters!Customer.Label”  expression with the Lookup function as shown below.

Note: If report parameter datatype does not match with the dataset field datatype, conversion required.

 Expression Value: =Parameters!p.Value & " | " & Lookup(CInt(Parameters!p.Value), Fields!CustomerKey.Value, Fields!FirstName.Value, "DataSet1")

If we run the report, we see the value field and label field as shown below.

Author:     SatyaSai K – Support Engineer, SQL Server BI Developer team, Microsoft

Reviewer:   Kane Conway – Support Escalation Engineer, SQL Server BI Developer team, Microsoft