Crosstab TopN sorting -- Better Approach?? 1

[ind123p]

I am using CR XI, Access 2000.

I have inerted a crosstab in the report. The report is as below

Jan Feb Total
Hours Invoice Hours Invoice Hours Invoice
Staff P 5 $100 2 $50 7 $150
Staff Q 2 $50 3 $150 5 $200
Staff R 3 $150 4 $140 7 $290
Client A 10 $300 9 $340 19 $640
Staff P 5 $100 2 $50 7 $150
Staff Q 2 $50 4 $110 6 $160
Staff R 3 $150 4 $140 7 $290
Client B 10 $300 10 $300 20 $600

I need to sort ascending or descending on Total Invoice or Total Hours based on a parameter (?Invoice) or (?Hour).


I am able to accomplish the above by creating 4 different cross tabs and suppressing the ones that do no match the parameter criteria. I am using TopN Group Sort Expert.
crosstab 1 - Invoice Ascending
crosstab 2 - Invoice descending
crosstab 3 - Hour Ascending
crosstab 4 - Hour descending

I think my approach is not so good. Is there a better way of doing this ?

I will appreciate if somebody could give me a direction.

 

[lbass]

You could create a parameter {?Sort} with options: "Invoice-Asc", "Invoice-Desc","Hour-Asc","Hour-Desc". Then create a formula {@sort}:

select {?Sort}
case "Invoice-Asc" : {table.invoice}
case "Invoice-Desc" : -{table.invoice}
case "Hour-Asc" : {table.hour}
case "Hour-Desc" : -{table.hour}

Insert {@sort} as a third summary field, and then set the group sort based on this formula. Select "All" and {@sort} "Ascending".

Finally suppress the inner and total columns and totals for {@sort}, and then resize the columns to minimize the width.

-LB

 

[ind123p]

Thanks LB for a quick response.

I will try your suggestion and let you know.

 

[ind123p]

Thanks LB!!!! Your solution worked perfect..

STAR for you!!!!