CODEWITHSUNDEEP
sqlreporting-servicesreportreportbuilder
d90
d90

Reputation: 787

SSRS Report Builder Aging Buckets

I'm trying to create an aging report for customers by sales person. I have customer groups, then all the customers in that group. I'm trying to calculate the sum of money due by aging buckets of <30, Between 31 and 60, between 61 and 90, and >90.

My SQL view calculates the age of the bill in the table as OverDueDays, and the total of the bill as AmountDC.

I came up with the following for the <30 bucket, but it's not displaying the correct values. 

=IIF(Fields!OverDueDays.Value<30 , Sum(Fields!AmountDC.Value),0)

Any ideas as to what I've done wrong?

Upvotes: 0

Views: 2027

Answers (2)

John Schultz
John Schultz

Reputation: 672

try the following expressions if you have multiple datasets:

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

Upvotes: 0

ExactaBox
ExactaBox

Reputation: 3395

I haven't used Reporting Services in a while, so the syntax may not be 100% correct. Basically, you want to switch the order of your SUM and IIF. Try something like:

=SUM(IIF(Fields!OverDueDays.Value < 30, Fields!AmountDC.Value, 0))

bonus: if you wanted to count the number of customers in a bucket:

=SUM(IIF(Fields!OverDueDays.Value < 30, 1, 0))

Upvotes: 2

Related Questions