locked
Sort order problem - Crystal Report RRS feed

  • Question

  • Hi everyone, 

    I'm working on a project in VS 2010 Pro and using a crystal report to display some data that I have in my database. The report displays fine, however, I seem to have a small problem with the sort order in my report.

    For example in my report, it will display: A.1, A.10, A.11, A2, A3 in that order whereas I want it to display in this order: A.1, A.2, A.3, A.10, A.11, etc. Can you please help me. I can't seem to find an easy way to correct this. 

    I can tell you the steps I've taken in creating my group: 

    I've created one group > Group Expert - I then group by a specific field in my dataset > I then select "Options" which brings up "Change Group Options". In the "Common" tab I select "in specified order." and that bring up another "Specified Order" tab. Here I create more "Named Groups" so that for records beginning with A, I name it "A RECORDS", for records beginning with G, I name it "G RECORDS", and groups them under these headings in the report. However within these groups I get the result I described above. 

    How can I correct that and get the result I want? 

     

    Wednesday, December 15, 2010 7:42 AM

Answers

  • Thanks for your reply. 

    I have seen the image. Please note that the character in "Field2" (which I assume you mean to be the "Type" field that I described above ) does not necessarily follow the first letter of the code column. i.e. if the code is B.1, or even T.1, they could both be "A TYPE" records. I'm not 100% sure of what you mean by the image. 

    This problem here is really as a result of me trying to sort a string column numerically. Unless, I get a bright idea or more simpler solution, the only idea I have is to create two additional hidden fields, split the code into two parts for example, I would take "A.1", put "A" in a string column, put "1" in a numeric column, sort first by the string column, then by numeric column. I'm not sure if my logic is sound but I've got to try something.  

    Friday, December 17, 2010 8:58 AM

