Need to limit Crystal Reports related table selection to only one record. I'm new to this app.

Posted on 2006-03-30
Last Modified: 2007-12-19
Ok, formulating this question may be difficult for me.

I have a mostly working report in which all data is keyed off of a field called JobN and one master table called OpenJob.  There are several related tables, all linked by JobN.  One of those tables, named JobStatus, has many rows with the same JobN.  

When I run the report, what I WANT is one row per JobN, with data in each column as I've defined, and the maximum value of Jobstatus.StatusCode for any given JobN from the linked JobStatus table.

What I GET is multiple rows for each JobN- one for every occurence of JobN in that linked JobStatus table.

In straight SQL, I might be able to say, SELECT .... max(JobStatus.StatusCode) but I apparently can't edit a SQL statement directly in CR.

Any ideas?  Does the above make sense at all? :-)

Question by:ProlabWest
    LVL 16

    Expert Comment

    Is JobStatus.StatusCode a number/int?  
    LVL 100

    Expert Comment

    Try this

    Add a group to the report to group on JobN
    Add a Sort on the JobStatus
    In the group footer put your field for the report.

    The detail section will be empty.

    LVL 16

    Accepted Solution

    Provided StatusCode is a number the following will work.

    Create a group on JobN.  Then Right Click on the StatusCode, Go to Insert>Summary.  
    A box will appear (Insert Summary), the first section should have your StatusCode column name in it.  If it doesnt, pull the drop down and find your column.  Then the second section is Calculate this summary, in the drop down choose Maximum.  The final section is Summary Location (where do you want the summary to appear), choose Group #1.

    So your report will look something like this:

    GH1 - JobN
    Detail - Suppress(to suppress, right click on the Section Name; Detail, and choose suppress)
    GF1 - JobN, Maximum([StatusCode])

    Hope this helps.

    Author Comment

    Great input all- thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    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. …
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    758 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

    11 Experts available now in Live!

    Get 1:1 Help Now