Setting up a Multi-Tenant System

The Basic Setup

We start with a fresh and empty system. (For instructions on how to install ReportServer see the ReportServer Configuration Guide as well as the best practice tutorials for installing ReportServer on Windows and Linux. Or simply use one of the prepackaged installers.)

With a fresh setup, the user management module should contain two folders (or Organizational Units) called Administration and DEFAULT_ROLES, a single user and two groups (see the screenshot on the right). We assume a basic working knowledge of ReportServer's permission and user management system. For an introduction to ReportServer's permission management see the tutorial Getting Started with Permissions as well as the Administration Guide.

The idea with a multi-tenant system is to create an organizational unit per tenant. That is, we would add an organizational unit called Clients to the root folder and then beneath that one unit for each of the clients. Thus, the basic structure could be as follows

root
+-Administration
+-Clients
  +-A
    +-Alice
    +-Andreas
  +-B
    +-Bob
    +-Bill
  +-C
    +-Charly
    +-Chris
  +-D
    +-Dora
    +-Dean
+-DEFAULT_ROLES

Each of the users should be granted the standard user permissions, that is, the user should be allowed to log in to the system, see the Dashboard and TeamSpace module, etc. For this, we are going to use the default role Users which grants these permissions. As the default user role provides a bit too many permissions, we will later need to adapt it, but for getting started, we will add the Clients OU (short for organizational unit) as a member to the Users group (see screenshot on the right).

Adding an OU to a group is equivalent to adding each and every user that is in the OU to the group. Thus, by adding the Clients OU to the Users group we have added all the users, Alice, Andrease, ..., Dora, and Dean, to the group. Also, if at any later point new users (or clients) are added, they directly get the correct permissions.

To test the current configuration we can login as one of the users. For this, we can use the switch user feature. By pressing CTRL+ALT+L (and if the current user has the necessary permissions) we can login as a different user and confirm that, say, Alice can login and see the modules Dashboard, TeamSpace and Scheduler. Once we log out, we will again be logged in with our actual user.

Let us quickly recap, what we have done so far. We have created an organizational unit for each of the clients, we have added the users for each client and we have granted permissions for all clients to access the system. In the next step we are going to set up a basic report and provide access to the report to all clients.

The above SQL is for MySQL. The demodata was generated via http://www.generatedata.com/. The database script to load the demodata used in this tutorial can be downloaded from

• demodata.sql

Before we can create a report, we need to create a datasource pointing to the demodata. In our case, we create a new relational database datasource which we name Client Data Datasource. For more information about how to set up datasources in ReportServer see the Administration Guide.

Assuming the database is correctly set up, we can now create our first report. For this, we go to the report administration module, create a folder with the name Common Reports (which will contain reports that can be accessed by all the clients) and within that we create a new Dynamic List called DemoReport. As datasource we select the Client Data Datasource and as query we enter

SELECT * FROM ClientData

In order to confirm that the report is working properly, we run the report by double clicking it, selecting all available attributes and checking the preview, which should give the following picture (note the ClientID column which contains the information about to which client each record belongs).

Now that we have a report, let's provide access to the report to all of our clients. For this, we are going to create a TeamSpace for each of the clients. Of course, we could also create multiple teamspaces for each client but for this tutorial we go with one TeamSpace per client. To do this, we switch to the TeamSpace module and create the four TeamSpaces called

• Client A
• Client B
• Client C
• Client D

In each of the TeamSpaces we add the corresponding users to become members and import the report that we've created in the previous step.

To once again test that everything is set up correctly, we can switch to one of the users, say Alice, and confirm that she can access the TeamSpace of Client A and that she can there access the Demo Report.

What we have done until now, is that we have created TeamSpaces for each client and added the single report to each of the TeamSpaces. The latter step is of course problematic since now every client can see the data of everyone. We could mitigate this problem by creating four reports instead of one each restricted to the data of one client and then giving each client only the permission to access their report. This, however, creates lots of additional maintenance work which we would like to avoid and in the following section we will explain how we can restrict the view within a single report such that it only shows the data the current user is allowed to see.

