Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you populate an SSRS TextBox with a DataSet value?

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"

like image 624
Pressacco Avatar asked Oct 03 '13 13:10

Pressacco


People also ask

How do I add a dataset to RDL?

In the Report Data pane, right-click the name of the data source, and then click Add Dataset.

How do I create a parameterized SSRS report?

To add SSRS Report Parameters, Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option. Once you click on Add parameters.. option opens a new window called Report parameter Properties to configure the parameter properties in SSRS.


1 Answers

Using a Dataset called HeaderData like:

enter image description here

I have a couple of textboxes in a report header:

enter image description here

Where the expression above is:

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

This is working OK:

enter image description here

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.

like image 64
Ian Preston Avatar answered Oct 27 '22 20:10

Ian Preston