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.
| Author |
Topic |
|
ianmain
Starting Member
7 Posts |
 Posted - 2007-01-18 : 22:46:03
|
Hi,I’m using SQL Server 2000.I need some help on what strategy to adopt in order to filter some existing reports that I have written. I’m basically a web developer (ASP.NET) with some ???? T-SQL skills (therefore I need advice on which is the best strategy please).The business system I’m working on has 2 levels of access: Administrators (there can be several) and Users (20,000+).Users can login and do stuff (do a task).Administrators can login and give tasks to the Users to do. Administrators can also run reports to see the status of the Users relating to a particular task. A report on task X will show a list of all Users who have been given that same task (example of a task: “attend to a conference”) and next to each User will be the status (e.g. “Completed” or “Not yet completed”).That is the existing system (above). Now I need to add a new type of person who can login and view the same reports as the administrator. This type of person will be called a “Manager”. The difference is that the Manager will only be able to view information on a defined set of Users. There might be a few hundred “Managers”.So my “Wizard web page” code that allows an Administrator to create a Manager account will allow the Administrator to select (from a list of Users) which Users this Manager is allowed to see.This means I’m going to have a new TABLE to relate the Manager to the User. I’ll call this the tblReporterUser table.This table will have a ReporterID and UserID (read strategy #1 for reason this is not called “ManagerID”).Here are the strategies I can think of. Which is best or is there one even better?Strategy #1 As well as filling the tblReporterUser with Manager to User relationships, also fill it will Administrator to User relationships.This means that when I call the Stored Procedure to select the rows of my report I need to pass in the ReporterID (which will actually be a ManagerID if it’s a Manager running the report, or an AdministratorID if it is an Administrator).In order to filter Users out of the report I would then use this ReporterID to JOIN against the tblReporterUser TABLE.Disadvantages: Each time Users are added to the system I have to make sure all administrators get permission to view the new User (add a row to tblReporterUser TABLE for each administrator with the new UserID).Also it might make reporting for administrators slower? Strategy #2Explained with some pseudo-code (I don’t like this because of the “repeated code” as its easy to get into maintenance problems):If (RoleIsAdministrator) THEN -- role is Administrator Existing report codeELSE -- role is Manager Existing report code JOIN on the tblReporterUser using the ManagerID as the ReporterIDENDIF Strategy #3Is there a way to do a “conditional” JOIN ON the tblReporterUser if the “role” is Manager (but don’t do the JOIN if the role is Administrator).If there is a way then this sounds like the best solution.I have no idea how to do a conditional join – could someone give me an example please?Massive thanks in advance to anyone replying with any amount of help.Cheers, Chiz |
|
|
ianmain
Starting Member
7 Posts |
Posted - 2007-01-28 : 16:37:59
|
| Hi guys, Just back from holidays. 5 days camping. It rained! (but in Australia that is good news!!!!).I guess this is a *bump* for my thread.If anyone knows anything about "conditional JOINS" I'd really appreciate some help.Thanks,Chiz.Chiz |
 |
|
|
|
|
|
Best approach? How to filter a report