SSRS - Expression using different dataset fields

Question

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

Langobardic

Answer 1

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")

Catacomb

Answer 2

As PerPlexSystem writes, asuming you only want to compare the first value from a dataset with values from another dataset, you can use the First function.

However, if you want to compare the values of each row from one dataset with with the values from each row of another dataset, then you will need to use a subreport - see here for further details.

Daube

or if you cannot stand the problems caused by subreports, such as alignment issues and the nuances of SSRS differences in rendering, you can use the =LookupSet() method in filter expressions. – Partridgeberry

Answer 3

Another option is to use a parameter as a variable. This is helpful if you want to create a calculated field in one of the datasets. This is best applied when the parameter value comes from a dataset with a single record.

Teddman