Skip to Content

Crystal Reports version:

2008

Background:

Suppose we have fields as below in a report and respective data types and parameter value (used for dynamic sorting) as below:

Database Fields Detail

Suppose the parameter name is “dyn_sort_param

Say, we have to use a dynamic group for sorting the records using following fields:

DESIGNATION, JOIN_DATE, DATE_OF_BIRTH, SALARY, AGE and LOCATION.

There is a limitation in Crystal that, we cannot use different data type fields for creating group dynamically using say if else condition. Either we have to convert different data type fields to a particular data type (e.g. number and date fields to string) or then we can use them for grouping.

So, the if else conditions for group (say dynamic_sort) will be as below:

If dyn_sort_param = “designation” then DESIGNATION

Else if dyn_sort_param = “join_dt” then cstr(JOIN_DATE)

Else if dyn_sort_param = “dob” then cstr(DOB)

Else if dyn_sort_param = “sal” then cstr(SALARY)

Else if dyn_sort_param = “age” then cstr(AGE)

Else if dyn_sort_param = “loc” then LOCATION;

Drawback with conversion of date and number fields to string:

In the above if else conditions, we are using cstr Crystal function for fields JOIN_DATE, DOB, SALARY and AGE to convert them from date and numbers to strings, so that we can use them for dynamic grouping.

If we are converting number and dates to strings, the sorting of data will not be correct.

The number sorting will be in the fashion 1, 2, 3, 4 ….10, 11, 12….100, 101 etc.

Whereas the string sorting will be 1, 10, 11, 100, 101…..2, 20, 21, 200 etc.

So, the sorting of number field converted to a string will not provide us the data in the intended fashion.

Solution:

To achieve the dynamic sorting as intended, we need to go for grouping without converting the fields to a particular data type (in this case, it is String data type).

This can be achieved using 3 different groups one each for string fields, number fields and date fields as below:

Group1 for number fields (Group Name: dyn_sort_num)

Grouping logic:

If dyn_sort_param = “sal” then SALARY

Else if dyn_sort_param = “age” then AGE;

Group2 for date fields (Group Name: dyn_sort_date)

Grouping logic:

If dyn_sort_param = “join_dt” then JOIN_DATE

Else if dyn_sort_param = “dob” then DOB;

Group3 for string fields (Group Name: dyn_sort_string)

Grouping logic:

If dyn_sort_param = “designation” then DESIGNATION

Else if dyn_sort_param = “loc” then LOCATION;

This separate grouping will give us the data with sorting as intended for number and date fields also along with string fields.

P.S:

Groups as such will have their own performance issues, since we are using 3 different groups; there may be a chance of bit more performance issue.

Hence, to achieve business logic as intended, if a bit performance is not a problem, then above method can be used.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply