CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2
BIReportGuy
BIReportGuy

Reputation: 817

SSRS Sum Values Based on Earliest Date

I'm trying to sum a net balance based on the earliest date in an SSRS report. In this case there are only 2 dates, but there can be more dates not more than 7 days.

Here's a sample of my data:

enter image description here

Here's what I'm trying to get with the earliest date of 10/26/15:

enter image description here

I've tried the following code, but not able to get this to work:

    =Sum(IIf(DateDiff("d",Fields!SettleFullDate.Value,today())>=7 
and DateDiff("d", Fields!SettleFullDate.Value, today())<7
and Fields!SETTLEBALANCE.Value>0), Fields!SETTLEBALANCE.Value, 0)

Update: I tried the code below and keep getting an error on the report. Could it be that I need to change the date field to an integer? enter image description here enter image description here

Thanks in advance for your help!

Upvotes: 1

Views: 2181

Answers (1)

Jonnus
Jonnus

Reputation: 3028

To compare the sum of values of two dates, the maximum and minimum in a set you can use the following equation

=Sum(iif(Fields!myDate.Value = Max(Fields!myDate.Value), Fields!myVal.Value, 0))
-Sum(iif(Fields!myDate.Value = MIN(Fields!myDate.Value), Fields!myVal.Value, 0))

This Sums all the values that match the maximum date in the dataset together, and sums all the values that match the minimum date in the dataset together, and takes one from the other.

It is irrespective of which dates you ask to be received, the above approach will work only against the records that you return to SSRS. So if you have a filter (WHERE clause) to return records between Date1 and Date2 this will still apply (Note - don't actually use 'Between' in the query)

Rather than using the maximum and minimum dates as listed here, you could also calculate a date similar to your original approach using

dateadd("d", -7, Fields!MySpecificDate.Value)

And insert that to the expression above.

Hopefully this is what you require - if not please let me know.

Upvotes: 1

Related Questions