Formula Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wayne Wengert - 30 Oct 2005 11:53 GMT

I have a field "Accept" that can be Null or contain either "Y" or "N". In my
test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
want a count of each case in the footer along with some descriptive text. I
tried the formula
=Count(Fields!Accept.Value="Y")

But it returns "4"? The same thing happens if I use

=Count(Fields!Accept.Value="N")

What is wrong in this formula?

Also, I really want the report to display something like: "Number of
Accepts: 2" but if I try to code the field as:

"Number of Accepts: " & =Count(Fields!Accept.Value="Y")

the formula displays as text?

TIA

Wayne

Reply to this Message

Liz Matyas - 30 Oct 2005 21:34 GMT

try to write:

= "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))

Good Luck

> I have a field "Accept" that can be Null or contain either "Y" or "N". In my
> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
[quoted text clipped - 18 lines]
>
> Wayne

Reply to this Message

Wayne Wengert - 31 Oct 2005 00:12 GMT

Liz;

Thank you very much. I never would have figured that one out. Seems like
making a point the hard way. I've ordered a couple of books that I hope will
explain the logic of creating expressions.

Wayne

> try to write:
>
[quoted text clipped - 26 lines]
>>
>> Wayne

Reply to this Message

Robert Bruckner [MSFT] - 31 Oct 2005 00:28 GMT

The Count RDL aggregate function works similar as the Count aggregate in
SQL: it will count all rows that are NOT NULL. The expresson
Fields!Accept.Value="Y" will return a boolean value (true/false) for all
rows, that's why the count results in the value 4.

Besides using =Sum(iif(...)) as shown by Liz, you could also use
=Count(iif(Fields!Accept.Value="Y", 1, Nothing))

-- Robert

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

> Liz;
>
[quoted text clipped - 36 lines]
>>>
>>> Wayne

Reply to this Message

Wayne Wengert - 31 Oct 2005 02:57 GMT

Robert;

Thanks for that additional information. It helps me understand the process.

Wayne

> The Count RDL aggregate function works similar as the Count aggregate in
> SQL: it will count all rows that are NOT NULL. The expresson
[quoted text clipped - 49 lines]
>>>>
>>>> Wayne

Reply to this Message