Solved

Record Selection in Crystal Reports

Posted on 2013-12-19
7
507 Views
Last Modified: 2014-02-27
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.
0
Comment
Question by:MrWeen
7 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 334 total points
ID: 39730551
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.

mlmcc
0
 
LVL 3

Expert Comment

by:13Shadow
ID: 39730563
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'
0
 

Author Comment

by:MrWeen
ID: 39730618
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)
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 334 total points
ID: 39730729
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'

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 166 total points
ID: 39733481
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).

 James
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

15 Experts available now in Live!

Get 1:1 Help Now