Best way to filter reports?
-
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 #2
Explained 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 code
ELSE
-- role is Manager
Existing report code
JOIN on the tblReporterUser using the ManagerID as the ReporterID
ENDIF
Strategy #3
Is 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,
Ian.
-
This was removed by the editor as SPAM
-
*BUMP*
Just back from a weeks holiday. Hmmm. No replies yet. Shame.
Any ideas greatly appreciated. I'd really like some sort of discussion otherwise I'll have to take a best guess approach to choosing a strategy (I work for a very small company so there's no one else here I can discuss this with - sad ay).
Cheers,
Ian.
-
i believe here the user can be associate with one role there fore it should be a primary key of some other table ....
now the join part ... i operate upon conditional join in this manner
select * from A
Inner join B
on
A.Id = B.Id
and
B.role = 'X'
This does work in SQL Server 2005 .... do let me know in case this does not work out
-
This does work (sort of). If the "role" is "Manager" then I get the right results.
The problem is, if the "role" is "Administrator" then I want no filtering at all - so if the role is "Administrator" I want the SELECT to behave as if it was written like this:
SELECT * FROM A
If the role is "Manager" then I want the Manager to only be able to see the data they have permission to see so I want the select to behave as if it was written like this:
SELECT * FROM A
INNER JOIN tblPermissions P ON P.ManagerID = A.UserID
WHERE P.ManagerID = @CurrentManagerID
(where @CurrentManagerID is the ID of the Manager who is running the report)
Of course if it was this simple I'd just do (this bit is psudo-code)
IF("Administrator") THEN
SELECT * FROM A
ELSEIF ("Manager") THEN
SELECT * FROM A
INNER JOIN tblPermissions P ON P.ManagerID = A UserID
WHERE P.ManagerID = @CurrentManagerID
ENDIF
...but since the SELECT * bit is actually about 150 lines of conditional CASE expressions (could be wrong terminology?) I don't want to "Copy and Paste" this code and have duplicated in 2 places because it'll become a maintenance nightmare over time...
Nothing is ever easy. I guess that's why I have a job
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply