Solved

Record Selection in Crystal Reports

Posted on 2013-12-19
7
509 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 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

22 Experts available now in Live!

Get 1:1 Help Now