Conditional sorting with group and multiple fields

[LMGroup]

I'm using CR2008 with a SQL DB and need to create a conditional sort. The report has 2 RH, 2 PH, 1 group and 1 detail section. If the user selects the detailed sales report, the first RH, PH and detail lines are displayed and I need to sort by salesperson, customer, invoice. There is no grouping for this one. If the user selects the customer summary report, the second RH, PH and the group display (which is grouped by customer). Everything is working perfectly except for the sorting. The various unneeded sections suppress depending on the report type. The sorting works for the summary but the detailed one gets completely muddled because it is doing the group sort first and then the other fields.

The Record Sort Expert is showing:
Group #1: CustomerN
A-SalesmanN
A-CustomerN
A-InvoiceN
A-JobN


I need something like this:

if {?ReportType} = 1 then
{SalesmanN} ,{.CustomerN},{.InvoiceN},{JobN}
else
{CustomerN}

but I don't know how to set it up or where to place it.

Thanks in advance for any help!

 

[kray4660]

Not sure how to do this unless you use formulas for grouping (thus sorting the report). You can always hide the groups if that help.

 

[AndyMc]

What Kray4460 suggest is what I do for conditional grouping/sorting. So your formula called {@sort}:

if {?ReportType} = 1 then
{SalesmanN} & {CustomerN} & {InvoiceN} & {JobN}
else
{CustomerN}

You would group on {@sort}

As Kray4460 suggests, if you only need the group for sorting purposes, suppress or hide the group header and footer.

 

[LMGroup]

I've changed the group to use the formula but it still isn't working for type 1. This is what it currently says:

if {?ReportType} = 1 then
{SalesmanN}//+{CustomerN}+{InvoiceN}+{JobN}
else
{CustomerN}

I removed the other sort fields from the Record Sort Expert so it only has Group #1: @ReportSort

It still works for the summary (type 2) but give a complete jumble on the sort if I use all 4 fields for type 1. Commenting out all but the first gives me a sort on SalesmanN, but I need it to also sort by the other three fields.

 

[Charliy]

Put separaters between your fields in for Type 1

if {?ReportType} = 1 then
{SalesmanN}&' '&{CustomerN}&' '&{InvoiceN}&' '&{JobN}
else
{CustomerN}

if your fields are numeric, it was adding them together.
if your fields were character, but contained numbers, it was mashing them together

Salesman 2 Customer 11 would look identical to Salesman 21 Customer 1.

Also both results of the formula must be the same data type, so if Customer is numeric, do a totext.

 

[LMGroup]

All 4 fields are numeric. When I add anything besides + between the fields in the first condition, it wants a string for the second condition. If I use ToText on the second, the sort order changes.

 

[LMGroup]

I just created several more formulas to cover the extra sort fields and added them into the record sort expert since I couldn't come up with a formula that would work with all of them together.

Thanks for you time!!

 

[Charliy]

add leading zeros to the totext for the second part, then it won't change the sort sequence.