Order By when click on header - 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

Order By when click on header

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-03 : 15:22:22

Is there a way to let the user order the reports ascending or descending when they click on the header of each field? Right now I just have it order by id, but what if the user wants to order by name or department. Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-03 : 15:24:23

You would need to do this with a parameter. Then your query would accept the parameter and use it in the order by. Otherwise, you'd need to write a web page to do this instead of using Reporting Services.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-03 : 16:17:32

So do I just do an order by using my parameters in the query? Or how would I do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-03 : 16:23:55

You'll need to use a dynamic ORDER BY:

http://www.sqlteam.com/item.asp?ItemID=2209

Make sure to check out the comments in this article though as it discusses data type issues and how to resolve it.

Tara

jhermiz

3564 Posts

Posted - 2004-12-04 : 19:54:04

Hey chris take a look here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_tutorials_v1_7qr7.asp

Jon

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-09 : 16:52:17

thanks, I will look at this link.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-10 : 13:29:26

Tara,

I tried the link you provided and was able to get it to work somewhat, but I'm also getting some error.

An error occurred while reading data fromt he query result set. Syntax error converting datetime from character string.

Here's my stored procedure:

CREATE PROCEDURE SP_CUSTOMER
@STARTDATE NVARCHAR(10),
@ENDDATE NVARCHAR(10),
@SORTORDER TINYINT = NULL
AS
SELECT CUSTOMER.ID, CUSTOMER.NAME, CUSTOMER_ORDER.ORDER_DATE, CUSTOMER_ORDER.SHIPPED_DATE
FROM CUSTOMER
WHERE CUSTOMER.ID = CUSTOMER_ORDER.ID
AND
(CUSTOMER.DATE BETWEEN @STARTDATE AND @ENDDATE)
ORDER BY CASE
WHEN @SORTORDER = 1 THEN CUSTOMER.ID
WHEN @SORTORDER = 2 THEN CUSTOMER.Name
WHEN @SORTORDER = 3 THEN CUSTOMER_ORDER.ORDER_DATE
ELSE CUSTOMER_ORDER.SHIPPED_DATE
END DESC

Any help or advise?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-10 : 13:32:43

You'll need to use multiple CASE statements in the ORDER BY to handle the different data types. So you'd have one for varchar columns, one for int columns, and another for datetime columns. Take a look at the comments of the article for more information.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942

Tara

mprolli
Starting Member

24 Posts

Posted - 2004-12-10 : 13:42:26

well, Is this being passed from an ASP page?? if so, why don't you declare the varaible = to the column name.

CREATE PROCEDURE SP_CUSTOMER
@STARTDATE NVARCHAR(10),
@ENDDATE NVARCHAR(10),
@SORTORDER NVARCHAR(50)

AS
Declare @sql1 varchar(8000)
set @sql = '
SELECT CUSTOMER.ID, CUSTOMER.NAME, CUSTOMER_ORDER.ORDER_DATE, CUSTOMER_ORDER.SHIPPED_DATE
FROM CUSTOMER
WHERE CUSTOMER.ID = CUSTOMER_ORDER.ID
AND
(CUSTOMER.DATE BETWEEN '''+ @STARTDATE +''' AND '''+@ENDDATE+''')
ORDER BY '''+@sortorder+''''
Exec (@sql1)

Let us rise up and be thankful, for if we didn't learn a lot today, at least we learned a little, and if we didn't learn a little, at least we didn't get sick, and if we got sick, at least we didn't die; so, let us all be thankful.
--Buddha

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-10 : 13:46:07

Please don't use mprolli's solution as it is using dynamic sql which is a performance and security issue.

Mprolli,

There is a non-dynamic solution available which you can find in those links that I posted.

Tara

jhermiz

3564 Posts

Posted - 2004-12-10 : 13:48:05

Err what happens when I change the field in the db name ? I'd have to change it on the code pages as well. Dont like that idea.

mprolli
Starting Member

24 Posts

Posted - 2004-12-10 : 13:48:41

Sorry, just trying to help, I'm new to SQLteam...however, why would the order by create a security issue? (just wondering), not to mention the fact that if you use the case statements, and your table structure ever changes you'll have to update your every Sp that you have this order by Case statement. If your truely concerned about SQL injection, why wouldn't you limit it by Saying "....set @sortorder = (select [name] from syscolumn c join sysobject o on c.[id] = o.[id] where c.[name] like '''+@sortordername@+'''....."

Let us rise up and be thankful, for if we didn't learn a lot today, at least we learned a little, and if we didn't learn a little, at least we didn't get sick, and if we got sick, at least we didn't die; so, let us all be thankful.
--Buddha

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-10 : 13:50:10

Dynamic SQL requires permissions on the tables and not just EXEC on stored procedures. One of the main reasons to use stored procedures is that you only need to grant EXEC on them. But when you add dynamic SQL to the mix, then you now need those permissions on the tables too. This isn't a good security model.

Tara

mprolli
Starting Member

24 Posts

Posted - 2004-12-10 : 14:02:16

But, if this is being passed from an ASP page, which is written internally, wouldn't that Login (applicationlogin) have permissions on that table anyway?

Let us rise up and be thankful, for if we didn't learn a lot today, at least we learned a little, and if we didn't learn a little, at least we didn't get sick, and if we got sick, at least we didn't die; so, let us all be thankful.
--Buddha

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-10 : 14:05:25

It shouldn't. That's one of the main reasons to use stored procedures: you don't need permissions on the tables (unless you use dynamic SQL).

He is using Reporting Services rather than ASP, but the answer is still the same.

Tara

jhermiz

3564 Posts

Posted - 2004-12-10 : 14:08:13

I think what she's trying to get at is why does your login user have access to every table ? I think creating a general person in this case for reporting and giving rights to the procedures is a much more organized and cleaner solution? At least it is on the reporting side.

mprolli
Starting Member

24 Posts

Posted - 2004-12-10 : 14:13:00

Seeing as though, you are "Soon-to-b-SQL-Warrior-Queen" and have obviouslly, contributed alot to this site, and I'm the new guy.. You can have this one.

.. But for the record, my applicationlogin, doesn't have access to everytable, just those that is queries.

Let us rise up and be thankful, for if we didn't learn a lot today, at least we learned a little, and if we didn't learn a little, at least we didn't get sick, and if we got sick, at least we didn't die; so, let us all be thankful.
--Buddha

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-13 : 16:29:43

I'm kind of new to sql and reporting services, so I'm not too sure about security issues, but I will play around with the given code and see. Thanks for all the comments and help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-13 : 16:31:05

I would not recommend playing around with the given code as there already is a non-dynamic SQL way of doing this.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-12-13 : 16:55:56

Thanks, Tara for the caution. Yes I was able to find examples that you provided from the link. Though I'm curious if there's a way to allow the user to sort by if they click on like the column heading. For example if they click on heading ID, then it will sort it. Not too familiar with Reporting Services to know if it will support this feature.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-13 : 16:58:43

Reporting Services does not allow this kind of functionality. You would need to write your own web page if you wanted this.

Tara

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources