Cross tabs - manual or better formulas? 2

[crystalite]

I'm using Crystal 8.5 and SQL.

I need to create a report that after selecting an item, will show all jobs related to that item and the time to complete each operation (op).

The fields are Job, Operation, description (actually 3 fields make up the description)

Here's what I would like:
Job Job Job
Op 76 462 974
10 description data data data
30 description data data data
40 description data data data
50 description data data data
60 description data data data
70 description data data data
80 description data data data
90 description data data data
100 description data data data
110 description data data data
120 description data data data

If I use the cross tab report, I use a concatenate for my row (operation & description), but then my rows appear as:
Job Job Job
Op 76 462 974
10 description data data data
100 description data data data
110 description data data data
120 description data data data
20 description data data data
30 description data data data
40...

I need the operations to sort in ascending order and show the description.

I think maybe I should create a manual cross tab, but I not sure how to set up the columns.

I would appreciate any help.

 

[beanbrain]

In the Cross-Tab expert you can choose to group on the orignal "Op" Then click on the row variable or field and click the "Group Options" button. In the pop-up window click on the "Options" tab. Check the "Customize Group Name Field" to on, click on "Use a formula as Group Name", click on the "X-2" button, and put the contents of the formula for the concatinated operation and description in there.

This will force the cross tab to be in the Op order alone but display the concatinated value as the row title.

 

[lbass]

Don't give up on the inserted crosstab yet. For your concatenation formula, format the number field so that it will sort correctly, as in:

totext({table.ops},"000") + " "+{table.description}

Use this as your row field. While it is highlighted, click on "group options"->customize group name->use a formula to customize name->x+2 and enter:

totext({table.ops},0,"") + " "+{table.description}

This will give you your original display (after refreshing your report), but with the correct sort.

-LB

 

[crystalite]

Beanbrain and LB thank you for your quick response.

Ibass - I used your suggestion and it worked perfectly. You rock!!!!