May 29, 2012 at 3:59 am
Hi all
I've got a matrix on a report that shows actual man hours by transaction date. This has a total column for which the expression is =Sum(Fields!ActualManHours.Value). This works fine.
I now want a Current Year to Date total. The Current Year is defined as everything after 01 Apr 2012. The date of the record is in a field called TransactionDate so I tried this:-
=sum(iif(Fields!TransactionDate.Value>="1 Apr 2012", Fields!ActualManHours.Value, 0))
But this is throwing an error.
I've done some investigation to try and get to the bottom of it. This works:-
=sum(iif(Fields!TransactionDate.Value>="1 Apr 2012", 1, 0))
which tells me that it's not the date comparison that's going wrong.
This also works:-
=sum(iif(Fields!TransactionDate.Value>="1 Apr 2012", Fields!ActualManHours.Value, Fields!ActualManHours.Value))
which tells me that I can sum the Actual Man Hours field in thsi way.
But as soon as I use a zero to elimiate values before 1 Apr I get errors. Can anyone suggest what's causing the errors? I'm mystified at this point.
May 29, 2012 at 10:43 am
Are you getting an actual error, or are you getting the wrong result? What is the error you are getting?
May 31, 2012 at 11:46 am
I don't get an actual error message. I'm just getting the #err# appearing in the matrix cells. Basically it's SSRS telling me "I can't work out the value for this" but it doesn't tell me why not.
May 31, 2012 at 12:24 pm
I don't know why the zero would give you a problem. But would this work instead?
=sum(iif(Fields!TransactionDate.Value>="1 Apr 2012", Fields!ActualManHours.Value, Fields!ActualManHours.Value - Fields!ActualManHours.Value))
Since Fields!ActualManHours.Value - Fields!ActualManHours.Value should give you a zero it might do.
June 1, 2012 at 8:20 am
Hmm, that might be worth a punt. I'm away from work until Wednesday but I'll definiely give this a try. Nice lateral thinking:cool:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply