Reporting Services: Read Data from SSAS and SQL Server in One Dataset

In my last article, Reporting Services: Adding extra columns / rows to a matrix I showed you how to add an extra column to a matrix. The basic idea was to write a stored function that retrieves the data in the way it is needed. This allows you to expand the standard functionality of a matrix which only supports subtotals and totals.

You can use a similar method to solve another common problem: Reporting Services cannot join 2 data sets. If you want to build a table or a matrix which holds data from two different SELECT-statements, you have two options:

1. You could use sub reports which suffer from poor performance and limited export functionality (Excel).
2. You could develop a stored function to bring together all the relevant data. Then, you have only one SELECT-statement - from Reporting Services' perspective.

In this article, I want to show how to use the second method to retrieve data from Analysis Services and a SQL Server relational database in one dataset. As you can imagine, this leads to many new possibilities.