How to count the number of non empty cells in the column using formula field.

Hi,
1.How to count the number of non empty cells in the column using formula field.The column has string value in some cells.some cells are empty.

2.I have a column with Amount in the Table.I am showing the sum of the amount in the Group footer.
It has empty cells.If all the cells are empty.It is showing nothing in the group footer.
For that I added a formula field.But it is always showing $0.00 even if it has amount.Please let me know any corrections.

If Sum({table.field})>$0.00 then
Sum({table.field})
Else
$0.00

Thanks.
KavyaVSAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
If you aren't concerned about NULLs in other formulas or fields you can set the report to convert NULL to default.  That would solve the issue of all NULLs in the currency field

If you want a formula for the currency field use the formula suggested above by peter

If isnull({table.numberfield}) then 0 else {table.numberfield}

mlmcc
0
 
peter57rConnect With a Mentor Commented:
This all depends on what you mean by 'empty'.

1. There is a value 'Null' which occurs when a field has never been used (or if it has been set to Null) and if that is what you have in your empty fields then Count({table.field}) will produce the correct count of non-empty fields.

However, if some of your field might have "" - called a zero length string- then Count() will include these and you would need a formula field.
So you create a formula field like...

If isnull({table.stringfield}) or trim({table,stringfield}) ="" then 0 else 1

and then your insert a summary of this formula field but you must use SUM() not Count()

2.  I wouldn't expect nulls to have any effect on summing a number field, but you can do the same thing as for 1

If isnull({table.numberfield}) then 0 else {table.numberfield}

You can then sum this field.
0
 
mlmccConnect With a Mentor Commented:
For the SUM issue, is that the actual formula you are using as far as the use of $0.00?

If you right click the amount field and click INSERT --> SUMMARY .  
Put it in the group footer.
Does it show the correct value?

mlmcc
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
KavyaVSAuthor Commented:
Hi peter57r,
Empty means empty string .It's not null
Count({table.field}) giving the correct count of non-empty fields.

I tried this for summing the Amount.But it is giving the error. The error is A string is required here.Amount is Currency Field Type.

If Trim({table.stringfield})= "" then
0
Else
({table.stringfield})


Hi mlmcc,
I inserted the summary in the group footer. But it is not showing the 0.00 when all the cells are empty.Empty means empty string .It's not null.


Thanks
0
 
mlmccConnect With a Mentor Commented:
Try

If Trim({table.stringfield})= "" then
    0
Else
    1

For the currency types you should be able to simply sum them.  Is it really a currency(numeric) and not a string?

mlmcc
0
 
James0628Connect With a Mentor Commented:
If table.field is never null, then Count ({table.field}) will simply give you the number of records read by the report.  It will _not_ exclude records where table.field is an empty string.  That's a valid value that's counted like any other value, except null.

 Are you talking about 2 different fields here (one string and one number/currency), or 1 field?  From some of the things you've said, it seems like you could be talking about 1 field, but sometimes you're treating it like a string, and sometimes you're treating it like a number/currency.

 If it's 2 different fields, what, exactly, are you trying to do with each field?

 If it's 1 field, is it a string or number/currency?  If it's not a string, what does "empty" actually mean, since it can't be an "empty string".

 James
0
 
peter57rConnect With a Mentor Commented:
"Hi peter57r,
Empty means empty string .It's not null
Count({table.field}) giving the correct count of non-empty fields."

That is impossible.  If the fields are not null they will be counted so all you will get is the total number of records.
0
 
KavyaVSAuthor Commented:
You are right.The stored procedure returing null values and Crystal report showing empty string.

I am talking about two columns.One is string type and other one is currency Data Type.For both columns the stored procedure returning null values and Crystal report showing empty string.

For currency data type column I inserted the summary value in the group footer.But it is not showing $0.00 if all the cells in the column are empty.It is not showing anything.
For that currency column sum I need a formula field.

Thanks.
0
 
James0628Connect With a Mentor Commented:
If the field is sometimes null, I think a summary might still give you the correct total, but if it's null in every record, then I could see the "sum" as being null too, so it would be blank on the report.

 Did you try the #2 suggestion from Peter's first post?  If the numeric field is null in any or all of the records, that should handle it.

 James
0
 
KavyaVSAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.