Is there any way to put record numbers into crosstab reports?

[SamSharma]

using Crystal 8.5, Sql Server 2000, trying to do a crosstab and the associated chart (the chart's the most important thing, I've been told)

I have a date field which I'm using as the item running across the top (that is, a date as the "column" heading).

I'd love to instead use just a number (earliest date is column 1)

I just cannot see a place to specify the "Record Number" special field.

When I try to define a "Formula Field" that only has the "Record Number" the Formula is created but it fails to show up as an item in any of the dialog boxes so that I can replace the date with the serial/event number

I know a way to get row numbers of of sql server but the query is killing the test server which, natch, has a lot fewer rows than production.

 

[lbass]

You could create a a formula {@colcnt}:

whilereadingrecords;
numbervar colcnt;
stringvar x;

if instr(x,totext({table.date},"MM/dd/yyyy")) = 0 then
(x := x + totext({table.date},"MM/dd/yyyy");
colcnt := colcnt + 1);
colcnt

Use this as your column field.

-LB

 

[SamSharma]

That's great. The key I was missing was

whilereadingrecords.

I had defined several formulas but without whilereadingrecords none appeared as an item I could enter into the crosstab.

The text field overflowed (too many records) & I had to change it. Instead of staying with the date field I chose another field in the database. Changed the SQL statement so it does 'Order by table.serno'

The usage of serno is not as silly as it sounds because the table I'm working with is a child or detail table, with multiple copies of serial numbers FK'd to a unique serial number in the parent table.

whilereadingrecords;
numbervar colcnt;
numbervar sernum1 := {tblAnalysisApplications.SessionID};
numbervar sernum;
if sernum < sernum1 then(
colcnt := colcnt + 1;
serno := serno1);
colcnt