Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Record Selection in Crystal Reports

Posted on 2013-12-19
7
Medium Priority
?
517 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 1336 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 101

Assisted Solution

by:mlmcc
mlmcc earned 1336 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 35

Assisted Solution

by:James0628
James0628 earned 664 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

721 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