INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Conditional sorting with group and multiple fields

Conditional sorting with group and multiple fields

(OP)
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!

RE: Conditional sorting with group and multiple fields

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.

RE: Conditional sorting with group and multiple fields

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.

RE: Conditional sorting with group and multiple fields

(OP)
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.

RE: Conditional sorting with group and multiple fields

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.

RE: Conditional sorting with group and multiple fields

(OP)
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.

RE: Conditional sorting with group and multiple fields

(OP)
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!!

RE: Conditional sorting with group and multiple fields

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close