Solved

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

Posted on 2013-01-15
10
2,514 Views
Last Modified: 2013-01-25
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.
0
Comment
Question by:KavyaVS
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 143 total points
Comment Utility
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 215 total points
Comment Utility
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
 

Author Comment

by:KavyaVS
Comment Utility
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 215 total points
Comment Utility
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
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 142 total points
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 143 total points
Comment Utility
"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
 

Author Comment

by:KavyaVS
Comment Utility
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
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 142 total points
Comment Utility
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 215 total points
Comment Utility
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
 

Author Closing Comment

by:KavyaVS
Comment Utility
Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now