[Solved] Summing specified records in a report

[Nocton]

I have a report where I wish to count only specified records, e.g. I wish to count all records printed which have a field set to True or Yes rather than False or No. How can I do this? I have tried various functions, but can't get anything to count over the whole record set.

Regards,
Nocton

[Villeroy]

A working report engine would separate the True records from the False records and aggregate them separately.

In a Calc report this would be a matter of either 2 additional COUNTIF formulas or a pivot table with the booleans as column field.

[chrisb]

Hello Nocton

I assume your field is a text field & that you use Report Builder.

The 'ISTEXT' function does not work so if this text field contains NULL values I suggest using the COALESCE function within your query to replace them with empty strings.

Once every field contains a value it's easy to count your records.
You will need to create a User-Defined Function.
I know that you have the knowledge to do this so I will not waste time spelling it out just remember to create it in the relevant group.

Ignore the apostrophes contained in the following lines.

I have named the formula as 'Count_Yes_True'.
I have named the fieldname as 'Flag'.

FORMULA PROPERTIES:-
Name: 'Count_Yes_True'
Formula: '[Count_Yes_True]+OR([Flag]="Yes";[Flag]="True")'
Initial value: 'IF(OR([Flag]="Yes";[Flag]="True");1;0)'

An interesting fact is that when using only the 'OR' function i.e. 'OR([Flag]="Yes";[Flag]="True")' to set the 'Initial value' the formula would not reflect a TRUE initial value.
I found it necessary to enclose the 'OR' function within the 'IF' function.

[Nocton]

Thank you, chrisb, that works perfectly. I had a work around using a couple of queries, but this is more elegant and part of the report.

Regards, Nocton