How do you populate an SSRS TextBox with a DataSet

Question

Dear community member,

Given a specific Key (e.g. "user_name"), how does one populate an SSRS TextBox with the corresponding DataSet Value (e.g. "John Doe")?

Any insight you can provide would be greatly appreciated!

CONTEXT

• SQL Server = 2008 R2
• IDE = Visual Studio 2012
• ReportData DataSet contains data used to populate a Tablix in the body of the report
• HeaderData DataSet contains data used to populate two TextBox elements in the report header
  • this DataSet contains two columns: key and value

CONSTRAINTS

• for this particular problem, I cannot simply bind the HeaderData DataSet to a Tablix in the report header... I have to populate TextBox elements
• If a report contains multiple DataSets, and you reference the Fields collection in an expression... you will receive a "The Value expression for the text box users an aggregate expression without scope" error.

SAMPLE DATA

HeaderData

Key="camera_name", Value="Panomera - Terminal 1"

Key="user_name", Value="John Doe"

Answer 1

Using a Dataset called HeaderData like:

I have a couple of textboxes in a report header:

Where the expression above is:

=Max(IIf(Fields!Key.Value = "user_name"
  , Fields!Value.Value, Nothing)
  , "HeaderData")

This is working OK:

This works as it uses the IIf expression to NULL out any values other than when Key = user_name, then takes the Max of the non NULL Value values.

Since you're referencing a Dataset outside of a tablix you need a Scope and an aggregate - I'm using Max to ignore NULL values, which something like First will not do. Judging by your description there should only ever be one non NULL value there so it should be fine.