How to filter by date to SUM a field?

Question

Hi

I am building a report in SSRS 2008 R2.

I have several fields that I need to use to filter data in the report.  Each of these fields are text fields that I have converted to datetime in my query.

Convert(datetime,Last_Date,101)

Convert(datetime,Date_Last_Doc_Created,101)

In the field expressions in the report fields, I need to total the number of users by each location, based on the Last_date they have signed into the system.

I've gotten the total of the field by using the following:

=SUM(IIF(Fields!Users_role.value="staff",1,0))

WIth the above, I get the total for all users whose role is equal to "staff" , but not just the ones limited by date range. ( I need them for the entire month to see who is signing in during that month and if they have created any documents)

I have tried using the converted date of 08/01/2012, '08/01/2012' for filters for both the groups and the tablix.  I'm not getting anywhere.  \

This report will be used monthly and hopefully give me aggregate information for each month as to which roles of users are signing in and also creating documents.

Help?

Kaycey

Answer 1

Sheesh.  After several days of fighting this and trying a myriad of solutions, I found one that worked.

=SUM(IIF(Fields!Last_Date.Value>="August 01, 2012",0,CINT(Fields!NumLogins.value)))

Hallelujah!

---

Kaycey

Answer 2

I thought you said you convert it to date type 101, which is supposed to be in the mm/dd/yyyy format. Then why would you compare them with August 01?

Edit: See sample:

declare @test varchar(50)
set @test = 'August 01, 2012'

select @test,convert(datetime,@test,101)

Output:

August 01, 2012 | 2012-08-01 00:00:00.000

---

Think out of the box