Services

Integrating Reporting Services with Analysis Services

Microsoft's Reporting Services can be a great compliment to Microsoft's Analysis Services. OLAP Cubes are great for "analysis" but they lack functionality when it comes to retrieving raw data. Now that Reporting Services is available as a free addition to SQL Server, companies can use both products to compliment each other.

Here are a few examples of how Reporting Services can be integrated with Analysis Services Cubes:

Display OLAP Reports along with Relational Reports on the Reporting Services Server: Both ProClarity and Panorama have add-ins that allow companies to pull OLAP views into Reporting Services. These reports are pulling their data from the OLAP cubes. Both products feature live links that allow users to "link to ProClarity or Panorama" for further analysis if the static report does not fulfill their request. This allows for scheduled reports and gives the user the option of "digging deeper" but clicking on the link to the analytical web server.
Display Detailed Reports based on a member in a Cube: Reporting services allows for parameterized reports. This makes it very simple to link Analysis Services cubes via the use of Actions. An action can be set up in a cube which passes the "current member" parameter to a Reporting Services report. Example: When a user highlights a customer name within the cube and activates the "action", then a Reporting Services report is displayed which shows the Customer name, address, city, sales rep, annual sales, etc. The data for the Reporting Services report is not being pulled from the cube, but rather from the base relational source. This allows for a lot of flexibility.
Drill to the Relational Detail Data via Reporting Services: Analysis Services supports "drill to detail" which allows users to drill back to the relational source and pull all detail records that comprise a transaction. Although this does work, the functionality is pretty limited. With the help of reporting services, you can now pull up a nicely formatted "drill to detail" report, rather than having to rely on Analysis Service's "drill to detail" functionality. This is accomplished by setting up a parameterized report in Reporting Services and then creating a cell based action that takes the co-ordinates of where the user is in the cube and passes it to the reports. Note: The client tool must allow for CELL based actions for this to be accomplished using this method.