Reporting Services Deployment Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Reporting Services Deployment Question

Author

Topic

matt.riley
Starting Member

2 Posts

Posted - 2009-03-03 : 07:34:38

Hi,

I have developed a number of reports for my business and need to deploy them in a certain manner.

We have 200+ stores and each store needs to see the same report but containing that store data only.

The data has a Store ID within. We cannot allow store 123 for example to view store 67's data.

All users from each store are within an AD group for their store such as Store123.

I want the end user to be able to run the report and for only their store data to be shown. I may be reading the books wrong but it seems like i have to deploy 200 copies of the report in 200 folders with custom parameters and then give the AD group permissions to view that folder.

Is there a quicker way of doing this?

Kind Regards,

Matt Riley

jester
Starting Member

1 Post

Posted - 2009-03-03 : 12:18:12

Hi,

You can use suser_name() in your query. This will give you the active directory account name on the sql layer and then you'll just need to associate the store with the account.

e.g.

SELECT * FROM STORE
WHERE STORE.AccountName=suser_name()

To see your AD name try SELECT suser_name()

Warning: you need to ensure your connection to the database uses active directory and not sql server logon.

Regards
Tony

ChrisBui2008

3 Posts

Posted - 2009-03-03 : 12:18:47

Here's one method.
Let's say you have a table called Store that contains the user name and store number. Use the user name to associate the store number he/she works at. The SYSTEM_USER function identify's the user who is loged into the computer.

SELECT Daily, WTD, MTD, YTD FROM SALES
WHERE StoreID = (SELECT StoreID FROM Store WHERE UserName = (SELECT SYSTEM_USER))

Chris Bui

kmarshba
Starting Member

24 Posts

Posted - 2009-03-03 : 13:03:59

You still have options if you are either using Windows Authentication with a dedicated, impersonated account or a SQL login for the data source from SSRS to SQL.

You can pass the username for the user logged on to SSRS as a parameter to the report procedure.

Create an internal parameter on each report with a default value of User!UserID.
This will grab the fully qualified user name that is authenticated against SSRS, e.g. DOMAIN\USERNAME.

Use that report parameter and pass it as a parameter to the stored procedure.

Use the sproc parameter to find the appropriate store value and filter the data for the query.

You would need a table that holds the StoreID and UserName combinations (I would design this out MUCH more, but this is a simple, non-optimized example. you can normalize the schema as you desire).

Optimizing the example ChrisBui2008 gave:

SELECT Daily, WTD, MTD, YTD
FROM StoreSales ss
INNER JOIN StoreUsers su
  ON ss.StoreID = su.StoreID
WHERE su.UserName = @UserName

If you ARE passing Windows Authentication for your SSRS data source (not a dedicated account but whomever is logged on to SSRS) then go with SYSTEM_USER, but write the query this way:

SELECT Daily, WTD, MTD, YTD
FROM StoreSales ss
INNER JOIN StoreUsers su
  ON ss.StoreID = su.StoreID
WHERE su.UserName = SYSTEM_USER

If you provide more detail on your system setup we can give you a better suggestion.

Kevin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 13:08:01

or just use same query for all users and do filtering in report. just add an internal paremeter and give default value as suser_sname() and use it for filtering to show only his relevant data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources