Chapter 45. Creating SSRS reports from SSAS

 

Robert Cain

Many developers, be they .NET developers, database developers, or business intelligence developers, have become accustomed to using SQL Server Reporting Services (SSRS) to create professional-looking reports, using relational databases of all kinds as their data source.

The real power of Reporting Services is unleashed when SQL Server Analysis Services (SSAS) is used as a data source. Reports that show aggregations at the highest level, yet still allow users to drill down to the detail level, can be quickly and easily generated. Many developers have shied away from Analysis Services, assuming they had to learn MDX (Multi-Dimensional Expressions, the SQL-like language used to query an SSAS database). Fortunately, using the tools built into SSRS requires no MDX knowledge.

For those unfamiliar with the concepts behind SSAS, let’s review a few quick definitions before we get started. A database in Analysis Services is often referred to as a cube, due to the way data is stored and referenced. The term measure refers to the values stored in a cube; items such as sales amounts or inventory quantities are examples of measures. Dimensions are typically things like country, product name, date, or anything that measures are analyzed by.

Creating the report project

Adding a shared data source

Creating shared datasets

The report

Summary

About the author