Building and deploying Reports in Multi-tenant applications

One of my early challenges in SAAS was to determine the best way to build and deploy reports for our customers. These would include a standard set of reports common to all clients and then customized reports which are unique to a particular customer.
We didn't want to "reinvent the wheel" by writing a reporting engine which many organizations have already done.

I have always been keen on the idea of using Microsoft Reporting Services even in the early 1.x versions due to its tight integration with SQL Server and Analysis Services. It may have been limited in report functionality and suitability to multi-tenant web based solutions, but I found a way to get it all working using the Web Service API and careful management of Report Folders.

With the release of SQL Server 2005, Reporting Services is taken to a whole new level.
With the inclusion of invisible parameters and the ability to use parameter variables in the connection string we were able to come up with a far more elegant and scalable solution to multi-tenant reporting. If there is interest, I will publish details on how we did this.

We are looking at adding more functionality to our reporting module by utilizing more features of RS 2005 such as multi value parameters and drilldown.

We looked at a number of OpenSource reporting products which are good in their own right, but RS 2005 always had the upper hand due to its close fit around SQL 2005.


The end result, is a quick and low risk way of maintaining common reports across multiple clients (only one upload of report is required which complies with the multi-tenant model) and to deploy customized reports to specified customers.