Group Sort Selection

[former_member1343181]

Unable to get an answer on ExpertsExchange, I figured I'd go to the source.

I have created a report that produces the "Top N" number of records based on a parameter field populated at runtime. I have two "summary" fields that I can select that will, in effect, control the group sort order; Sum of Quantity Ordered (this will sort the group by dollar amount) and Max of Item Number (this will sort the group by Item Number)...as shown below.

The problem is that my user will sometimes want the group sort to be by Sum of Quantity Ordered and sometimes by Max of Item Number.

Rather than deploying the report in two versions, I'd like to allow the user to select which group sort order he wants "this time." My first though was to create a boolean parameter field to test if that selection was set to True or False and then, under Group Expert>Options>Use a Formula as Group Sort Order, create a formula that tests the value of the parameter field. Unfortunately, Crystal did not allow me to use a summary field in the formula. Only the below fields are available for use there.

My question is; how can I allow my user to select, at runtime, the group sort order he needs?

[abhilash_kumar · Accepted solution]

Hi Tom,

Since you dealing with summaries of different kind (Sum and Max), the only ways to do this are by using a Subreport or a Custom SQL query - A Custom SQL Query being the best.

Please have a look at Jamie's workaround here:

-Abhilash

[former_member1343181 · Accepted solution]

Thank you very much, Abhilash. I will look into creating the custom SQL query.