Sort order for user selection - 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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Sort order for user selection

Author

Topic

Bigglesuk
Starting Member

8 Posts

Posted - 2010-02-02 : 10:56:46

I have sucessfully maanged to create my report in SSRS 2008. I am trying to add some extra functionality.

I currently have a query paramaters that the user can select from to pick a username to run the report for. I would like to put some type of sort order on the list so that their own userid becomes top of the multi select list.

my SQL for the display of the username is:
SELECT ResourceName, ResourceNTAccount, ResourceGroup
FROM MSP_EpmResource
WHERE (ResourceGroup IS NOT NULL)
ORDER BY ResourceNTAccount

(the parameter displays the ResourceName list, but the value is actually ResourceNTAccount)

I have a paramter called @Resource (basically =User!UserID) which holds the ResourceNTAccount for that user. For love not money can I figure how to for make a 4th column called SortOrder and assign the paratmeter user Id a 0 and the rest of the a values a 2 and then sort by that? Am I doing it the correct way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 11:33:19

just give orderby expression like =IIF(Fields!ResourceNTAccount.Value=User!UserID,0,2)

Bigglesuk
Starting Member

8 Posts

Posted - 2010-02-02 : 12:02:00

hello Visakh,

I was under the impression I cannot put code like that in SQL expressions? I've just tried to add it to the Orderby but cant get it to work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 12:15:42

quote:
Originally posted by Bigglesuk

hello Visakh,

I was under the impression I cannot put code like that in SQL expressions? I've just tried to add it to the Orderby but cant get it to work?

oh you were asking about sql expressions. i thought you were asking for ssrs expression. in sql you need to do like

ORDER BY CASE WHEN ResourceNTAccount=@User THEN 0 ELSE 1 END

Bigglesuk
Starting Member

8 Posts

Posted - 2010-02-02 : 12:24:30

Cheers, managed to crack it as you were posting. I ended up with:

SELECT ResourceName, ResourceNTAccount, ResourceGroup, CASE ResourceNTAccount WHEN USER THEN 0 ELSE 1 END AS Sort
FROM MSP_EpmResource
WHERE (ResourceGroup IS NOT NULL)
ORDER BY Sort, ResourceNTAccount

thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 12:30:25

quote:
Originally posted by Bigglesuk

Cheers, managed to crack it as you were posting. I ended up with:

SELECT ResourceName, ResourceNTAccount, ResourceGroup, CASE ResourceNTAccount WHEN USER THEN 0 ELSE 1 END AS Sort
FROM MSP_EpmResource
WHERE (ResourceGroup IS NOT NULL)
ORDER BY Sort, ResourceNTAccount

thanks for your help.

cool
you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources