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
  1. 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
  1. 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

 

 

 

 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: April 21, 2017