A user variable can be considered a property that is put on each user and that can be used by report designers when creating reports. In our case, we are going to use a very simple user variable which for each user will assign the client the user belongs to. That is, for Alice and Andreas the variable will be set to A, for Bob and Bill it will be B and so forth.

User variables are defined in the user management view at the root node. Once you select User root you should see a tab User variable management. This allows us to define what user variables are available. Currently ReportServer supports two types of user variables:

Text variables

Have a single value for each user

List

Allows to define a list of values for each user

In our example, we want to store for each user which client he or she belongs to. We thus create a text user variable with the name client. What we need to do next is to actually set the value for each user.

Naturally, explicitly setting the value for each and every user in the system would be a tedious endeavour. Thus, to make things easier, ReportServer allows you to use the hierarchical structure of the user tree to define variables. That is, you can not only specify the variable's value directly per user but also on an organizational unit. In this case ReportServer scans through all the user's parent OUs to find the value to use. The first one it finds, will be the one taken. This allows you to, for example, specify a default value on the root node and then overwrite this value for certain users or OUs deeper in the user tree.

To assign the users of each of the clients the correct value we set the user variable at each of the client OUs. That is, we select the OU for client A and then the tab User variables. There we select the previously created user variable client and set its value to A. We do the same for each of the other clients.

Now that we have the user variable defined, we need to adapt our report to use it. For this, we go to the Demo Report and open the parameters section. Add a parameter of type User Variable, name it Client ID with key P_CLIENT_ID and then in the specific properties of the parameter choose the previously created user variable client. You might also want to set the parameter to be hidden, as otherwise the user will see a parameter page with the value of the user variable. As this is the only parameter for the report and it is static (i.e., the user cannot change it) it does not make much sense to show it to the user. In other scenarios it might of course be useful to show the users what restriction is in place.

Now we are almost done, all that is left to do is to adapt the report's query to

SELECT * FROM ClientData WHERE CLientID = ${P_CLIENT_ID}

SELECT * FROM ClientData WHERE CLientID = ${P_CLIENT_ID}

Let us quickly show, how we could adapt the permissions in our example. The first step would be to remove the permissions that the default user role grants on users and reports. For this we go to the report root folder and there to the permissions tab and remove the Access Control Entry for Users.

Let us stress that the default user role is nothing static which is fixed in ReportServer but simply a pregenerated group that is meant to simplify setting up permissions. You can (and should) adapt it to you own requirements.

Since we want all clients to be able to access the Common Reports folder we select it, and switch to the permissions tab. We add a new Access Control Entry and select the Organization Unit Clients as folk. Note that, as folk (the recipient of the permission) we can select either individual users, groups, or organizational units. As permissions we grant the read and execute permission.

Next we swith to the user management module and also remove the permissions that are granted to the default user role. Again we go to the root node (in this case User Root) and there to the permission tab to remove the Access Control Entry for Users. We then go to each client, that is, client A, B, C and D and there add an Access Control Entry granting users of the current client to read all other users of the same client.

Access to Multiple Clients

As we've mentioned earlier with our set up only users that have the CLIENT_ID variable set will be able to run the Demo Report. In particular the root user would no longer be able to run the report. We could of course, provide a simple solution to this. We assign a default value, say DEFAULT to the user variable (that is we set a value at the Root node). Then we adapt the report's query to look somewhat like

While this solves the problem for administrators that should be able to see the report in its entirety, it is not clear how to let a particular user see the data for two clients, say A and B. One approach to solve this is to use a user variable of type list instead of a simple text variable. We can then set the variable to 'A' for users within client A, but for our special user we could set it to 'A|B'. (Here note that to enter multiple values in the list variable the values are separated by athe | (pipe) symbol.) When working with a list in the report's query we would then use the $X expression (see the Using Parameters from the administration guide)

SELECT * FROM ClientData WHERE $X{IN,ClientID,P_CLIENT_LIST}