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

SSRS report losses formatting in Report Manager via Internet Explorer

I have a report that renders a report header on its own page and then two pages for each member that it finds. The report will render each page correctly for the report header and the first member listed but …

Read More
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