SQL Query Sort order is correct, but Reporting Services report shows different

Question

SQL Query Sort order is correct, but Reporting Services report shows different

rkelly58

SSCrazy

Points: 2151
More actions
September 22, 2015 at 5:11 am

#308011

In SQL, i'm getting this output:
12 amNULL
1 amNULL
2 amNULL
3 amNULL
4 amNULL
5 amNULL
6 amNULL
7 amNULL
8 amInduction
9 amInduction
10 amInduction
11 amInduction
12 pmInduction
1 pmInduction
2 pmInduction
3 pmInduction
4 pmInduction
5 pmInduction
6 pmNULL
7 pmNULL
8 pmNULL
9 pmNULL
10 pmNULL
11 pmNULL
The above is correct. I have no sorting on the Query.
However, when I put the query in a Reporting Services report and run it, I'm getting this
12 am
1 am
2 am
3 am
4 am
5 am
6 am
7 am
6 pm
7 pm
8 pm
9 pm
10 pm
11 pm
8 am induction
9 am induction
10 am induction
11 am induction
12 pm induction
1 pm induction
2 pm induction
3 pm induction
4 pm induction
5 pm induction
I want the report to be sorted (or rather the same as the SQL query), so that the report show 12am and ends at 11pm
Any ideas folks?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

you must have an explicit ORDER BY in your query to guarantee the data appears in a specific order. you stated you have no order by, so that is the rock solid fix to the problem....add an explicit order by.

SQL is tasked with getting the data in the fastest way it can, and the plan to do that can change due to a huge variety of factors....different ANSI settings, connection settings,indexing, statistics, etc.

getting the same results, in the same order is just coincidence, and could change. you need to get an ORDER BY in there.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rkelly58 SSCrazy Points: 2151 More actions · Answer 2

This the SQL (that J Livingston helped me with over on the SQL forum)

with roomhours (hour_number ,hour_description) as

(

SELECT 0, '12 am' UNION ALL

SELECT 1, '1 am' UNION ALL

SELECT 2, '2 am' UNION ALL

SELECT 3, '3 am' UNION ALL

SELECT 4, '4 am' UNION ALL

SELECT 5, '5 am' UNION ALL

SELECT 6, '6 am' UNION ALL

SELECT 7, '7 am' UNION ALL

SELECT 8, '8 am' UNION ALL

SELECT 9, '9 am' UNION ALL

SELECT 10, '10 am' UNION ALL

SELECT 11, '11 am' UNION ALL

SELECT 12, '12 pm' UNION ALL

SELECT 13, '1 pm' UNION ALL

SELECT 14, '2 pm' UNION ALL

SELECT 15, '3 pm' UNION ALL

SELECT 16, '4 pm' UNION ALL

SELECT 17, '5 pm' UNION ALL

SELECT 18, '6 pm' UNION ALL

SELECT 19, '7 pm' UNION ALL

SELECT 20, '8 pm' UNION ALL

SELECT 21, '9 pm' UNION ALL

SELECT 22, '10 pm' UNION ALL

SELECT 23, '11 pm' )

,cte as (

SELECT Description, StartDateTime, EndDateTime, Name

FROM (SELECT data.V_ACTIVITY.Description, data.V_ACTIVITY_DATETIME.StartDateTime, data.V_ACTIVITY_DATETIME.EndDateTime,

data.V_LOCATION.Name

FROM data.V_ACTIVITY INNER JOIN

data.V_ACTIVITY_DATETIME ON data.V_ACTIVITY.Id = data.V_ACTIVITY_DATETIME.ActivityID INNER JOIN

data.V_ACTIVITY_LOCATION ON data.V_ACTIVITY.Id = data.V_ACTIVITY_LOCATION.ActivityId INNER JOIN

data.V_LOCATION ON data.V_ACTIVITY_LOCATION.LocationId = data.V_LOCATION.Id) AS [#Tempy]

WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room)

)

SELECT

r.hour_description

, cte.description

, cte.StartDateTime

, cte.EndDateTime

, cte.Name

FROM roomhours AS r LEFT OUTER JOIN

cte ON r.hour_number >= datepart(hh,cte.StartDateTime)

