Sorting Field in CrossTab Report 2

[begley]

My CrossTab report shows "Beats" in the column. The order is 1, 10, 11, etc. I want 1, 2, 3. Can you advise me how to accomplish this?

Thanks Amanda

 

[BobSuruncle]

If this String field ALWAYS contains numbers, you could just write a formula to convert to a number.
@Beats
ToNumber({YourTable.YourField})

Then use this formula in the Cross-tab in place of the field.



HTH
Bob Suruncle

 

[begley]

Thanks Bob. I have done that and it worked!

 

[begley]

I said, in error, that this worked. It didn't and the reason being I didn't realize the field does not contain numeric data. The "beat" field contains 1, 2, 3, and so on, but there is also HDQ, CEN, etc. I would like for the numbers to appear first in the cross tab in numberic order(they are the columns) and then the alpha to appear in alphabetic order.

Thanks in advance asbegley@indygov.org

 

[BobSuruncle]

Is your data always 3 characters in length?
Use a formula in the crosstab like this:
Right("000" + {NumericText.NumericText},3)

HTH


Bob Suruncle

 

[lbass]

The easiest approach might be to go to format crosstab->select {beat} under "column"->group options and use specified order.

-LB

 

[begley]

I tried both ways. The Right("000" +{NumericText.NumericText},3)gave me a weird result. I then tried the specified order and it seems to be working.

Thanks for the help guys. This is a great site for me (a cobol programmer working my way into the PC world).

Amanda

 

[begley]

I am creating a cross-tab using unit number as the rows and beats as the columns. My problem is, beat "ecp" in the file, I want it to be "17" on the report, "hqa" in the file, I want to be 18 on the report, etc. The file includes beats 17 and 18. I know that I need to create a formula and use it as the column field, my problem is how to write the formula. In cobol I used an array, in crystal I am lost. Thanks Amanda

 

[begley]

I am creating a cross-tab using unit number as the rows and beats as the columns. My problem is, beat "ecp" in the file, I want it to be "17" on the report, "hqa" in the file, I want to be 18 on the report. The file includes beats 17 and 18. I know that I need to create a formula and use it as the column field, my problem is how to write the formula. In cobol I used an array, in crystal I am lost. Thanks Amanda

 

[begley]

I am creating a cross-tab using unit number as the rows and beats as the columns. My problem is, beat "ecp" in the file, I want it to be "17" on the report, "hqa" in the file, I want to be 18 on the report, etc. The file includes beats 17 and 18. I know that I need to create a formula and use it as the column field, my problem is how to write the formula. In cobol I used an array, in crystal I am lost. Thanks Amanda

 

[begley]

Correction:

I am creating a cross-tab using unit number as the rows and beats as the columns. My problem is, beat "ecp" in the file, I want it to be added to beat "17" on the report, "hqa" in the file, I want to be added to beat 18 on the report, etc. The file includes beats 17 and 18. I know that I need to create a formula and use it as the column field, my problem is how to write the formula. In cobol I used an array, in crystal I am lost. Thanks Amanda

 

[lbass]

You could try something like this for your column:

if {beat} in ["17", "ecp"] then "17" else
if {beat} in ["18", "hqa"] then "18" else {beat}

-LB

 

[mski]

I have the same trouble with sorting my columns in a cross-tab Amanda described in her original post.

I tried the approach, LB mentioned ("group options"/"specified order&quot, but I don't get the result I expect. Now my cross-tab consists of a single column with the group name as it's heading. I read somewhere else, that I can provide a database field as the group name so that it will be replaced by the record's value. But if I use the usual notation with {fieldname} then the string "{fieldname}" us used as the column header. I guess that's why my columns are all groupt into one: they all get the same name. What am I doing wrong? Can anybody help me? I'm completely new to crystal reports.

Thanks in advance.
Marc

 

[lbass]

When you go to specified order, you must enter each value into the area "Named Group"--you are naming each instance of the group, not the group itself. So if your group field is quantity, and the instances are 1,2,3, and you want them in order 1,3,2, then place 1 in the named group field and enter, 3 and enter, 2 and enter. You don't have to use the new and edit buttons if the group instances are already defined. If, however, you wanted to cluster results into groups, you would enter "1 and 3" as the named group and then press new and define it as one of these numbers.

To reorder entries in the original screen, just use the direction keys.

If you have only numbers in your field, and they are sorting like text instead of numbers, you could convert them to values first (val({text.field})) and use the formula in the crosstab instead of specified order.

-LB

 

[mski]

Thanks for your fast reply.

I did it this way and and it works, but this solution is probably too limited for me, because the number of columns in my cross-tab is not fixed and the column headers can vary from report to report (it's just a template for a group of similar ones). The header can be month names, days or hours and the number of time units to be shown can be entered by the user.

Isn't it just possible to tell crystal not to sort the data at all? I'm using VS.Net Datasets, that are filled manually with the required data and it already has the right order.

Maybe I could just insert an invisible integer field into my dataset and use that for sorting?

Marc

 

[begley]

Okay, I am back to this report. I have everything working correctly (beats and units in the right order) and now I realize that I need to include two fields in the row of the cross-tab.
The unit could have been assisting unit #1 or #2. Which are two seperate fields in the table. I am using a formula called UnitNumber for the row that is Right("0000" +{VW_DISPATCH_RUNS.AST_UNIT_NUM1},4)
I need that to make the unit numbers come out in 1,2,3 order. How do I add the 2nd field?

Thanks Amanda