Counting rows using conditional dates in Reporting Service
I’ve been using Reporting Services a lot. As with many things I learn on a “needs” basis.
Something I’ve done quite a bit of is have to run counts on the data returned by datasets. Something that stumped me early on when I started was how to run a count against data that was returned based on dates.
It was actually nice and simple:
=SUM(IIF(Fields!DateField.Value = Today(),1,0))
In this example what we’re basically doing is checking if the date is equal to “today”, if it is it’s given a value of 1, if not then 0. And of course, it’s summed up. So this would give us the SUM() of dates equal to today.
A variation of this conditional counting (and something I’ve had to do) is to run a comparison like this against a date and a “non date” value.
So in this example our Fields!DateField.Value is actually stored as an integer. So we need to convert the integer to a date to have it count properly.
=SUM(IIF(new DateTime(Fields!WOODDT.Value / 10000, (Fields!WOODDT.Value / 100) mod 100, Fields!WOODDT.Value mod 100) = Today(),1,0))
So in the above example, we are creating a datetime type with our DateTime() function and then comparing it to the Today() value. If the newly created date value (which is again based off of the data returned from the dataset) is equal to today’s date it is given the value of 1, if not then 0. And this is summed up.