Setting up a Multi-Tenant System

In this tutorial we discuss how to set up a multi-tenant system in ReportServer.

ReportServer is built upon a very flexible permission scheme that allows to easily handle even the most complext requirements. For this tutorial we are going to consider the following scenario: A software vendor SV provides a set of services and wants to use ReportServer to allow its clients to access their data. We will demonstrate how to set everything up, so that the four clients A, B, C, and D get access. The data is structured such that every table in the datawarehouse is extended by an attribute CustomerID which specifies for which client the data record is.

The design goals of our setup are twofold:

1. Each client should get access to the system and to their data (and only their data).
2. We would like to reduce maintenance and administration overhead on the IT team of our software vendor SV as much as possible. In particular, reports should be generated only once and then be used for all clients. That is, the reports should not be duplicated for each client, but ReportServer should ensure that when client A is running a report that only the data for client A is included.

In order to set up the system we are going to use ReportServer's hierarchical model of users and ReportServer's user variable concept. In the following we discuss how to structure users in order to simulate separate sub systems for each client. We then discuss how to use user variables within reports to allow the reuse of reports across clients. Finally, we will discuss some more advanced use cases.

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.

Adding Basic Reports

In the following we will create a report and provide access to the report to all clients. The report will be a Dynamic List on top of a database table called ClientData which has the following structure:

CREATE TABLE `ClientData` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `City` varchar(255),
  `Name` varchar(255) default NULL,
  `Company` varchar(255),
  `SomeDate` varchar(255),
  `SomeNumber` varchar(13) default NULL,
  `ClientID` varchar(255) default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

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.

Note that there are various ways to structure TeamSpaces. For example, if the clients should only get read access, one could add them to their TeamSpace with the guest role. If, on the other hand they should be able to make changes then the role of user would be more appropriate. (For an introduction to TeamSpace roles see our User Guide.) Both settings, however, require that when a new user is added to the system an administrator needs to place the user into the correct TeamSpace. By giving one ore more users in a TeamSpace the role of Manager you can pass this administrative task to the client. Finally, if you are running ReportServer Enterprise then the whole set up and maintenance of TeamSpaces can be automated via scripts. We will get to this during the discussion of advanced use-cases.

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.

Reporting with User Variables

Our Demo Report was set up with the simple SQL query

SELECT * FROM ClientData

Using parameters, we could, for example, add a dropdown box to the report which allows to select a client and then only show the data for that particular client. Assuming that we name the parameter P_CLIENT the adapted query could be:

SELECT * FROM ClientData WHERE CLientID = ${P_CLIENT}

Of course, adding a selection box to choose which client's data one wants to see does not solve the problem that in the current setup client A can see also the data of client B. The idea that we will use to restrict the access of each client will, however, use a similar approach. We will use a parameter to restrict the data records returned by the query, but we will not allow the user to select the parameter. Instead the parameter's value is automatically assigned depending on which user is currently logged in. The concept that makes this work are ReportServer's User Variables.

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}

Let's test it. If we now log in with Alice and execute the report we expect to only see records for Client A.