Custom ordering/sorting of records

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

Custom ordering/sorting of records

Author

Topic

dporter
Starting Member

9 Posts

Posted - 2005-11-23 : 08:18:33

Hello all,
I am building a report for sales managers that shows the data for the
salespeople beneath them. They are also a seller under their own management.
The request is to make their sales information display first and then their
salespeople. The report is run based on a report parameter that accepts the
manager id of the manager.
For example:
Bill Gate has a manager id of 19. Bill is in charge of Adrian Murphy, AJ
Kalin, Kevin Buckwalter and Sherin James. ** Names have been changed to
protect identity.**

The report is using a table control and I have a grouping for the seller_name
field. If I set the sort order of the group to ascending, then I would get
the following order:

Adrian Murphy AJ Kalin Bill Gate Kevin Buckwalter Sherin
James

What they want is Bill Gate Adrian Murphy AJ Kalin Kevin
Buckwalter Sherin James

Is there a way to order the items so that the first seller listed would be
the manager, if the manager had any sales for that time period?
BTW, the id is a manager id, not a salesperson id, so sorting by salesperson
id doesn't get what I need. Even if it did correlate, then I believe I'm
stuck with a number sort issue if the manager's id is in the middle of the
ID's numerically.

Can anyone shed some light on how to get the manager's information to display
first?
Any help is greatly appreciated.
Dennis

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-23 : 09:28:33

you can put a CASE statement in the ORDER BY clause...
search here for examples of same...under "custom sort"

dporter
Starting Member

9 Posts

Posted - 2005-11-23 : 09:40:32

quote:
Originally posted by AndrewMurphy

you can put a CASE statement in the ORDER BY clause...
search here for examples of same...under "custom sort"

Thank you for responding. I tried to search the Reporting services forum for "custom sort" and got two results on the non-exact match option (mine and one other). The site timed out when I tried to search all forums.
Can you point me to a specific example? I would appreciate it.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-23 : 09:49:14

how about some sample data and expected results?

chances are, you want to order by two columns:

the first is managerID (or the EmpID of the manager if he IS a manager), the second is whatever you are using normally.

Thus, your order by would look something like this:

ORDER BY CASE WHEN <expression to determine if someone is a manger here> THEN EmpID ELSE ManagerID END, EmpID

The key is -- how do you know if they are a manager? Again, some sample data would help greatly and take out much of the guess work I am doing here.

C

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources