Print Page | Close Window

Filtering Data with a Formula

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=7090
Printed Date: 29 Apr 2024 at 9:05am


Topic: Filtering Data with a Formula
Posted By: Chels2983
Subject: Filtering Data with a Formula
Date Posted: 20 Jul 2009 at 9:40am
I am trying to pull data from a field that contains both sample data and QC data.  I would like to have the sample data appear in the report and the QC data appear in the subreport.  I thought that I could use Select Expert to filter out the QC data for the main report, and then Select Expert to filter out the sample data for the subreport.  However, when using Select Expert for the main report, I receive an error that says something with regards to there being too much data or the formula being too complicated.  Thus, would it be better to create a formula myself?  And if so, I'm not exactly sure how to go about creating that formula. 
 
 
If ({FLATDATA_1.ACOD} = "TOC_") or ({FLATDATA_1.ACOD} = "%ASH_C") or, etc. 
then {FLATDATA_1.ACOD}
else . . . omit
 
Any help would be greatly appreciated.  


Replies:
Posted By: DBlank
Date Posted: 20 Jul 2009 at 9:59am
In this case for your select statement you do not need an if-then. Just use the TRUE condition for the items you want to be included:{FLATDATA_1.ACOD}} in ["TOC_","%ASH_C", "Next item here"] 

Posted By: Chels2983
Date Posted: 20 Jul 2009 at 11:57am
I tried this formula earlier.  However, when I insert it into my report, the field gives me a "True" or False" value when I need it to give me the actual data/field value.  

Posted By: DBlank
Date Posted: 20 Jul 2009 at 12:13pm
Correct, it is a evaluating each row to see if it meets the condition (TRUE) or not (FALSE).
In your original post you indicated that you wanted a way to filter data using the select expert. I wrote the statement to be used in the select expert, not as a formula field.
I guess you are trying to use a formula field and then use that in the select statement which you can do but it really is adding in an extra step.
If you want to go down that rode then create a formula field as 'DataSelect' (or whatever you want):
if {FLATDATA_1.ACOD}} in ["TOC_","%ASH_C", "Next item here"]  then "Sample Data" Else "QC Data"
I may have uinverted the data types above so just switch the order if you need to.
In your select statement you can then use
mailto:%7b@DataSelect - {@DataSelect } = "Sample Data"
However this is the same thing as just making your select statement as:
{FLATDATA_1.ACOD}} in ["TOC_","%ASH_C", "Next item here"] 
 

Posted By: Chels2983
Date Posted: 20 Jul 2009 at 1:22pm
I think I understand now.  When I was using the Select Expert, I was trying to create a formula that would not include certain values within the field.; I continued to receive an error message stating that the "Expression is too complex."  However, the Select Expert works well with regards to creating a formula to include certain values.
 
Thank you so much for all of your help and suggestions.  It can be very frustrating trying to figure out what formulas to use and how to use them within Crystal.  I am so grateful for any help that you all have provided/continue to provide.   
 
 

Posted By: DBlank
Date Posted: 20 Jul 2009 at 1:31pm
No problem.
With the select expert you are writing it to evaluate each row and then return a True or False for that row. 
If it =True it is included if it is False it is excluded.
 
The formula Fields in the Field Explorer can also be written to return true or false but is used for a lot more than that (like converting your data to some other more user friendly or readable format).
 


Print Page | Close Window