cancel
Showing results for 
Search instead for 
Did you mean: 

Conditional sorting

0 Kudos

Hello,

I have a Crystal Report that accepts an incoming parameter indicating how the report should be sorted.  If the parameter comes in as "company", then the report needs to be sorted A-Z by company, with page breaks after each individual company.  If the parameter comes in as "revenue", then the report needs to sort by revenue in descending order instead of sorting/grouping by company.

My first thought was to create a formula to serve as a group, by which I could sort my results.  If the parameter came back as "company", the grouping value would be the Company field, otherwise, it would be the Revenue.  The problem seems to be that I can't have one sort in ascending order while the other sorts in descending order, at least from the Record Sort expert.

Any ideas on how I might accomplish this?

Thanks!

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

Actually, I'm thinking I might be able to do this by having Group1 be a formula that checks to see what the parameter value is.  If "Company," then the formula returns the Company field (and the Group Footer #1 will conditionally page break in this instance).  If "Revenue," just return an empty string.  Then I can set up a secondary sort based on Revenue.

Hoping this does the trick!

abhilash_kumar
Active Contributor
0 Kudos

That should work however, you'd need a formula as a secondary sort.

The formula would look like this:

If {?Sort_Prompt} = 'Country' then

{Country}

else

{Revenue}

And then  make sure the sort direction is set to Descending.

-Abhilash

abhilash_kumar
Active Contributor
0 Kudos

Hi Bob,

Here's what you need to do:

1) Create a formula called '@Sort 1' with this code:

If {?Sort_Prompt} = 'Revenue' then '!'

else {Country}

2) Create another formula called '@Sort 2' with this code:

If {?Sort_Prompt} = 'Country' then '!'

else Right('000000000000'&totext(tonumber({Revenue})),15)

3) Go to the Report Menu > Record Sort Expert.

Add @Sort 1 to the Sort List and set its sort direction to 'Ascending'.

Add @Sort 2 after that and set its sort direction to 'Descending'.

P.S: Remove any groups you might have inserted on the report.

-Abhilash

0 Kudos

Hi Abhilash,

Is there no way to achieve this using grouping?  My concern with eliminating the group is that there is conditional page breaking in the Group Footer when the parameter value is "company".  Each company would need to be on its own page, but if it is "revenue," then no page breaking should occur.