Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Custom sort order for group

    I am grouping my report by the value in a column named "RollupLevel".
    This column contains one of four different values: "Bank", "Center", "District", or "Region". However, I want the group level sorted in this order: "Bank", "Region", "District", "Level". What is the best method to accomplish this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    May 2007
    Posts
    1
    In Oracle you can do it this way:
    DECODE(RollupLevel, 'Bank', 1, 'Region', 2 , 'District', 3, 'Level',4, 5) ASC

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Adding an additional column will make that column appear in the report groupings on the left side of the Crystal Enterprise screen. I want the users to see Centers, Districts, Regions, Bank. Not 1, 2, 3, 4.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2007
    Posts
    348
    I don't understand the question. The way I read it, you are grouping by an attribute then sorting on the same attribute. Wouldn't that give you no sort within the group at all?

    Still, custom sorting is requires that you do it like celera suggests. Sort on a field that is in the order you want, like a table with 1 Bank, 2 Region, 3 District, 4 Level, then just don't show the number field.

    OR

    Use yet another group and within the group expert select "specified order" and set your order out thatta way.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm grouping by rollup_level, which Crystal defaults to soring alphabetically.
    The value in rollup_level appear in the report tree:
    Code:
    - ReportName
       + BANK
       + CENTER
       + DISTRICT
       + REGION
    I want them to appear in order of their business level:
    Code:
    - ReportName
       + BANK
       + REGION
       + DISTRICT
       + CENTER
    I can create a pseudo-column for business level with values 1 through 4 and group by that instead, but then I get this in the report tree:
    Code:
    - ReportName
       + 1.0
       + 2.0
       + 3.0
       + 4.0
    ...which is not what I want.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    What happens when you put the rollup_level column that contains those values in your group header?

    Or do it the second way I suggested. For only four values that should be easy enough.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The rollup_level column is already in my group header.

    And again, if I sort by another group then that group appears in the report tree.
    I want rollup_level and rollup_level alone to appear in the report tree, but I want rollup_level sorted by another logic.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2007
    Posts
    348
    we are clearly not having a meeting of the minds on some level.

    Here is how I understand your report to be laid out

    HEADER
    Group - Rollup_level
    details - sorted on some other value

    The trick is that you want your group to not go in alphabetical order. If I'm following you so far then read on, if not, well, save yourself the effort.

    Then when you go into the group expert, you should (btw, I'm working in Crystal 10) be able to select your group and hit options. There you should be able to select Ascending, Descending and some more options. One of those options is "Specified Order". So switch your order to whatever you want. No extra groups, no extra fields, just what you asked for.

    If your not in a version that allows all that and you have some arranagement of:
    1 - Bank
    2 - Region
    and so on, and the hypen in my example above represent that they are not in the same field. Then you should be able to sort your groups on the number but not display the number on the final report using any one of about three methods. Now, from your last post I get the impression you will be giving this to someone as a Crystal file not a PDF or on Paper so you are worried about how it will look in Crystal. I don't have anyone else to do that with so it's alittle outside my experience but, you can make a formula field where 1 - Bank and so on, like you can do with dates and days of the week. It's a bit convoluted but it is doable.

    EDIT

    I made a little sample of the first method I described, everything you need should be attached
    Attached Files Attached Files
    Last edited by starkmann; 05-18-07 at 10:13. Reason: adding example

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks for you suggestions, starkmann. I will try them out when I am at the client site again next week.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2007
    Posts
    348
    did it workout for you?

  11. #11
    Join Date
    Sep 2006
    Location
    Singapore
    Posts
    10
    I had the same problem, then i tried your suggestion starkmann using specified order.
    And it works.
    Thanks
    An apple a day keeps the doctor away..
    If the doctor is handsome, throw the apple away..

  12. #12
    Join Date
    Feb 2007
    Posts
    348
    glad to help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •