Ad Hoc and Embedded Reporting Solutions

Many of our clients maintain multi-tenant data, where a common data store (such as a set of database tables) houses data for many of their clients.  Each of their clients is considered a “tenant” of the data store.  For reporting purposes, it is of course necessary to restrict access to the data by client.  There are many ways to architect this…we commonly see three:

1) Tenant marshaling is handled by a stored procedure.

2) Mapping parameters to tenant keys in eWebReports.

3) Each client has a separate schema, accessed by changing the connection string.

Number 2 is the most common case, is very simple to implement and is covered in this post.  Numbers 1 and 3 are also fairly straightforward and will be covered in future posts.

Example Data

Consider the following example. A single database houses information for both Exágo University and the School of Reporting Technology (SoRT). The ‘University’ column is used to specify which row belongs to which fictitious college.

Whither eWebReports?

To enable tenant-based access by the ‘University’ column we use an eWebReports parameter.  Parameter values are used fairly frequently in eWebReports for things like runtime filtering, dynamic SQL statements and of course multi-tenancy.  They are defined either in the Administration Console or at runtime using the eWebReports API. In this case we’ll use the Administration Console. We create a parameter “collegeID” and set its initial value to “Exago U”.

For each Data Object listed in the administration console, there is a ‘Tenants’ field in the row for that Data Object.  Clicking in the ‘Tenants’ field for a Data Object brings up a dialog for matching Parameters to Data Columns.  You can add as many as you need to reflect the tenant keys (only one in this case).

Once we complete our actions in the dialog, the Data Object row reflects the new tenant/key matching.

Note: for this example you can ignore the ‘Parameters’ column next to the ‘Tenants’ column; it is used for associating parameter values with programmable data objects like stored procedures or MSSQL table-valued functions.

We save the configuration and eWebReports is ready to report for Exágo U.

Results

Below is an example report design to produce a list of students. Because the parameter value is set to “Exágo U”, only data for Exágo University will appear in the report.

Changing the value of the parameter to “School of Reporting Technology” will cause the report to display only data for students of SoRT.

In an embedded reporting scenario your application can set the parameter value on-the-fly through the eWebReports API, enabling you to base the tenant information on whatever authentication method your application is already using.

Happy Reporting!