[RESOLVED] serial no in datareport

Hi friends,

How to generate automatically serial nos. in datareport.
Suppose if it displays 100 employee's payment report,serial no.should be generated from 1-100.Please help me?

Add a autonumber field to the table and bind that to the Serial Number

Thanks.
Is it possible if we merge two tables also?Please explain how to do!

But if we want to print report for deptwise,can we use auto serialno?

instead you can consider generating Serial Number at the query result itself...

How are you setting up the report at the moment? Through the data environment or at run-time through set datasource = recordset? And what's the SQL that fills the command object/recordset?

using query,how to generate ?Please give one example.

Code:

---

       

vb Code:

       

Private Sub cmdOk_Click()

    On Error Resume Next

    If Txtdate = "" Then

        flag = MsgBox("Display Entire Issued Report?", vbYesNo + vbQuestion, "Materials System")

        If flag = vbYes Then

            If Rs.State = adStateOpen Then

                Rs.Close

            End If

            Rs.open "Select * from Sale where qty>0", conn

            Set IssdateReport.DataSource = Rs

            Load IssdateReport

            IssdateReport.Show

            Unload Me

        Else

            Exit Sub

            Txtdate.SetFocus

        End If

    Else

        If Rs.State = adStateOpen Then

            Rs.Close

        End If

        Rs.open "select * from Salesnew where date between '" & Txtdate.Text & "'and '" & txtdateto.Text & "'", conn

        Set IssdateReport.DataSource = Rs

        Load IssdateReport

        IssdateReport.Show

        Unload Me

    End If

End Sub

---

I see your getting a subset based on dates... you might want to read this first http://www.vbforums.com/showthread.php?t=450993 and decide if its feasible or not to pursue a subquery solution.

You can also try printing out your data with Excel or a better reporting tool since the data report is too basic.

Thanks.
Is it possible to include serial no. in datareport through data environment?

Is the serial number the number of the record in the report, or is it the number of sale? (IOW, should a particular sale have the same serial number every time it appears on a report, or should the numbers start over for each report?)

Moved.

Thanks.
Serial number should come according to the no.of rows retrieved for that particular report.

For example,if we want to display the no.of employees(ex.125 emp.) in a particular dept. , serial no.s should be generated from 1-125 in a report.

I think the only way to add a serial number is to make it a part of the query.Check this

Code:

---

SELECT (select count(a2.emp_no) from tblEMP as a2
where a1.emp_no<=a2.emp_no) AS [SL No],
a1.name, a1.address FROM account AS a1
GROUP BY a1.emp_no, a1.name, a1.address
ORDER BY a1.emp_no DESC

---

Yes amrita, as an aggregate query (count) that is a subquery... for a1.emp_no<=a2.emp_no to work properly as intended, refer to link in post #8... also, such an implementation will also involve additional overhead moreso with lots of returned records.

Quote:

---

Originally Posted by Akshaya

using query,how to generate ?Please give one example.

---

Check my signature for "Add Serial Number / Row Number to SQL Queries" to add serial / row number to a query

Thanks friends.
I used the following query.It is working fine.
But Serno comes in desc order by default.How to arrange in asc. order?

Code:

---

SELECT Code, Name, grade,remarks,(SELECT COUNT(*) FROM Customer C2 WHERE C2.code >= C.Code) AS SerNo FROM Customer C

---

Thanks friends.It is working fine in this code.

Code:

---

SELECT code, name, grade,remarks,(SELECT COUNT(*) FROM Customer C2 WHERE C2.Code >= C.Code) AS SerNo FROM Customer C  order by c.code desc

---

Quote:

---

Originally Posted by Akshaya

Thanks friends.It is working fine in this code.

Code:

---

SELECT code, name, grade,remarks,(SELECT COUNT(*) FROM Customer C2 WHERE C2.Code >= C.Code) AS SerNo FROM Customer C  order by c.code desc

---

---

Thanks for sharing the solution:thumb:

Akshaya: if you change the greater than symbol in the query to lesser than symbol it would give you the result what do u want...

for more explanation please refer this