Print Page | Close Window

filter

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6623
Printed Date: 28 Apr 2024 at 9:26pm


Topic: filter
Posted By: psrs0810
Subject: filter
Date Posted: 08 Jun 2009 at 12:39pm
How do you filter out (remove) all data that would normally come in with 0?
 
Thanks


Replies:
Posted By: hilfy
Date Posted: 08 Jun 2009 at 1:30pm
Is is 0 or is it null?
 
In the Select Expert, select the field, "Is Not Equal To", and enter 0.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: psrs0810
Date Posted: 08 Jun 2009 at 1:35pm
How do I do that when there are 4 columns of information?
i.e. Actual Charges, Budget Charges, Actual Volume and Budget Volume
Also, some are posted with 0.00 while others are just blank
 
 
Thanks

Posted By: DBlank
Date Posted: 08 Jun 2009 at 2:49pm
Sorry my original post was wrong...
Use or statements:
table.Actual Charges>0
or
table.Budget Charges>0
or
table.Actual Volume>0
or
table.Budget Volume>0
 
if any value is >0 it will include the record, if all are either null or =0 it will exlcude the record.

Posted By: psrs0810
Date Posted: 09 Jun 2009 at 5:17am
where in crystal would put that statement?  That is the other part that I am confused about.
 
Thanks

Posted By: hilfy
Date Posted: 09 Jun 2009 at 7:20am

You can edit the formula in the Select Expert.  I would set up all four of the conditions there, edit the formula, and replace the "and" put there by the Select Expert with "or".  Note:  if you have set up other conditions in the Select Expert, you'll need to put parentheses around this set of OR conditions, something like this:

({table.Actual Charges}>0
or
{table.Budget Charges}>0
or
{table.Actual Volume}>0
or
{table.Budget Volume}>0)
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: psrs0810
Date Posted: 09 Jun 2009 at 7:46am

Sorry, but I am new to Crystal.

Under Reports, is this formula going Selection Formulas, Record or group?
And what is the difference?
 
Thanks

Posted By: DBlank
Date Posted: 09 Jun 2009 at 7:56am

Record. This is used to evaluate conditions that are per record (even if it is more than one criteria for that record like your statement is).

Group is used to select data based on a group condition like a SUM of a field a a group level or Count of a records in a group.

Posted By: psrs0810
Date Posted: 10 Jun 2009 at 9:50am

What about filtering out a null or blank value?  There are some values that are less then 0.

 

Thanks

Posted By: DBlank
Date Posted: 10 Jun 2009 at 10:10am
I guess this begs the question are you trying to exclude rows based on the sum of these 4 fields meeting a condition or are you trying to exclude records where at least 1 of the fields is >0 (which is how it is now).
The select statement is evaluating the record to see if each full condition = True (include record) or = False (exclude record).
By putting the parenth around all 4 parts of the "or statement" (above) it evaluates all of this as 1 statement so if any one of the part=true then the whole condition is TRUE and the record is included in the report.
 
The ">0 " evaluation would be FALSE (exclude) if the field is NULL or a field with a negative value. Therefore if all 4 fields were any of the following: null, 0 or negative, then the row is excluded.
if 3 are negative and 1 field has any positive number it is included.
Make sense? 

Posted By: psrs0810
Date Posted: 10 Jun 2009 at 10:16am

I need the all of the information if any one of the four fields have a value.  If all four fields are blank or even 0.00, I do not need them.

I am trying to reduce the amount of information, because it starts out with over 25,000 rows and I am trying to reduce it down to a managable 9,000.

Thanks

Posted By: psrs0810
Date Posted: 11 Jun 2009 at 7:19am

I am using this formula, because ther are some values that a below 0:

{CIDS_2009_CHRG.May}>0

or

{CIDS_2009_CHRG.May}<0

or

{CIDS_2009_BUDGET_CHRG.May}>0

or

{CIDS_2009_BUDGET_CHRG.May}<0

or

{CIDS_2009_VOL.May}>0

or

{CIDS_2009_VOL.May}<0

or

{CIDS_2009_BUDGET_VOL.May}>0

or

{CIDS_2009_BUDGET_VOL.May}<0

BUT it is still eliminating rows of information that has values.  Any ideas?

Posted By: hilfy
Date Posted: 11 Jun 2009 at 7:46am

Are any of the values null?  Comparison against a null value does not return true or false, it returns null and no more comparisons are made.  This is because the database processes "or" statements until it gets a non-false result and null is not false.  I know, it's confusing!

What type of database are you using?  If it's Oracle, I would create a SQL Expression for each field that looks like this:

 

nvl({CIDS_2009_BUDGET_VOL.May}, 0)

 

This will return a 0 if the value is null.  There's probably a way to do something similar in SQL Server and other databases (unless you're using something like Excel as a data source.)

 

The Selection formula would then look like this:

 

({%CHRG} <> 0
or
{%BUDGET_CHRG} <> 0
or
{%VOL} <> 0
or
{%BUDGET_VOL} <> 0)

 

-Dell

 



-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: psrs0810
Date Posted: 11 Jun 2009 at 8:19am
Is null just blank or no value?
 
Also, when I try to put in the % in the formula, it gives me "field name is not known" or " ) is missing"
 
the database we use is a Siemens product 

Posted By: hilfy
Date Posted: 11 Jun 2009 at 10:51am
You have to create the SQL Expressions in Crystal in order to use them.

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: psrs0810
Date Posted: 11 Jun 2009 at 1:20pm
Sorry, I am not fluent in SQL


Print Page | Close Window