How to sum using datediff in SSRS

Question

My goal is to get the following result in my *.rdl report from my SSRS expressions in visual studio 2008. The DueDate and InvoiceAmt are bound fields (fields!duedate.Value and fields!valuehome_1). The "NotDueYet" and "0-30 Days" are aliases with expressions referencing the bound fields.

ClientInvoice>>>DueDate>>>InvoiceAmt>>>>NotDueYet>>>>0-30 Days
4502767-00>>>>4/8/2013>>>>$75.89>>>>>>>>>>>>>>>>>>>>$75.89

4505151-00>>>>4/11/2013>>>$137.26>>>>>>>$137.26

Total:>>>>>>>>>>>>>>>>>>>$213.15>>>>>>>$137.26>>>>>>$75.89

Problem: In the Alias columns "NotDueYet" and "0-30 Days", based on my iif/sum/datediff expressions below supporting the totals, the Totals should be of the detail amounts that appear in those columns only (see above sample). And the detail amounts are based on expressions referencing the Duedate and InvoiceAmt bound fields. Instead I'm getting the following results;

ClientInvoice>>>DueDate>>>InvoiceAmt>>>>NotDueYet>>>>0-30 Days
4502767-00>>>>4/8/2013>>>>$75.89>>>>>>>>>>>>>>>>>>>>$75.89

4505151-00>>>>4/11/2013>>>$137.26>>>>>>>$137.26

Total:>>>>>>>>>>>>>>>>>>>$213.15>>>>>?>>$0.00>>>>>>>>$213.15

Here are my expressions for each field;

InvoiceAmt (DETAIL):

=(fields!valuehome_1.Value)

InvoiceAmt (TOTAL):

=Sum(fields!valuehome_1.Value)

NotDueYet (DETAIL):

= iif(datediff("d",fields!duedate.Value,now())<= 0 
and (fields!valuehome_1.value>0),fields!valuehome_1.Value,"")

NotDueYet (TOTAL):

= iif(datediff("d",fields!duedate.Value,now())<= 0 
and (fields!valuehome_1.value>0),SUM(fields!valuehome_1.Value),0)

0-30 Days (DETAIL):

= iif(datediff("d",fields!duedate.Value,now())>0 
and (datediff("d",fields!duedate.Value,now())<31 
and (fields!valuehome_1.value>0),fields!valuehome_1.Value,"")

0-30 Days (TOTAL):

= iif(datediff("d",fields!duedate.Value,now())>0 
and (datediff("d",fields!duedate.Value,now())<31 
and (fields!valuehome_1.value>0),SUM(fields!valuehome_1.Value),0)

I appreciate any assistance in resolving this mystery. Thank you.

score 0 · Answer 1 · answered Apr 12 '13 at 02:14

0

The nesting isn't quite right: Try these expressions:

For NotDueYet (TOTAL):

=SUM(
    iif(datediff("d",fields!duedate.Value,now())<= 0
        and (fields!valuehome_1.value>0),
      fields!valuehome_1.Value,
      0))

0-30 Days (TOTAL):

= SUM(
   iif(datediff("d",fields!duedate.Value,now())>0
       and (datediff("d",fields!duedate.Value,now())<31 
       and (fields!valuehome_1.value>0),
      fields!valuehome_1.Value,
      0))

I think that should get what you're after.

answered Apr 12 '13 at 02:14

Jamie F

23,189
5
61
77

Thanks for the response. However, I get #ERROR value in the result for each column. ClientInvoice>>>DueDate>>>InvoiceAmt>>>>NotDueYet>>>>0-30 Days 4502767-00>>>>4/8/2013>>>>$75.89>>>>>>>>>>>>>>>>>>>>$75.89 4505151-00>>>>4/11/2013>>>$137.26>>>>>>>$137.26 Total:>>>>>>>>>>>>>>>>>>>$213.15>>>>>?>>#ERROR>>>>>>>>#ERROR If the two columns have both invoice amts I get the correct total. ClientInvoice>>>DueDate>>>InvoiceAmt>>>>NotDueYet>>>>0-30 Days 4502767-00>>>>4/8/2013>>>>$75.89>>>>>>>$75.89 4505151-00>>>>4/11/2013>>>$137.26>>>>>>>$137.26 Total:>>>>>>>>>>>>>>>>>>>$213.15>>>>>>>>$213.15 – Ken Mahone Apr 12 '13 at 21:19

How to sum using datediff in SSRS

1 Answers1