Change the sort order (ascending/descending) based on parameter field

[Former Member]

I have created a formula on a subreport to change sorting based on variables.

select {?Pm-@Sorting}

case "property_type_desc ASC" :

{owned_property.property_type_desc}

case "property_type_desc DESC" :

{owned_property.property_type_desc}

default:

({owned_property.property_type_cd}; )

how can I change the sort order ascending and descending using the formula??

Thanks, Jessica

[former_member11696]

CR in cluded in Visual Studio 2005 is version 10.2 and .NET 2008 is 10.5 and both based CR 10 functionality and in a limited set. To get full control upgrade to CR 10 Developer or above.

[Former Member]

Try this

1. Create a parameter with 2 default values in the picklist: Descending and Ascending.

NOTE:

In Crystal Reports version 5 or 6, it is not possible to have a pick a parameter value from a list. The user must enter a specific value, guided by the prompting text. For example : To Sort in Ascending order type A, to sort in Descending order type D.

u2022 On the 'Insert' menu, click 'Parameter', then click 'New'.

u2022 Type the name and prompting text for the parameter in the corresponding boxes.

u2022 In the 'Type' box, click 'String'.

u2022 Click 'Set Default'.

u2022 In the 'Value to add' box, type 'Descending' then click '>' to move it to the 'Default Values' list.

u2022 Repeat the above step for 'Ascending'.

u2022 Click 'OK', then click 'OK' again to return to the report.

2. Create 2 formulas based on the field you want to change the sort direction on:

u2022 On the 'Insert' menu, click 'Formula', then click 'New'.

u2022 Create this formula:

@Asc

If {?Parameter} = "Ascending" then u2022 Save this formula and close the Formula Editor. This brings you back to the Formula tab of the Insert Fields dialog box. u2022 Click 'New' and create this formula: @Des If {?Parameter} = "Descending" then

u2022 Save this formula and close the Formula Editor.

u2022 Click 'Close' to return to the report.

4. Sort the report based on these two formulas.

u2022 On the 'Report' menu, click 'Sort Records'.

u2022 In the 'Report Fields' list, click {@Asc} and move it to the 'Sort Fields' list.

u2022 Under 'Sort Direction', click 'Ascending'.

u2022 In the 'Report Fields' list, click {@Des} and move it to the 'Sort Fields' list.

u2022 Under 'Sort Direction', click 'Descending'.

u2022 Click 'OK' to return to the report.

Once you preview the report you will be prompted to select a parameter value, which will determine how the report is sorted.

Regards,

Raghavendra

[Former Member]

What version of Crystal Reports are you using?

[Former Member]

Crystal Reports Basic for Visual Studio 2008

[Former Member]

I am not familiar with that version, so bear with me.

When you insert a group, is there an option to "Use a formula for Group Sort Order"?

[Former Member]

Yes, I am doing that with the above mentioned formula. I don't know the syntax to change the ordering (ascending/descending) dynamically based on a parameter that is passed in.

[Former Member]

This would be the logic to use with your example:

select {?Pm-@Sorting}

case "property_type_desc ASC" : crAscendingOrder

case "property_type_desc DESC" : crDescendingOrder

default: crOriginalOrder

[Former Member]

so i can't use the field name AND the order together? I need to sort by the property_type_desc AND asc/dsc.

My select case contains multiple fields but I trimmed it down for the example. In the bigger picture, I have a c# grid that they can change the order based on 4 columns (asc/dsc). We are creating a Crystal report and need to reflect the same sort order. So, I'm passing in the column name and sort order. How can I use those together?

[Former Member]

Create one formula that allows you to dynamically pick a field. This can be the same as what I just provided you except it returns the field instead of the sort order value.

Then, use what I provided in the Sort Order formula. You can accomplish what you want but it is a two step process.

[Former Member]

Ok. I will try that. Thanks

[Former Member]

When I try the following syntax, it's not recognizing crAscendingOrder.

select {?Pm-@Sorting}

case "property_type_desc ASC" :

crAscendingOrder

error: A number, currency amount, boolean, date, time, date-time. or string is expected here.

[Former Member]

Where are you placing the formula?

[Former Member]

I'm not sure I understand the question but I just created a new formula. It has not been placed anywhere on the report yet. It won't let me create it. I right-clicked in the Field Explorder, Formula Fields, New.

[Former Member]

So you need to create this formula first:

select {?Pm-@Sorting}

case "property_type_desc ASC" :

{owned_property.property_type_desc}

case "property_type_desc DESC" :

{owned_property.property_type_desc}

default:

({owned_property.property_type_cd}; )

Group on this formula.

After that, you click on the X+2 button to the right of "Use a Formula for Sort Order" and paste in this logic:

select {?Pm-@Sorting}

case "property_type_desc ASC" : crAscendingOrder

case "property_type_desc DESC" : crDescendingOrder

default: crOriginalOrder

Does that help?

[Former Member]

I think that would work great if I was using Crystal XI.

"Use a Formula for Sort Order" is not a feature of the VS 2008 version I am using.

[Former Member]

I had asked if that option was in that version and you had answered Yes.