Counting rows conditionally by date within a group in SSRS?

0
votes

I'm trying to to count the number of rows within a group that meet a certain criteria in SSRS.

In my Tablix; i have a category group. Within this category group there is a Create_date column. I want to count the number of rows within the group where the Create_date is within the current month.

=SUM(IIF(ReportItems!Inital_Date.Value > 
DATEADD(mm, DATEDIFF(mm,0, Today()), 0) 
AND ReportItems!Inital_Date.Value > 
DATEADD(dd,-1,DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0, Today()), 0)))
, 1, 0))

I've also tried to specify Fields! instead of ReportItems!; I think i need to properly specify the scope of the group perhaps?

This gives me an error: Aggregate functions can be used only on report items contained in page headers and footers.

I've included an image of my tablix layout as well.

Tablix Layout

2
reporting-services
Initial_Date is coming from a dataset? Or that's the name of a textbox? – Steve-o169
It is a field in the dataset – DDulla
And what error do you get when you use Fields!Initial_Date.Value? – Steve-o169

2 Answers

0
votes

I can;t test this at the momeent but if you simply want to test against the current month then you could simplify the expression as 

=SUM(IIF(MONTH(Fields!Initial_Date.Value) = MONTH(Today()),1,0))

The scope will be the same scope as you put the expression, so if it's within a row group, the SUM will operate against all the rows with in that rowgroup, this is the default behavior.

0
votes

in your expression amend to show:

=IIF(MONTH(Fields!Initial_Date.Value) = MONTH(Today()) AND (YEAR(Fields!Initial_Date.Value) = YEAR(Today())) , 1 ,0)