Solved

How do I lookup a field, compare to two tables and then enable / prevent access to Drill-through report?

Posted on 2013-01-30
3
413 Views
Last Modified: 2013-02-04
Sample Report Output
On the left is a list of teams. The rightmost column contains a count of errors, if you click on count of errors you will drill-through to a detailed report that lists individual errors.

The report works and is fully functional!

However, I am trying to apply a security model that will restrict users to only view detailed error data for their own teams.

To illustrate: members of the Core Team can view details of the 943 errors but cannot view details of any details for the Adult Antenatal team.

Question:
I have created a lookup table that lists all users and the TeamCodes they can access.
I also have a table that lists all TeamCodes e.g. VHCAB and their corresponding Team Names e.g. CORE

Any thoughts on how I can look up the CurrentUser, lookup the team name compare it to two tables and validate if they can access the drill through or not?

TABLE LAYOUT
SSRS Layout
0
Comment
Question by:JohnAeris
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
liija earned 500 total points
ID: 38836402
Since you have the tables, you might join them in query.

SELECT
 Errors,
 Errors.Team,
 Users.UserID
FROM Errors
 LEFT OUTER JOIN Users ON Errors.Team = Users.Team
 AND Users.UserID = @UserID

It'll add parameter UserID on the report.
Set the default value of parameter UserID =User!UserID (built in field)
-> set the parameter ashidden.

Now UserID on your dataset has value NULL, if the user doesn't have access to the team data, and value current report user id if the user has access.

Next add expression to the link and font color,
=Iif(isnothing(Fields!UserID.Value), "Black", "Blue")
and link
=Iif(isnothing(Fields!UserID.Value), Nothing, yourlink)
0
 

Author Comment

by:JohnAeris
ID: 38851378
Done!
liija mate, thanks for your input. I realised that I had over complicated things and your input simplified my thinking.

thanks! :)
0
 
LVL 6

Expert Comment

by:liija
ID: 38851424
You're welcome.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installatiā€¦
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Itemsā€¦

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now