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
415 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

786 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