August 19, 2009 at 1:18 pm
Hi All,
I am new to the SSRS and am not sure if this is the right forum to ask a question about it.. So please point me to the right direction if this is not it...
I have an SSRS project that displays the list of all appointments per user... Based on my query below, users only get to see there own entries and nothing else... I just wanted to add another condition in there that if @REPORTUSER is my manager, he should be able to see everything...
SELECT a,b,c
FROM x,y,z
WHERE
x.id = y.id
AND y.num = z.num
AND u.loginid = @REPORTUSER
FYI: @REPORTUSER is set to User!UserID
I hope I was able to explain the question here... Please let me know if you need details... Would really appreciate your help...
Thanks
Max
August 20, 2009 at 1:58 am
You are going to think I'm crazy... :w00t:
AAAANNNNYYYYWWAAAAAYYYY the code below is based on what i think a report user table would look like. Basically I'm thinking it looks something like this:
PK_ROWNUM UserId UserName isManager ManagerId
with the ManagerId and UserId having a parent child relationship type thing....
Here's the code below for this scenario (please don't laugh) I took 15 minutes out of my day to write this code, which you can execute in SSRS to return the relevant records for your report. BTW - haven't tested this because I don't have such a scenario where I work. :crazy:
create procedure returnUserResults
@REPORTUSER varchar(MAX)
as
begin
declare @ismanager int
declare @i int
declare @myUsers varchar(max)
set @myUsers = ''
set @i = 0
select @ismanager = IsManager from MyUsersTable where userid = @REPORTUSER
if (@isManager = 1)
begin
select @i = count(1)
from MyUsersTable A
where userManager = @REPORTUSER
if object_id('TEMPDB..#MYUSERS') IS NOT NULL
drop table #MYUSERS
create table # MYUSERS (
rowid int null,
userid varchar(20)
)
insert into #MYUSERS
select userid
from MyUsersTable
where userManager = @REPORTUSER
update #MYUSERS
set rowid = a.rowid
from #MYUSERS inner join (
select row_number() over(order by userid)[rowid],
userid
from #MYUSERS
) a on a.userid = #MYUSERS.userid
while (@i > 0)
begin
if @i = (select max(rowid) from #MYUSERS)
begin
select @myUsers = '''' + @REPORTUSER = ''', '
end
else if (@i = 1)
begin
select @myUsers = @myUsers + userid + ''''
from MyUsersTable
where userManager = @REPORTUSER and rowid = @i
end
else
begin
select @myUsers = @myUsers + userid + ''', '
from MyUsersTable
where userManager = @REPORTUSER and rowid = @i
end
set @i = @i - 1
end
if (@myUsers '')
set @REPORTUSER = @myUsers
end
select a, b, c
from x, y, z, u
where x.id = y.id
and y.num = z.num
and u.loginid IN (@REPORTUSER)
end
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
August 20, 2009 at 2:44 pm
maxedout420 (8/19/2009)
SELECT a,b,c
FROM x,y,z
WHERE
x.id = y.id
AND y.num = z.num
AND u.loginid = @REPORTUSER
First, I believe that this syntax is being deprecated in favor of using explicit JOINs.
Try the following code
SELECT a,b,c
FROM x
INNER JOIN y
ON x.id = y.id
INNER JOIN z
ON y.num = z.num
WHERE
u.loginid = @REPORTUSER
OR <logic for manager>
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 20, 2009 at 3:50 pm
Thanks guys for your help... But I think I figured out a work around by using case....
SELECT a,b,c
FROM x,y,z,u
WHERE
x.id = y.id
AND y.num = z.num
AND u.loginid = @REPORTUSER
AND u.id =
CASE WHEN u.loginid not in (managers)
THEN x.id
ELSE u.id
END
Note: I am setting u.id=u.id if the loginid is one of the manager's id, so it gives me the whole list instead of just the ones that match in the 'x' table...
It seems to be working so far.... Keeping my fingers crossed...
Also Drew..Just an FYI: Everytime I tried the way you mentioned: (WHERE u.loginid = @REPORTUSER OR ...) SSRS completely froze on me and I had to restart the app to get back..
Anyway, let me know if I am wasnt able to explain myself properly and I'll try another shot of explaining the problem as well as my workaround clearly for future references...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply