SSRS how to count with multiple conditions?

Question

I'm new to expressions in SSRS, hope you can help!

I need to create the following:

"Count [issueID] where [closed] is not null and [due_date] is in the past."

I can do the first bit, but don't know the syntax to add the "...and [due_date] is in the past" in the textbox expression.

Cheers

Lins

Answer 1

It would be easier to modify your dataset source to return this flag in your data. If you are not going to modify your data then I guess you could...

1. Add a Calculated Field to your Data Set MyCalc.

2. Set the expression for the Calculated Field

   =IIF(!IsNothing(Fields!Closed.Value) && Fields!DueDate.Value < DateTime.Now,1,0)

3. Now you can add an expression similar to

   =IIF(SUM(Fields!MyCal.Value) > 10 , "+10","not + 10")

Answer 2

You can try this:

select 
count(case when [closed] is not null then [issueID] else 0 end) as 'TotalCount'
from [Your_Table]
where [due_date] <= getdate()