Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

Record Selection in Crystal Reports

I am having issues with getting the correct parameters to print in Crystal Reports.  Here is some sample data.

Table: Customer

Number        Name
123               Pulleys
456               Bobbys
789               Tommys

Table: ExtraCustomerData

Number     Keyfield     AlphaData
123            HOR001          W
123            MAI001           N
123            BIN001            3
456            HOR001          W
456            MAI001           Y
456            BIN001           4
789            HOR001         H
789            MAI001           N
789            BIN001           6

These two table are connected by the "Number" field.

I have it setup in record selection that Keyfield='HOR001'

This work fine as it is only printing the  information from the HOR001 line and not the other 2.  But now I want to further restrict record selection by only printing the HOR001 record if MAI001='N'.  

How do I do this?

Thank you.
3 Solutions
One way is to pull both records in

{Keyfield} IN  ['HOR001', 'MAI001']

In the report you would check if there is one with MAI001 and N then print it in the group footer.

Another way would be to do it through a SQL Command with a subselect.

You need the MAI001 records to determine whether a record is printed or not so you will need to start by changing your record selection to Keyfield='HOR001' or Keyfield='MAI001'.
Then you need to sort your records by Keyfield descending so you get your MAI record before your HOR record. Then create a group by Number and hide the group header as well as the detail lines. In the group footer under suppress enter the formula  MAI001<>'N'.
This will only display HOR001 records where MAI001 = 'N'
MrWeenAuthor Commented:
I'm getting an error when i try and use MAI001<>'N' in the footer suppression.  Don't I need some formula like

{AdmFormData.Keyfield}='MAI001' AND {AdmFormData.AlphaData}<>'N'

(This formula does not work either)
You can't do it through the Crystal record selection alone.

You need to use a command like

SELECT list of fields
FROM  YourTable xx
WHERE   {AdmFormData.Number} IN (SELECT Keyfield FROM YourTable Where {AdmFormData.Keyfield}='MAI001' AND {AdmFormData.AlphaData} = 'N')
AND xx.KeyField = 'HOR001'

This probably wouldn't be the best solution, but another option might be to include the ExtraCustomerData table twice.  If you add the same table again, CR will add a "_1" (or something like that) to the end of the table name, so that you can reference each table separately in the report.  You'd use Number to link the two copies of ExtraCustomerData.

 Then your record selection formula would be something like:

{ExtraCustomerData.Keyfield} = 'HOR001' and
{ExtraCustomerData_1.Keyfield} = 'MAI001' and
{ExtraCustomerData_1.AlphaData} = 'N'

 I haven't tried it, but I _think_ it would work.  Like I said, it's probably not the best solution, but if it works, it's easy to do.  If you have a lot of data, the performance may not be very good.  I guess that may also depend on how "clever" CR and the db are (whether they optimize the query at all).


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now