SSRS reports for multiple companies/teams

Question

I'd like some advice on best practice.

I am maintaining an SSRS reports server, and now need to support multiple companies. Each company should only see their own data. And the parent-company should be able to see all data, but also be able to split by the child-companies.

Some of the reports are the same, just looking at different sub-sets of the overall data.

What is the best way to implement this, to ensure data is only accessible by the appropriate companies, and also that I can easily update reports, etc., and add new companies later on?

I'm currently building "core" reports, and then using linked reports with the company name as as a hidden default parameter.

Alternatively, I thought of creating individual databases for each company with the filtered views of the main data. So then instead of the parameter, I would change the Source.

I realize this is an open question, but would like to know the pros and cons from more experienced people, before setting on this.

Answer 1

There are a few ways you could approach this:

• Combined database and select by company
• Schemas within one database to separate the companies. A bit of maintenance required for this.
• Separate databases per company. Aggregation across companies may be complex.

Assuming this isn't a security issue for your internal people who have access to a combined database, the simplest is a combined database and have logic in the reports to ensure people only see the companies they are allowed to.

You have two issues here:

1. Identifying who is running the report
2. Identifying what companies they can run the report for

Reporting Services solves the first problem for you with the @UserId global variable.

I assume you have a table Company that looks like a bit like this:

CompanyId | Name        | ParentCompanyId
----------+-------------+----------------
1         | ABC Inc.    | 
2         | DEF Co.     | 1
3         | GHI Ltd.    | 1
4         | XYZ Corp.   | 

We need a way of mapping that to the companies they can run the report for, so we create a table with UserId and CompanyId fields to map users to their company.

UserId    | Name        | CompanyId
----------+-------------+----------
10        | Alyssa      | 1
20        | David       | 2
30        | Gloria      | 3
40        | Xavier      | 4

So Alyssa can see ABC Inc and the subsidiary companies DEF Co and GHI Ltd, whereas David can only see DEF Co, Gloria can only see GHI Ltd and Xavier can only see XYZ Corp.

In your report you select your data based on the relationships:

SELECT Invoices.CompanyId, Invoices.Number, Invoices.Date, Invoices.Amount
FROM Invoices
INNER JOIN Companies ON Invoices.CompanyId = Companies.CompanyId
INNER JOIN Users ON Users.UserId = @UserId 
                AND ((Companies.CompanyId = Users.CompanyId) OR (Companies.ParentCompanyId = Users.CompanyId))

Okay, that takes care of making sure that the user can only see the data for the company and subsidiary companies that the user is associated with: David can only see invoices for DEF Co whereas Alyssa sees all invoices for ABC Inc, DEF Co and GHI Ltd.

Now, if Alyssa only wants to see DEF Co she needs to be able to select that company. Create a @CompanyId parameter based on this query:

SELECT Companies.CompanyId, Companies.Name
FROM Companies 
INNER JOIN Users ON Users.UserId = @UserId 
                AND ((Companies.CompanyId = Users.CompanyId) OR (Companies.ParentCompanyId = Users.CompanyId))

This shows all the companies the user can select. Set the default value of the parameter to get its value from a query:

SELECT CompanyId FROM Users WHERE UserId = @UserId

Then we just add this parameter to our query:

SELECT Invoices.CompanyId, Invoices.Number, Invoices.Date, Invoices.Amount
FROM Invoices
INNER JOIN Companies ON Invoices.CompanyId = Companies.CompanyId
INNER JOIN Users ON Users.UserId = @UserId 
                AND ((Users.CompanyId = Companies.CompanyId) OR (Users.CompanyId = Companies.ParentCompanyId))
                AND ((@CompanyId = Companies.CompanyId) OR (@CompanyId = Companies.ParentCompanyId))

Report Subscriptions

Now we come to delivering reports by subscriptions and our strategy around using @UserId falls apart because the report scheduler runs reports under its own account, not the user that we want to target.

Let's create a hidden parameter called User and make it the first parameter. For its Available Values property, create a query like the following:

SELECT UserId, Name
FROM Users
WHERE UserId = @UserId OR @UserId = 'MyDomain\ReportingAccount'

where MyDomain\ReportingAccount is the account that your reports run under. This query allows just the specific user details when an actual user is running the report but allows all users when the report scheduler is running it.

Make the Default Value property for your User parameter be =User!UserId. When your report scheduler runs the report, for the User parameter supply the UserId that you want for the person you are emailing.

Thus when the user runs the report, it selects the only possible user (which is themselves) and when the scheduler runs the report it selects the one you want from all available users. Being a hidden parameter, the user never sees it so doesn't know the difference.

If a user tries to be smart and spoof another user by passing it as a parameter in the URL it won't be a valid selection for the parameter's available values and so will fail.

Now just replace @UserId in the queries used above for company selection with @User and everything should work.

Answer 2

I would push the security implementation up a layer to the database, and force access through filtered views. The usual technique is to build a table which lists the companies accessible to each user, then filtering views with an inner join to that table, using the CURRENT_USER function.

This is a more robust solution as it is not dependant on individual report parameter settings. It will also support more deployment methods e.g. SharePoint, Web Apps, Excel etc.

With this in place, your Report Manager deployment becomes very simple - you can give all users the same access to a single set of folders and reports.