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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS Problems 9 57
BULK LOGGED - log full 9 25
Safely Uninstall SQL Server 2008 R2 Express 3 60
SQL Server Data Tools for Visual Studio 2015 - backward compatibility. 2 83
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 demonstrates a quick way of adding group price to multiple Magento products.
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…

919 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

14 Experts available now in Live!

Get 1:1 Help Now