Create Sequence Numbers in Query and Report Writer

 

Problem Statement:

How to Number Records from 1-99 or Higher within Query or Report Writer?

Solution:

1. In Query use the ROW_NUMBER() function to create a column that will display the row number as shown in the following SQL:

SELECT
 ROW_NUMBER( ) OVER( ) AS "ROW",
 EMPID,
 CUSTID,
 TOTBIL,
 TOTPAY
FROM
 SCDB1.INVHDR INVHDR
ORDER BY  2

2. Create a Macro like the following in Report Writer that will add an extra column that numbers the records from 1-? for the Entire Report.

Sub
  counting()
  DerivedField Str$(Current)
End Sub

3. Alternately, to count the rows within each group use the following Macro-Derived Field in Report Writer.

Sub
  RowCount()
  TrueCount = 0
  ThisRecord = 0
  Group$=Field$("EMPID")
  While (Group$=Field$("EMPID") AND Current() > 1)
  TrueCount = TrueCount + 1
  GetPrevious Wend if(Group$=Field$("EMPID") AND current() = 1) then TrueCount = TrueCount + 1 GetPrevious end if DerivedField Str$(TrueCount)
End Sub

Ref#: 1475696