and r.hour_number < datepart(hh,cte.EndDateTime);

Can I put an ORDER by in there (can you order the hour description? can you sort am and pms?

Lowell SSC Guru Points: 323504 More actions · Answer 3

if you were gettign help in another thread, you probably should have continued in that thread, instead of a new post.

looks like this would work to me:

ORDER BY cte.StartDateTime,r.hour_number

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rkelly58 SSCrazy Points: 2151 More actions · Answer 4

The guy was only helping with SQL, he barked at me when I mentioned reporting services in one my replies.

Thanks for your replies L.

Where do I add the ORDER BY cte_1.StartDateTime, r.hour_description?

I put in at the end of the query, but it produces this output in the Query Designer

1 am

10 pm

11 pm

12 am

2 am

3 am

Still not right, any other ideas?

Lowell SSC Guru Points: 323504 More actions · Answer 5

whoops i menat by hour and not description... changed above.

ORDER BY cte.StartDateTime,r.hour_number

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rkelly58 SSCrazy Points: 2151 More actions · Answer 6

Added that ORDER by at the end of the query, still getting this in Query designer:

12 am

1 am

2 am

3 am

4 am

5 am

6 am

7 am

6 pm

7 pm

8 pm

9 pm

10 pm

11 pm

8 am induction

9 am induction

10 am induction

11 am induction

12 pm induction

1 pm induction

2 pm induction

3 pm induction

4 pm induction

5 pm induction

It's as if its sorting by the second column with NULLS first and then any text..... Should be by first column, 12pm to 11pm

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

try

ORDER BY r.hour_number

....and I wasn't "barking" 😀

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

rkelly58 SSCrazy Points: 2151 More actions · Answer 8

Hi J, I was only joking about the bark, you've been more than helpful 🙂

I just tried adding ORDER BY r.hour_number to the end, and it works FINE in SQL Query Designer and the order is correct (sorted by hour with AM and PM) Same as it worked fine without the ORDER by line.

However when I use the same query in Reporting Services Designer, it still comes out as this:

12 am

1 am

2 am

3 am

4 am

5 am

6 am

7 am

6 pm

7 pm

8 pm

9 pm

10 pm

11 pm

8 am induction

9 am induction

10 am induction

11 am induction

12 pm induction

1 pm induction

2 pm induction

3 pm induction

4 pm induction

5 pm induction

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

rkelly58 (9/22/2015)
Hi J, I was only joking about the bark, you've been more than helpful 🙂
I just tried adding ORDER BY r.hour_number to the end, and it works FINE in SQL Query Designer and the order is correct (sorted by hour with AM and PM) Same as it worked fine without the ORDER by line.
However when I use the same query in Reporting Services Designer, it still comes out as this:
12 am
1 am
2 am
3 am
4 am
5 am
6 am
7 am
6 pm
7 pm
8 pm
9 pm
10 pm
11 pm
8 am induction
9 am induction
10 am induction
11 am induction
12 pm induction
1 pm induction
2 pm induction
3 pm induction
4 pm induction
5 pm induction

its ok...I appreciated the joke about barking.

as I said on your other thread...I really cant help with RS....a quick google throws up a lot of options, but we really need someone with experience to step in here.

sorry I cant help more.

good luck

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

rkelly58 SSCrazy Points: 2151 More actions · Answer 10

Got the sorting working...... I added J's r.hour_number parameter to the Report fields, and then was able to choose that field in Report Designer and its sorted them correctly.

Thank you, thank you thank you.

J, could I possibility skip back to my original SQL thread and ask you a couple more questions on the SQL code you gave me?

As far as this thread goes, hats off to you J and Lowell for your help.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

rkelly58 (9/22/2015)
Got the sorting working...... I added J's r.hour_number parameter to the Report fields, and then was able to choose that field in Report Designer and its sorted them correctly.
Thank you, thank you thank you.
J, could I possibility skip back to my original SQL thread and ask you a couple more questions on the SQL code you gave me?
As far as this thread goes, hats off to you J and Lowell for your help.

glad you sorted it....as for more q's on original thread...pls do (cant promise any solutions though:-))

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day