Solved

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

Posted on 2013-01-15
10
2,569 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
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 100

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
 
LVL 100

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 34

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 34

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 100

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

25 Experts available now in Live!

Get 1:1 Help Now