All replies

  • Hi,

    Create a formula filed in crystal report use the function LEN("Your Data base field name") in side. Then create a group for this function filed and keep in the top of your current group.

    have a nice day


    Please mark the replies as answers if they help. Kumar
    Wednesday, December 15, 2010 10:43 AM
  • Hi, thanks very much for your response.

    I've created a new formula field: LEN({Table.Field})

    Please explain what you meant by "keep in the top of your current group?" If you could help me through the steps it would be great. 

    Wednesday, December 15, 2010 1:28 PM
  • Right,

    First create a group based on the newly created formula field (Group  #1). Then create the second group based on your table field (that we used inside the formula field) (Group #2). Once you created the second group it will appear in your crystal report inside the first group. So the actual order will look like:

    Group Header #1 (Formula Field footer)

    Group Header #2

    Details Section

    Group Footer #2

    Group Footer #1 (Formula Field footer)

    please let me know if you need more explanation on this...

    thank you

     


    Please mark the replies as answers if they help. Kumar
    Wednesday, December 15, 2010 2:18 PM
  • Ok, I understand. Thanks very much. It works to a certain extent. This is the problem now...

    Using your structure above to explain the problem: My Group #2 is the one with my data.

    Now I have specified an order (i.e. specific order) to include all "A RECORDS", etc (I'm using another field in my database to sort by i.e. where there are A's in that field, I name is "A RECORDS", etc) I also have a number of "G RECORDS", "W RECORDS", "M RECORDS", "C RECORDS"

    Now when displaying all the records above, it displays correctly sorted using your solution above. But in one of my reports, I ONLY want to display "G RECORDS" and "A RECORDS" so in the Group Options for Group #2, I select "Discard all others" With this option, it seems to go back to the sorting problem I described initially.

    Hope I'm explaining myself properly. What could be the problem here? 

    Thursday, December 16, 2010 7:02 AM
  • I'm very new to CR so please bear with me..

    Is it possible to have a structure like this in a report?:

    Group Header #1

    Details Section

    Group Footer #1

    Group Header #2

    Details Section

    Group Footer #2

    Group Header #3

    Details Section

    Group Footer #3

    Thursday, December 16, 2010 7:14 AM
  • mmm..

    the structure you defined above is not possible in CR, because when ever you create a group it will place inside the existing group (you can manually change the order of appearance). If you can post some sample records I can find some solution to solve your issue. also i did not get "Discard All other" ???


    Please mark the replies as answers if they help. Kumar
    Thursday, December 16, 2010 8:17 AM
  • Ok, 

    Here are some typical records (I've separated the columns with tabs). Please note that I need to group on another hidden field called "Type" which differentiates each row below with Type A (A RECORDS), Type G (G RECORDS), Type W (W RECORDS), Type C (C RECORDS), Type M (M RECORDS)

    *These below are of type A and must therefore be grouped as "A RECORDS"

    CODE NAME DATE DESC

    A.1 NAME1 16.12.2010 NAME1_DESCRIPTION

    A.2 NAME2 10.12.2010 NAME2_DESCRIPTION

    A.10 NAME10 05.12.2010 NAME10_DESCRIPTION

    A.11 NAME11 05.12.2010 NAME11_DESCRIPTION

    *These below are of type G and must therefore be grouped as "G RECORDS"

    CODE NAME DATE DESC

    CODE-001 NAME1 16.12.2010 NAME1_DESCRIPTION

    CODE-002 NAME2 10.12.2010 NAME2_DESCRIPTION

    CODE-010 NAME10 05.12.2010 NAME10_DESCRIPTION

    CODE-011 NAME11 05.12.2010 NAME11_DESCRIPTION

    Regarding the "Discard others" this is where I find that: 

    In the field explorer, right click and select Group Expert > In the "Group by" section on the right, I have one selection i.e. Type. Select Options > In the "Common Tab" I select the same Type field and "in specified order" > A "Specified Order" tab & an "Others" tab pops up > I create two named groups i.e. G RECORDS and A RECORDS and on each one I edit the criteria to that specific type > In the "Others" tab, you will find that option to "Discard all others"

    Thursday, December 16, 2010 9:43 AM
  • Thanks for your sample records,

    see the image

    cr image

     

    based on your information i sorted like the above image, if this is what you required, then follow bellow steps

    1. Create a formula field based on your "Type " field insert the code Len(your type field)

    2. Create a group base on the formula field you created.

    3. Create your second group based on Your Type Field (not the formula field, your actual table "Type " field).

    4. Create your last group base on your code field.

    now the structure will be...

    Group Header #1 (Type Formula field)

    Group Header #2 (Type Field)

    Group Header #3 (Code field)

    Detail Section

    Group Footer #1 (Type Formula field)

    Group Footer #2 (Type Field)

    Group Footer #3 (Code field)

    hope this will help you

     


    Please mark the replies as answers if they help. Kumar
    • Edited by Kumar.Sivarajan Friday, December 17, 2010 8:54 AM updated image
    Friday, December 17, 2010 8:33 AM
  • Thanks for your reply. 

    I have seen the image. Please note that the character in "Field2" (which I assume you mean to be the "Type" field that I described above ) does not necessarily follow the first letter of the code column. i.e. if the code is B.1, or even T.1, they could both be "A TYPE" records. I'm not 100% sure of what you mean by the image. 

    This problem here is really as a result of me trying to sort a string column numerically. Unless, I get a bright idea or more simpler solution, the only idea I have is to create two additional hidden fields, split the code into two parts for example, I would take "A.1", put "A" in a string column, put "1" in a numeric column, sort first by the string column, then by numeric column. I'm not sure if my logic is sound but I've got to try something.  

    Friday, December 17, 2010 8:58 AM
  • Hi RJeremy,

    Thanks for your post.

    I hope this link can help you out, thanks.

    http://social.msdn.microsoft.com/Forums/en-US/vscrystalreports/thread/96b13aa6-5bc3-4b5e-b852-2a15a35e5f81

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, December 22, 2010 7:03 AM
    Moderator