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
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
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.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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