Hi All,
Does anyone know how I can create a column with a counter in it for the number of rows that are going to be returned.
=NL("Table";"15 G/L Account";$E$9:$T$9;"Headers=";$E$8:$T$8;"TableName=";"15 G/L Account") is the method used to create the table.
For arguments sake, there are 1102 rows(records) and next to each row returned I want a number to be returned from 1 to 1102 in sequence.
The reason for this is that the acc. no is text ie. 1000,1001,1002,1050,10049 but the Pivot table is sorting it as 1000,1001,1002,10049 then 1050.
I don't want to setup a custom sort as this needs to be dynamic as account numbers can be added at any time and then I can sort on this sequencing field instead of the acc. no.
Any help is greatly appreciated.
Regards
Tom
Date
Votes
3 comments
-
Jet Reports Historic Posts Official comment Nice :) Thanks for sharing!
Cheers,
Hans -
Jet Reports Historic Posts Hi Tom,
Interesting idea. I would never have thought of adding a row counter. I don't know a solution for that by heart, but how about this…
Would adding leading zeroes to the account number be an option? Then your account numbers would all have the same length (01000, 01002 etc.) and sorting would work fine. I know this is not an option for some countries, but maybe it is for you?
Leading zeroes can be added with a TEXT function in the Table builder.
Just my 2 cents, thinking out of the box. I hope it works for you. -
Jet Reports Historic Posts Hi Hans,
Many thanks for your reply.
I have literally just worked out something by accident which seems to do the sorting trick correctly. Just need to hide the sorting list.
No more trying to add zeros I think.
Using the formula =NP("Formula";"ROW()-10") in the Counter column where "10" is the row number of the start of my table so the count starts at "1" instead of "11", Jet seems to take care of the rest as the current row number is always the current row that the table builder adds if that makes sense.
The count started at 1 and ended at 1102 which seems to have solved my problem.
Regards
Tom
Please sign in to leave a comment.