Converting Access report to Reporting Services

Hi,

I am trying to convert an Access report where I use expressions to calculate the number of males/females for example.

I have used the following expression, but it always returns an incorrect figure:

=Sum(IIf(Fields!gender.Value ="Female", 1, 0))

When I check the query however it has 46 rows = to 'Female', but when I previes the report it always comes out as 24.

What's causing this?

Thanks

Hi,

From your description, it seems the expression doesn't return the right number of those 'Female' field, right?

What I can see is to use a TRIM function to remove all the leading and trailing while-spaces characters from the value in each filed. Sometimes theses leading and trailing white-spaces characters may cause the expression "IIf(Fields!gender.Value = "Female", 1, 0)" returns 0. You may use TRIM to have a try, see the following code:

=Sum(IIf(TRIM(Fields!gender.Value) = "Female", 1, 0))

Thanks.

|||

Thanks, that seems to have worked.
I wasn't aware of the leading and trailing white-spaces.