Why so many records?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=764
Printed Date: 04 May 2025 at 8:36pm
Topic: Why so many records?
Posted By: penguin57
Subject: Why so many records?
Date Posted: 30 May 2007 at 7:26am
|
I have a report that has several fields including a contact telephone. The database could have several records for different types of contact numbers but I'm only interested in the ones with the code "TEL" or "1MC" and only want to show these. so I used the selection expert but the problem is I don't want it excluding premises where there is no contact number (null) just to leave the contact field blank. I am reasonably new to Crystal and desperate to figure this out, please help!
|
Replies:
Posted By: RobV
Date Posted: 08 Jun 2007 at 2:35am
|
Hi there,
The answer depends on the code field is blank or is null.
if it is null then create the following formula....
if isnull({Customer.Code}) then 'Y'
else if {Customer.Code} = 'TEL' then 'Y' else if {Customer.Code} = '1MC' then 'Y'
else 'N' if the field is not null but contains no visible data then create the following formula.....
if {Customer.Code} = '' then 'Y'
else if {Customer.Code} = 'TEL' then 'Y' else if {Customer.Code} = '1MC' then 'Y'
else 'N' Now place the formula in your report and right click it. Click on Select Expert and select Is Equal to and select Y.
Not sure if you want to select the code field or the number field. The theory is the same either way.
Hope this helps.
Take it easy.
RobV
|
Posted By: hilfy
Date Posted: 08 Jun 2007 at 2:08pm
|
You don't have to place the formula on the report to be able to use it in the Select Expert.
Another way to do this would be to have the formula just return true or false. That way you don't have to compare it to anything in the select expert and the whole thing takes less processing. The formula would look like this:
isNull({Customer.Code}) or
{Customer.Code} = 'TEL' or
{Customer.Code} = '1MC'
This will return True if any of the conditions is met - you don't need "= true" or an if...then...else statement for it to work.
In the Select Expert you select the formula and "Is True".
Be aware that when you use a formula in the Select Expert, some or all of your report filtering will happen on the workstation instead of it occuring in the SQL that's run in the database (if you're using MS SQL Server, Oracle or another client/server database.) If you have a lot of data, this will take longer to run because all of the records will be pulled to the workstation and only the ones that meet this condition will actually appear on the report.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |