What is the proper way of referencing a field in a SSRS dataset?

Question

I am using SSRS 2014 SP1 x64 and VS2013 Update 5.

I have the following expressions to attempt to calculate ageing on a statement report.

=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=0, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=1 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"),Now())<=30, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=31 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=60, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=61 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=90, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=91, (Fields!LineAmount.Value, "Invoice"), 0))

Now when I try to add First to the field selector I get an error stating ... uses a First, Last or Previous aggregate in an outer aggregate.

How exactly do I reference this field from the Invoice dataset (there are at least two datasets)?

Am I doing this correctly?

score 0 · Answer 1 · answered Jul 13 '15 at 08:12

0

You need to use lookup function to reference a field from a second dataset

answered Jul 13 '15 at 08:12

cqi

539
3
13

How does the `Lookup` function help to return the value of a single Field? Wouldn't the `RunningValue(expression, function, scope)` be a better aggregate function? That way I can specify the scope and not have to compare values. – John Schultz Jul 13 '15 at 13:06
As you mentioned there are multi datasets in your report. I guess what you want is to put the field from Invoice dataset into a tablix/matrix which has another dataset embedded. Lookup function is like a SQL join function to link two datasets. Other than Lookup function and LookupSet function, most RS functions work in one dataset only. – cqi Jul 14 '15 at 01:18

score 0 · Answer 2 · answered Jul 13 '15 at 18:50

I believe the following will work:

=IIF(DateDiff("d", First(Fields!OrderDate.Value, "Invoice"), Now())<=0, RunningValue(Fields!LineAmount.Value, Sum, "Invoice"), 0)
=IIF(DateDiff("d", First(Fields!OrderDate.Value, "Invoice"), now())>=1 and DateDiff("d", First(Fields!OrderDate.Value, "Invoice"),Now())<=30, RunningValue(Fields!LineAmount.Value, Sum, "Invoice"), 0)
=IIF(DateDiff("d", First(Fields!OrderDate.Value, "Invoice"), now())>=31 and DateDiff("d", First(Fields!OrderDate.Value, "Invoice"),Now())<=60, RunningValue(Fields!LineAmount.Value, Sum, "Invoice"), 0)
=IIF(DateDiff("d", First(Fields!OrderDate.Value, "Invoice"), now())>=61 and DateDiff("d", First(Fields!OrderDate.Value, "Invoice"),Now())<=90, RunningValue(Fields!LineAmount.Value, Sum, "Invoice"), 0)
=IIF(DateDiff("d", First(Fields!OrderDate.Value, "Invoice"), now())>=91, RunningValue(Fields!LineAmount.Value, Sum, "Invoice"), 0)

What is the proper way of referencing a field in a SSRS dataset?

2 Answers2