Row Numbers in Crosstab 1

[lilolee]

Hi
I am using CR9 and would like the following

2006
1 Lee 9
2 John 7
3 Jane 4
20

As in the Name and Year is a crosstab, but I would like to number each row.

Any ideas?

Many Thanks

Lee

 

[lbass]

Replace your rowfield (name) with a formula:

whilereadingrecords;
numbervar cnt;
stringvar x;

if instr(x,{table.name}) = 0 then
(
x := x + {table.name} + ", ";
cnt := cnt + 1
);
totext(cnt,"000") & " " & {table.name} //add as many zeros as the maximum number of names

For this to work you MUST do a record sort using {table.name}.

-LB

 

[lilolee]

lbass

Thanks for that and it works great except...

My fault I should have said that the crosstab is using TopN, so the numbers I get aren't 1, 2, 3 etc, but something like 4, 12, 5, 9 etc...

I was hoping it was somthing that I could do in the Crosstab, as I will have more than one crosstab in the report.

Cheers

Lee

 

[lbass]

Okay, modify the first formula to:

whilereadingrecords;
numbervar cnt;
stringvar x;

if instr(x,{table.name}) = 0 then (
x := x + {table.name}+", ";
cnt := cnt + 1
);
cnt

Add this formula as your first row field in the crosstab, and the name field as your second row field. Go to the customize style tab and highlight the formula field->suppress subtotals. There will now be one number per name, but they will be incorrect.

In preview mode, select the 1st row field (the formula field)->right click->format field->common->suppress->x+2 and enter:

whileprintingrecords;
numbervar y := y + 1;
false

Then in the same screen go to display string->x+2 and enter:

whileprintingrecords;
numbervar y;
totext(-1*(y-distinctcount({table.name})-1),0,"")

If you want to, you can also go to the customize style tab->format grid lines->select the vertical line between the two row fields->uncheck "draw".

-LB

 

[lilolee]

Again many thanks for your reply.

It didn't quite work out, but using you suggestion I came up with the following formula for the display string

whileprintingrecords;
numbervar y;
totext({@TopN}-((y-distinctcount({Customer.Customer Name})-1))-distinctcount({Customer.Customer Name}),0,"")

where @TopN is the number required in the Group Sort Expert.

Also I had to put the rank as the 2nd column in the CrossTab otherwise it came out with the wrong order.

I will now go away and investigate Display String

Cheers

Lee

 

[lbass]

I assumed you were doing a group sort on all records. Glad you figured out the fix. Note that I was able to use your formula as the first row of the crosstab.

-LB