April 10, 2011 at 12:59 pm
Hi
This question may be obvious to some of you, but it has caused me no end of headaches.
I have 3 levels of access required to view different query results based on the current logged in user and their team/group.
Director: Can view all team sales & all team member sales
Manager: Can view all team member sales within their team
User: Can only view their indivdual sales
I have a table which holds the above details and can map this as a datset in report builder.
I understand the concept but I am struggling applying this in report builder 3 (SQL2008 R2).
Any help is greatly appreciated and am happy to use Adventureworks as a starting point..
Thanks in advance
James
April 11, 2011 at 1:59 am
It sounds like it needs to be recursive, so you either need a stored procedure on the database, or in the report builder switch to "Edit As Text" and build a T-SQL statement using a CTE by hand (as the query builder can't use CTEs).
If your tables have the domain\username stored for each user who will run the report, then you can match it against the User.UserID provided by RS of the current user running the report as in the sample below; otherwise you could pass in the username as a parameter and match to that instead. Each of those people in the table also need a link back to their manager in the same table ...
; WITH CTE_SalesPersonIDList
AS (
-- Get the user who is running the report
SELECT SalesPersonID,
ManagerID
FROM SalesPerson
WHERE SalesPersonID = USER.UserID
UNION ALL
-- Recurse all the users for whom they are the manager, and so on...
SELECT SalesPersonID,
ManagerID
FROM SalesPerson
INNER JOIN CTE_SalesPersonIDList
ON SalesPerson.ManagerID = CTE_SalesPersonIDList.SalesPersonID)
SELECT Invoice.*
FROM Invoice
WHERE Invoice.SalesPersonID IN (SELECT SalesPersonID
FROM CTE_SalesPersonIDList)
April 12, 2011 at 2:08 pm
Hi,
Thanks for the response. Although I didnt use your solution, it did ignite a spark in my brain to get the issue resolved.
I flagged each account as either a manager or a user along with a join indicating who the manager was. If the manager didnt have a manager, the flag was linked to themselves.
Thanks for the inspiration.
James
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply