Drilltrough and filtering on SSAS-cubes in SSRS

Abstract

In this article we will describe a technique to create Reporting services (SSRS) reports that use Analysis services (SSAS) cubes as data sources, have a very intuitive interface and take full advantage of SSAS performance optimization features.
Basically the user-friendliness of the interface is achieved by allowing users to drill up/down on the different hierarchies by simply clicking columns or rows, and applying filters by clicking on icons in rows or columns.
The performance is achieved by only retrieving the necessary data from the cube.
An image is better than a thousand words so let’s take a look at what we are trying to achieve: <cfr demo-file in the download-section>


Article structure

The article in divided in four parts, adding functionality (and complexity) in the first three parts and providing some template reports in the last parts that hide most of the complexity.
• Part 1: simple drill trough
• Part 2: performance and filtering on one member
• Part 3: filtering on multiple members
• Part 4: template reports
There is an example report for each part, based on the Adventure works cube.

You can find the complete article, examples and templates in the download section.

This project is intended for information purposes only, there are no garantees on anything you find here.

This project was developed with SSAS & SSRS and VS 2005

Regards
Antoon Vansina

Read more and download article and video here...