Enable Users using report builder to query by state

Question

Enable Users using report builder to query by state

daniarchitect

SSCommitted

Points: 1727
More actions
August 5, 2010 at 2:21 pm

#238628

Hi Guys,
We have a requirment that users who uses report builder to access our corporate report must only see data with related to their state.someone who is in VA should see only VA related data.
the users are not on the same domain.
Have designed the report model,and it is deployed to the report server.
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

How would you determine what state the user is in?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

daniarchitect SSCommitted Points: 1727 More actions · Answer 2

one thing to note , our users are very limited approx. 2 users per state.

please let me know your recommendation.so that I can implement it.

with regard to identifying the users we can get their names and address and implement in any way that can fix this issue.

when they access the report builder,each user must access its own's state data unless he is 'super user' who can access all.

thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

In order to limit the users that way you need to know what State they are in. A simple example would be:

CREATE TABLE dbo.users

(

userid NVARCHAR(128),

state_code CHAR(2),

CONSTRAINT PK_users PRIMARY KEY CLUSTERED (userid, state_code)

)

Go

CREATE PROCEDURE dbo.report

(

@userid nvarchar(128)

)

AS

SET NOCOUNT ON;

SELECT

*

FROM

dbo.report_table AS RT JOIN

dbo.users AS U ON RT.state_code = U.state_code

WHERE

U.userid = @userid;

In this example the users table specifies which states a user can see data from. If they can see data from multiple states then you'd have a row for each state that person can see. If they don't exist in the table then they see nothing.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

daniarchitect SSCommitted Points: 1727 More actions · Answer 4

Jack,

Thanks,

Is there any way that SSRS can do for us to get the user id when he/she gets logged into intranet to access the pre defined report model?

ok as I understand you put how to associate the report data with the user's state.which is fine

if we are using static reports that hits the database each time user logs in but this is report model.

Do you think we should have some interface done to get the user info and send it back to the database.separate report model for each state? or is there a way to have one report model for all states which can change per users credential? and How?

Thanks Again

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Sorry, I forgot we were talking about Report Model. I'm not very experienced with Report Models so I don't know if there is a way to force that to work with one report model.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

daniarchitect SSCommitted Points: 1727 More actions · Answer 6

Anyone out there who can shed some light on?

appreciated