Reference a field from a different dataset in SSRS

0
votes

I need some help creating an expression in a dataset called "ReportData" that references a field from a different dataset called "Contracts".

I've tried the expression builder, and created the following expression:

=IIf((First(Fields!Product.Value, "Contracts")="Loan"),
 cdbl(Sum(Fields!Provision.Value, "Contracts")),
 cdbl(0))

This returns 0, which is correct, because First(Fields!Product.Value, "Contracts")="Loan" is indeed false, but I want to evaluate the "entire" Product Field, and not just the first value.

3
reporting-services

3 Answers

1
votes

These expressions assume the current dataset scope is DatasetA.

This is a basic sum from DatasetB that will show if the value in DatasetA is "some text". Not sure if this will do what you need.

=IIf(Fields!Field1.Value = "some text", Sum(Fields!Field2.Value, "DatasetB"), 0)

This looks at the first value in DatasetB and does a sum based on that. This seems closest to the pseudocode you presented.

=IIf(First(Fields!Field1.Value, "DatasetB") = "some text", Sum(Fields!Field2.Value, "DatasetB"), 0)

This is the lookup version which returns the Field2 value in DatasetB based on a value (Field1) in DatasetA (current scope) matching a value (Field1) in DatasetB. I added this in case you need it for some reason while troubleshooting.

=Lookup(Fields!Field1.Value, Fields!Field1.Value, Fields!Field2.Value, "DatasetB")

Maybe a little more than you need, but hopefully one of these works for you.

EDIT:

=IIf(Lookup("Loan", Fields!Product.Value, Fields!Provision.Value.ToString().Length, "Contracts") > 0, Sum(Fields!Provision.Value, "Contracts"), 0)
0
votes

If in doubt, use the expression builder!

Within the properties of the element you wish to modify:

  1. Click
  2. You will see Datasets within the list of categories.
  3. From there you can select any field from any dataset.
  4. Build your expression within this window!
0
votes

Try this:

=IIF(
SUM(IIF(Fields!Product.Value="Loan",1,0),"Contracts")>0,
CDbl(Sum(Fields!Provision.Value, "Contracts")),
Cdbl(0)
)

UPDATE:

=SUM(IIF(Fields!Product.Value="Loan",Fields!Provision.Value,0),"Contracts")

Let me know if this helps.