please help with cross-tab table 2

[sql99]

Hello everyone,

I have an existing report that has a cross-tab table in it. On the column part of the table, it's pulling question numbers that are not archived in the database table. For example, it's pulling, 7, 8, 9, 12, 15 based on their active flag in the table. I would like to change these numbers to 1, 2, 3, 4, 5. I was able to do this when the field wasn't in a cross-tab table using the 'record number' special field. However, I cannot use this field in the cross-tab table. Is there a formula I can write to implement this change? Or is there a way to add the 'record number' field that I'm not aware of?

Thanks in advance for your help..

sql99

 

[beanbrain]

I was able to create a new formula in the Cross-Tab Expert called RecordNumber. The contents of the formula are;

RecordNumber;

I then added this to the row area of the Cross-Tab Expert. By moving it to the top I now have a cross-tab ordered vertically by record number.

The trick with using formula in Cross-Tabs is that they cannot use variables.

 

[sql99]

Hi Beanbrain...Thanks for the tip, it worked....!!

 

[davidwendelken]

Are you sure that works?

I tried adding a record number formula to my column table. I don't see any evidence of it knowing I meant the column table instead of the row table or the cell table.

I added the record number to the list of fields displayed in the column heading on a cross-tab report I'm learning on. It now appears to bring back the record number of the cell table, not the column table. Instead of 10 columns, I now a few thousand.

I just realized that the suggestion said the rownumber formula was added to the *row* area! Why there and not the column area? (And I just tested and get one row back for each cell record.)

 

[davidwendelken]

I work in an oracle environment with crystal reports 10.

I added a sql statement (add command in the database expert) that included the record number of the "column" table. Example:

select the_id, the_labeltext, rownum from the_table
order by the_labeltext

rownum is Oracle's keyword for record number.

This got me the correct number of columns in the report, with both the rownum and the labeltext displayed as column headers.

 

[sql99]

Hello,

I added the formula RecordNumber to the column section of the cross-tab expert and that fixed the problem. Instead of my numbers starting at 7, it started at 1. However this only works if it's pulling one row. I tried it w/two rows and the numbers got all messed up...

Thanks all for your help...

sql99

 

[davidwendelken]

sql99,

Did you try my suggestion about adding the rownum (or your database's equivalent) to a hand-coded sql command for the "column" table?

I think that will fix the problem you just mentioned.

David Wendelken

http://www.casetech.net