Data report Problem.

[ISChana]

Hi Dears.
I hv a samll problem in Data report . I like to know how to display the serial nos in the data reports.

ISChana

 

[tlbroadbent]

Please explain what you mean? By "serial nos" do you mean row numbers in the result set of a query? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[ISChana]

Dear Terry L. Broadbent

Its simple meaning is to display serial nos.in the data report.I'll give an example..

Suppose there are 20 students in the class..
My report should be as ;
Sr.No. Roll No. Name of the student
1 9891 xxxxxxxxxx
2 9892 yyyyyyyyyy
3 9893 zzzzzzzzzz
. .... ..........


. .... ...........
20 9900 hhhhhhhhhhh

Everthing Except Sr.No. i.e Roll No. Name of the Student/Class etc coming from Table. I am unable to print Seral Nos. i.e 1 to 20

Thanks
I think Now probelm would be clear.

Inder

 

[mukund]

Hi Inder,
Try the following query.

Select RollNo, Name,
(Select count(*) from Students Where RollNo < s.RollNo) as SrNo
From Students Order By RollNo

Here I am assuming the name of tables & columns, you can replace the names of students table and columns RollNo & Name.

Hope this helps,
-Mukund

 

[mukund]

Hi Inder,
Sorry missed the alias in previous query, try this one.

Select RollNo, Name,
(Select count(*) from Students Where RollNo < s.RollNo) as SrNo
From Students s Order By RollNo

Here I am assuming the name of tables & columns, you can replace the names of students table and columns RollNo & Name.

Hope this helps,
-Mukund

 

[ISChana]

Dear Mukand..
It works if it is sorted in that order .
My real meaning was to just put serial nos with the resultant.
as in my example let's say i hv this data

Roll No Name

11 Inder
12 Manish
13 Davinder
14 Rajesh

and i like to display name-wise report i.e. output shd be
SrNo Rollno Name
1 13 Davinder
2 11 Inder
3 12 Manish
4 14 Rajesh

but if we apply ur query it would give differnt result
i think prb would be clear to u..
i just like to put some variable (Counter type )
with each record as we do with other report writers.

So. Please reply.

Inder

 

[Inwoner]

Perhaps you could try to create a tempory table with an Identity field, and insert your result into the table, and reselect the data.

Note that going this way would reduce performance, but this is the fastest way I can think of doing it.

 

[JamesLean]

I think your question is clear now!

I don't think there's an automatic way of doing it but you could try using a temp table with an IDENTITY column:

CREATE TABLE #r (
	Rank int IDENTITY,
	Rollno int,
	[Name] varchar(20)
)

INSERT #r
SELECT rollno, [name]
FROM tablename
ORDER BY [name]

SELECT * FROM #r
ORDER BY rank

DROP TABLE #r

Obviously you can just change the ORDER BY clause in the INSERT statement to change the order they are displayed!