sort not working... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

sort not working...

Author

Topic

jhermiz

3564 Posts

Posted - 2005-05-13 : 13:53:17

I have a report with no groupings and no sorting specified in the actual report...and I am getting weird results.
I have to sort by a PROJECT.CATEGORY type, but not in alphabetical order...
The product categories are:
Engine
Transmission
Axle,
Test,
Service,
Install

They want them in that order...so I tried to use the charindex() to do the sort:


CREATE PROCEDURE rsp_overdue_projects
AS
BEGIN
SET NOCOUNT ON
declare @order varchar(100)
set @order = ',Engine, Transmission, Axle, Test, Service, Install,'

Select PROJECT.CATEGORY, PROJ_TASK.TEXT_26, PROJECT.PROJECTNAME, PROJECT.SUBJECT, PROJECT.AUTHOR, PROJECT.MANAGER_ID,  PROJ_TASK.NAME FROM PROJ_TASK
LEFT JOIN PROJECT ON PROJECT.PROJECTID=PROJ_TASK.PROJECTID
WHERE (PROJ_TASK.NAME Like '%Tear%' AND PROJ_TASK.CONTRACTUAL_MS = 2 AND PROJ_TASK.COLOR=255)
OR ((PROJ_TASK.PROJECTNAME LIKE '%SE' OR PROJECT.AUTHOR = 'SERVICE') AND PROJ_TASK.TASK_TYPE=3 AND PROJ_TASK.COLOR = 255)
order by charindex ( ','+CATEGORY+',', @order) DESC --, PROJECT.SUBJECT, PROJECT.MANAGER_ID, PROJECT.AUTHOR

Set NOCOUNT OFF
End
GO

That did not work...I also got rid of the DESC in the above procedure, and that too did not work.

Can I get any assistance in this so I can get the sort properly worked out. The sort should be first by that Category listed above, then by : PROJECT.SUBJECT, PROJECT.MANAGER_ID, PROJECT.AUTHOR
Which I have commented out for now, but will eventually need it. Again there is no groupings in my report so not sure why this is not working.

Thanks,
Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-13 : 13:57:46

Try this:


ORDER BY
	CASE
		WHEN PROJECT.CATEGORY = 'Engine' THEN 'a'
		WHEN PROJECT.CATEGORY = 'Transmission' THEN 'b'
		...
	END,
	PROJECT.SUBJECT, PROJECT.MANAGER_ID, PROJECT.AUTHOR

Tara

jhermiz

3564 Posts

Posted - 2005-05-13 : 13:59:31

good idea, let me try that...
I overcomplicate things.

thanks,
Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-05-13 : 14:02:11

Yes m'am

Thanks!

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-05-13 : 14:47:41

errr the sort works fine in SQL Query Analyzer but doesnt return the same results in the report ????

I have no groupings or sortings defined either :(

What in the world!

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-05-13 : 14:49:42

Even the data tab which allows you to run the query in vs.net returns the right data...as soon as I go to preview mode it doesnt :( sort properly

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-13 : 15:05:21

Remember that the preview mode caches the data. Go to the folder where your rdl exists and delete the ReportName.rdl.data file. You'll then get the data from the database and not cached data.

Tara

jhermiz

3564 Posts

Posted - 2005-05-13 : 15:15:41

I had tried that with the same results, the report was fairly simple so I just recreated it!!!
Sheesh TGIF

Thanks

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-13 : 15:26:25

Store the sort order in a table. If you have a table of Project Categories, that is the perfect place to put it.

- Jeff

jhermiz

3564 Posts

Posted - 2005-05-13 : 15:44:16

Ya that is a possible solution as well. This is a database unfortunately we purchased and for now I'm safe to write queries against the database. Don't want to add tables right now, since we are still getting situated.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources