SSRS - Expression using different dataset fields

ssrs-2008 reporting-services bids reportbuilder3.0
Zolt picture Zolt · Mar 13, 2012 · Viewed 70.4k times · Source

I have a report with multiple data-sets. Different fields from different data-sets are used in different locations of the report.

In one part of the report, I need to do a calculation using fields from two different data-sets. Is this possible within an expression?
Can I somehow reference the data-set the field is in, in the expression?

For example, I'd like to do something like this: 

=Fields.Dataset1.Field / Fields.Dataset2.Field

Answer

PerPlexSystem picture PerPlexSystem · Mar 13, 2012

You can achieve that by specifying the scope of you fields like this:

=First(Fields!fieldName_A.Value, "Dataset1") / First(Fields!fieldName_B.Value, "Dataset2")

Assuming A is 10 and B is 2 and they are of type numeric then you will have the result of 5 when the report renders.

When you are in the expression builder you can choose the Category: Datasets, your desired dataset highlighted under Item: and then double click the desired field under Value: and it will appear in your expression string with the scope added.

Using same logic you can concatenate two fields like so:

=First(Fields!fieldName_A.Value, "Dataset1") & “ “ & First(Fields!fieldName_B.Value, "Dataset2")

Related questions

How to create a report with sections and page breaks using SSRS

I'm trying to create a report that looks like this: using a select from this table: (fiddler here for query and data) CREATE TABLE StudentData ( id int PRIMARY KEY IDENTITY, name varchar(30), subject varchar(30), currentGrade varchar(2), targetGrade varchar(2), note1 varchar(100), …

Read More
Positioning tables and charts in BIDS SSRS

See (image.1) - As you can see, in the design view I have my two tables on the left and two pie charts on the right, all neatly positioned. See (image.2) - Now I preview the report, and as you …

Read More
SSRS Adding text box that accepts input for printing

Using Business Intelligence Development Studio, I am creating a report for SSRS that requires the user to add a few notes before being printed. The notes do not need to be sent back to the SQL Server that the report …

Read More