Grouping on one field and sorting on another in Crystal Reports

Today I was stuck with this problem in Crystal Reports. I have the report grouped on some field but don’t want the records to be sorted on that field. Instead I wanted to have it sorted on another field.

The Problem

When you have a group field in the report, crystal report automatically adds the “group by” field in the sort list, and there is no way of removing it. If you click the Record Sort Expert button on toolbar and open the Record Sort dialog, you will see what I mean. You will find your “group by” field already there, and buttons to remove it disabled. Also you can’t lower its sort priority by specifying any other sort condition above it; so this is the first sort condition forcefully, whether you like it or not. I have no idea why Crystal report does this. But in my case I didn’t want the records to be sorted by this field.

The Solution

I couldn’t find anything inside Crystal Reports to solve my problem. Fortunately, since I was using datatable anyways, this is how I resolved my problem.

This was a two step approach:

  1. Some changes in the Crystal Report file.
  2. Some changes in the way we connect it to dataset/datatable.

Setting up things in Crystal Report file

1. Click the Group Expert button on toolbar. This will open the Group Expert dialog box.

Crystal Reports Group Expert Dialog

2. In the Group Expert dialog box, click the Options… button. This will open Change Group Options dialog box.

Crystal Reports Change Group Options Dialog  Crystal Reports Change Group Options Dialog 2

3. Here in the second dropdown, select “in original order” option. Click OK button to close the dialog box.

4. Click OK button in Group Expert dialog box to save changes and close it.

5. Save the report file and close it.

Changes to How we use the DataTable

I modified my VB.NET code to sort the datatable there instead of letting Crystal Reports do it for me. So we pass it a pre-sorted datatable according to our needs.

This is the scaled down version of what I did:

'create datatable
Dim myDataTable As New DataTable
 
'TODO: fill datatable with appropriate data here
 
'set the sort
myDataTable.DefaultView.Sort = "City, CustomerName"
 
'create report document
Dim myReport As New ReportDocument
myReport.Load("C:\ReportTemplates\CustomerReport.rpt")
 
'set datasource
myReport.SetDataSource(myDataTable.DefaultView.ToTable)
 
'now do whatever you want to do with this report.
'e.g. I exported it to disk file, but you may want to show on screen etc.
myReport.ExportToDisk(ExportFormatType.PortableDocFormat, "C:\Reports\CustomerReport.pdf")

Your requirements may not be exactly same as mine. But this is the general approach to follow. (Pay more attention the comments than the code itself)

How This Works

What we basically did is not let Crystal Reports apply its brain for sorting the data. Instead we passed it the sorted data itself. The code is self-explanatory.

Advertisements

24 Responses to “Grouping on one field and sorting on another in Crystal Reports”

  1. oppodereibfoB Says:

    good article explaining the concept. thanks

  2. Sandhya Says:

    Thank you for posting this article. It helped me to solve my issue in crystal report. Keep up the good work.

    • David Says:

      Another option is group by the sorting field prior to your group to force a sort. Then just surpress the new group and footer.

      • vinoda Says:

        Hi,
        I need to group on one field and sorting on other field.i cannot do grouping in sql since i am getting data from 2 databases.i tried o insert one more group on top(which i have to sort bY).But iam getting so many values grouping on new group. even though i suppress the new group.can you help me to sort by new group and group by old one still.
        Thanks.

  3. Sagar Says:

    Thank you for posting this article. It helped me to solve my issue in crystal report.

  4. Jason Says:

    This unfortunately won’t work in situations where you have multiple drill down levels (groups) each with their own group name yet a different value you want to have them sorted by. In this instance, I tend to create a formula that is the concatenated value of both the sort field followed by the group field IE [Sort Field Name] & “-” & [Group Field Name]. I would then base the group on my formula. When the group is added, delete the automatically generated group field name and replace it with the value of interest (in this case field [Group Field Name]). While this method creates the overhead of an additional formula field to your report, you will need to use this method if you have several custom sort group headers that do not follow the original sort order of your data.

  5. Bruce Ma Says:

    Hi Jason,

    My problem is same with yours. I am not very clear about how you fix it. Could you please give more detailed procedure?

    I appreciate your help.

    Bruce Ma

  6. Jason Says:

    Bruce,
    It’s kind of unique for each situation. What are you looking to do specifically?

  7. vinoda Says:

    Hi Jason,

    I need to group on one field and sorting on other field.i cannot do grouping in sql since i am getting data from 2 databases.i tried o insert one more group on top(which i have to sort bY).But iam getting so many values grouping on new group. even though i suppress the new group.can you help me to sort by new group and group by old one still.
    Thanks.

  8. vinoda Says:

    I need to group on one field and sorting on other field.i cannot do grouping in sql since i am getting data from 2 databases.i tried o insert one more group on top(which i have to sort bY).But iam getting so many values grouping on new group. even though i suppress the new group.can you help me to sort by new group and group by old one still.
    Thanks.

    • pradeep1210 Says:

      A dataset or datatable is a disconnected entity. It is not bound to any database. You can fill it with data from one database or multiple databases or no database at all. Once you have the data from both databases in your datatable, you can proceed as you would deal with any datatable from single source. You can then use the method I proposed in this article to sort data in your report.
      Here is one link that discusses how to get data from two databases into your datatable:
      http://social.msdn.microsoft.com/Forums/en/Vsexpressvb/thread/580f0f30-c821-4f03-8415-4c46a1fa5fb1

      • vinoda Says:

        Hi Pradeep,

        Thanks For your response.I need to group by one filed (for ex:Department)which is coming from database directly.and i need to sort y the total salary amount(that goes to each department) for each group in descending order instead of group1 s order.so if i put salary as group on top of department i am ettig so many rows based on salary and its not really grouiping by department.So i need to first group by department.After getting all the filed i want for each department then i need to sort by total salary amount highest as first row.Can you please help me to do this.
        All i need to do is group by column from database and sort by total amount for each group forex:formula filed.

        Please help asayc.Its really urgent.Thanks in advance.

  9. selvaraj Says:

    I need to group on one field and sorting on other field.i cannot do grouping in sql since i am getting data from 2 databases.i tried o insert one more group on top(which i have to sort bY).But iam getting so many values grouping on new group. even though i suppress the new group.can you help me to sort by new group and group by old one still.AND i need custom grouping details
    lpease help me sir
    Thanks.

  10. Amit Tandon Says:

    Very nice article!!! you made it very simple and understandable, thanks for sharing with us. Check out this articles too its also explained very well about Group name field in crystal report…..
    http://www.mindstick.com/Articles/a2007ba0-3c74-49b7-9152-3373c18f9e1f/?Group%20Name%20Field%20in%20Crystal%20Report

    http://www.codeproject.com/Questions/155732/I-need-to-use-Group-name-Field-In-my-Crystal-repor

  11. William Says:

    Hello,

    Regarding to groupby field A and order the groups by field B i do the follwing via the groupexpert:

    First i make a group for field A
    Second i make a group for field B
    Third i move field B on top
    Fouth i hide the header and footer for field B which is Group#1 (Top Level) in the section expert

    Regards William

  12. Hifni Says:

    This helped me! 🙂 Thank you.

  13. James Says:

    Just confirming multiple grouping works perfectly as William mentioned above. This was a common requirement for us and it also allows for more flexibility when changes are required. Update the rpt and redeploy… good stuff.

  14. Muhammad Junaid Says:

    Excellent, great idea. This was exactly what I was trying to go 🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: