Tuesday, September 4, 2012

How to change sort order of crystal report by using code?



Crystal report -

Crystal report is a tool which provides facility to display data in reporting format. It also support functionality like sorting of data based on sort field. This sorting can be done in design time and run-time also.


Sorting data using sort field is easy while design time but if we want to sort data in run-time then some time it can generate error. The most common error for run-time sorting is-


"The sorting already exists"


In this article, I will explain -
How to change sort order of crystal report by using code?

First I will tell what the reason for this error is and how we can solve this error.


Reason-


This error comes when we are trying for setting sort field for crystal report using code and that particular sort-field is already added as sort order field for crystal report in design time.


Example-


Suppose we want set "Name" as sort order field and this field ("Name") is already present as sort order in design time then it will generate error.


Workaround-


Simple workaround for this problem is that swapping of sort order fields.

Suppose you have added two fields (firstName and lastName) as sort fields for report in design time
And present sort field is firstName now if you want to changes sort field to lastName then will generate error. So here we have a trick – Take any column from table which is not present in sort field which you have added in design time.

Design time –

Sort field (0) - firstName
Sort field (1)-lastName

In code set like this-

Sort field (0) - firstName
Sort field (1) - address (any field of table which is not in sort field)

Now again swap between firstName and lastName. This time it will successful because now currently we don’t have lastName as sort field in report. 

Here I am going to explain how we can solve this problem? Here is sample code to solve this problem-

String SortField="yourSortField";

bool fieldPresent = false;

 //CHECKING SORT FIELD PRESENT IN THE REPORT IF YES THEN SWAP THE POSITION //TO ZERO

    for (int i = 0; i <= Report100.DataDefinition.SortFields.Count - 1; i++)
  {
       if (Report100.DataDefinition.SortFields[i].Field.FormulaName == SortField)
        {
            fieldPresent = true;
            FieldDefinition tempDef = New FieldDefinition;
            FieldDefinition currentDef =New FieldDefinition;

            //HOLDING CURRENT [0'TH] SORT FIELD

            currentDef = Report100.DataDefinition.SortFields[0].Field;

            //HOLDING FOUND SORT FIELD

            tempDef = Report100.DataDefinition.SortFields[i].Field;

            //REPLACING FOUND FIELD  DEFINITION WITH OTHER FIELD DEFINITION EXCEPT (Already Present SortField in design time) BECAUSE THESE SORT FIELDS ARE ALREADY IN REPORT SO GENERATE ERROR


            Report100.DataDefinition.SortFields[i].Field = Report100.Database.Tables[0].Fields[5];


            //SWAPPING THE CURRENT SORT FIELD WITH FOUND SORT FIELD

            Report100.DataDefinition.SortFields[0].Field = tempDef;
            Report100.DataDefinition.SortFields[i].Field = currentDef;

            break;

        }
    }

    //IF SORT FIELD IS NOT PRESENT THEN REPLACE THE CURRENT FIELD DEFINITION


    if (fieldPresent == false)

    {
        FieldDefinition FieldDef = New FieldDefinition;
        FieldDef = Report100.Database.Tables[0].Fields[SortField];
        Report100.DataDefinition.SortFields[0].Field = FieldDef;
    }

Report100.DataDefinition.SortFields[0].SortDirection = CrystalDecisions.Shared.SortDirection.AscendingOrder;



In above code, I am setting sort order field, Here this I am swapping sort field orders but we can’t swap sort field directly because if field is present as sort field then it will generate error, for this I have written code to identify that sort field is present or not.


If sort field is not present then I am directly setting given field ("SortField") as sort field. Otherwise to set sort order field I am implementing swapping concept.


For this I have take two objects of  FieldDefinition class.


FieldDefinition tempDef = New FieldDefinition;
FieldDefinition currentDef =New FieldDefinition;


Using this first, I am replacing present sort order field  with different (which is not present in sort order field ) sort order field . Finally I am changing the sort order like this-

 //SWAPPING THE CURRENT SORT FIELD WITH FOUND SORT FIELD


Report100.DataDefinition.SortFields[0].Field = tempDef;

Report100.DataDefinition.SortFields[i].Field = currentDef;


Now done, try this code, I have successfully implemented this code.



Thanks


2 comments:

  1. Hi,

    Your post is really helpful for me, I was searching for this solution from long time, your code worked for me.

    Really good one.

    Thanks

    ReplyDelete