Sort question

Posted on 2012-08-29
Last Modified: 2012-08-30
I have a group that is comprised of a concatenation of two fields. It's setup to sort ascending. One  record is special and needs to be listed first. How do I accomplish this ? I can single out the record by type.  

Barry thomson is special and needs to be listed first.

Displayname & Rate (Sort Ascending)

Andrew Jackson 500
Andrew Jackson 600
Barry Thomson 700  (Is of type special)
Lisa Smith  200
Question by:mowit
    LVL 12

    Expert Comment

    Try using this...

    order by
    when displayname like 'Barry Thomson' then 1
    else 2
    Displayname & Rate asc
    LVL 77

    Expert Comment

    In the group expert you can can change the sequence to 'In selected Order' .  You will then get a dialog where you can  place your required value first .  You then have to say that you want to leave Others in their own groups.

    Author Comment

    Peter, I thought about doing that but then it will display another group level. I just wanted the record to be listed first.

    Jared_s - Is that SQL ? I'm looking for a solution within Crystal.
    LVL 12

    Expert Comment

    Sorry, I write most of my Crystal reports by adding a user command... force of habit.
    LVL 26

    Expert Comment

    by:Kurt Reinhardt
    If you don't want to use Specified Order for grouping, then you'll have to do it at the detail level with a formula field.  Here's one example:

      DisplayName = 'Barry Thompson'

    Instead of sorting on the actual DisplayName field (which you can still display on the report), you'll sort on the formula field.  This will ensure Barry Thompson is always listed first and all others will still be sorted alphanumerically after him.
    LVL 100

    Accepted Solution

    In Crystal add a formula

    Local StringVar SortOrder := 2;
    If {DisplayNameield} = "Barry Thomson" then
        SortOrder := 1;

    SortOrder & " " & {DisplayNameField} & {RateField}

    Use that for the group
    You can change what is displayed as the group name
    Right click the group header in the left margin
    Click Change Group
    On the second tab you can specify a field to display
    Display just the DisplayNameField or your formula


    Author Closing Comment

    mlmcc: you never disappoint. :)
    LVL 26

    Expert Comment

    by:Kurt Reinhardt
    Actually, Peter's original suggestion would not require an additional layer of grouping. You could have easily accomplished your goal with his suggestion.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    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…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    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…

    737 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

    20 Experts available now in Live!

    Get 1:1 Help Now