| Author |
 Topic  |
|
|
ZMike
Posting Yak Master
110 Posts |
 Posted - 07/05/2012 : 16:08:29
|
I have a ROW_NUMBER() option in my SSRS Report
Example
SELECT *
,ROW_NUMBER() OVER(ORDER BY Zip5 , Street) AS RowNumber
FROM Customers
ORDER BY Zip5 , Street
However the actual query that's running is ignoring my order by in my main dataset. This is messing up my Row Number.
I did turn the sort options on in both the group and the tablix and it will sort correctly but it's leaving my RowNumber all messed up since it's not presorting.
The main reason I was doing this is 2 reasons.
1. So I could let users get a certain amount of data (like using a top 10)
2. So that I can also use the row numbers in my header
Text Box 1 (Right Aligned)
= "Records " & MIN(ReportItems!RowNumber.Value) & " to "
TextBox2 (Left Aligned)
=MAX(ReportItems!RowNumber.Value) & " of "
& Count(Fields!RowNumber.Value, "DataSet1") & " Total Records"
If I could make the Main Order By Clause work before the dataset has to sort it then I'd be golden. |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47147 Posts |
Posted - 07/05/2012 : 16:15:51
|
what do you mean by its ignoring order by? your select should give you records in order of Zip5,Street. what else are you expecting to get as output?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 07/05/2012 16:16:11 |
 |
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 07/05/2012 : 16:27:45
|
visakh16,
Hopefully This makes sense
My Dataset 1 consists of
SELECT *
,ROW_NUMBER() OVER(ORDER BY Zip5 , Street) AS RowNumber
FROM Customers
ORDER BY Zip5 , Street
** Note , I have all fields listed instead of Select * but due to the data I cannot post it on here. **
However what the DataSet1 Appears to be doing is this
SELECT *
FROM Customers
If I run this query in Managment Studio and then go and look at my RowNumber Column it looks to just be doing a select * and giving the order that it's coming back (Without an OrderBy)
I've read that this can be an issue sometimes and you will have to go to :
Row Groups
Group Properties
Sorting
and then set the sorting here. Which is fine. I can make the data sort that direction. But since it's happening after it's already ran the data, my ROW_NUMBER() OVER(ORDER BY Zip5 , Street) data is no longer in order.
So instead of showing
Address 1 RowNUmber 1
Address 2 RowNumber 2
Address 3 RowNumber 3
It's Showing
Address 1 RowNumber 3
Address 2 RowNumber 1
Address 3 RowNumber 2
So it is showing the main data in the correct order, but only after I either go into RowGroups or the tablix and set the sorting there.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47147 Posts |
Posted - 07/05/2012 : 16:57:35
|
are there multiple records with same address? anyways this should give you result on order of row number
SELECT *
FROM
(
SELECT *
,ROW_NUMBER() OVER(ORDER BY Zip5 , Street) AS RowNumber
FROM Customers
)t
ORDER BY RowNumber
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 07/05/2012 : 22:38:47
|
Visakh16,
If I run the query in the query designer then I get data as follows which is correct...
State Zip5 Street Address RowNumber
Washington 99201 Main Street 123 1
Washington 99201 Main Street 234 2
Washington 99201 Main Street 345 3
Washington 99201 New Rd 1 4
State Zip5 Street Address RowNumber
Washington 99201 Main Street 123 3
Washington 99201 Main Street 234 2
Washington 99201 Main Street 345 1
Washington 99201 New Rd 1 4
So it will list them correctly. But again only if I go into the tablix and put the sorting there. It seems to ignore my order by in the dataset itself. If I try to add the Row Number in the Tablix Sorting then it would be like this
State Zip5 Street Address RowNumber
Washington 99201 Main Street 345 1
Washington 99201 Main Street 123 2
Washington 99201 New Rd 1 3
Washington 99201 Main Street 234 4
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47147 Posts |
Posted - 07/05/2012 : 22:44:40
|
you cant guarantee order of display inside tablix unless you put an explicit sorting in it
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 07/05/2012 : 23:29:49
|
Visakh16,
I'm not sure if this is the best way but it's working.
IF OBJECT_ID('tempdb..#Customers') IS NOT NULL
DROP TABLE #Customers
SELECT *
INTO #Customers
FROM Customers
ORDER BY Zip5 , Street
SELECT * , ROW_NUMBER() OVER(ORDER BY Zip5 , Street) AS RowNumber
FROM #Customers
This is keeping the correct order by since I put it in the temp table first.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47147 Posts |
Posted - 07/05/2012 : 23:41:14
|
thats fine but unless you put an explicit sort on tablix you cant guarantee the order of retrieval.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Order By Help
