Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
422 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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

609 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