Search code examples
reporting-servicesssrs-2008ssrs-2012

SSRS SUM IIf from TODAY, WEEK, MONTH, YEAR

I'm trying to count how many incidents have been created TODAY, for the WEEK, YEAR and TOTAL.

I've been able to get the TOTAL using: 

=Count(Fields!Issue_ID.Value, "My_Data_Set")

but I have not been successful getting the total COUNT for how many incidents have been created for: TODAY, WEEK, YEAR against IncidentDateTime field.

enter image description here

enter image description here

https://i.sstatic.net/LvTo1.jpg

https://i.sstatic.net/1jpas.jpg

Solution

  • Use these expressions.

    For today:

    =SUM(IIF(Fields!IncidentDateTime.Value=Today.Date,1,0),"DataSetName")

    For Year:

    =SUM(IIF(YEAR(Fields!IncidentDateTime.Value)=Today.Year,1,0),"DataSetName")

    For Week:

    =SUM(IIF(DATEPART(DateInterval.WeekOfYear,Fields!IncidentDateTime.Value)=
DATEPART(DateInterval.WeekOfYear,Today),1,0),"DataSetName")

    For Month:

    =SUM(IIF(MONTH(Fields!IncidentDateTime.Value)=Today.Month,1,0),"DataSetName")

    Let me know if this helps.