Solved

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

Posted on 2013-01-15
10
2,771 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 143 total points
ID: 38779306
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 101

Assisted Solution

by:mlmcc
mlmcc earned 215 total points
ID: 38779348
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
ID: 38779798
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
Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 215 total points
ID: 38780737
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 35

Assisted Solution

by:James0628
James0628 earned 142 total points
ID: 38781795
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 143 total points
ID: 38782054
"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
ID: 38783066
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 35

Assisted Solution

by:James0628
James0628 earned 142 total points
ID: 38784166
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 101

Accepted Solution

by:
mlmcc earned 215 total points
ID: 38785548
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
ID: 38818838
Thanks
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

626 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