Using DAX to create SSRS reports: The Basics

his article is the fourth in a series about the SQL Server Analysis Services (SSAS) tabular model. In the first three articles (one | two | three), we've covered the basic components that make up a SQL Server 2012 tabular database and examined how to create Data Analysis Expressions (DAX) queries to access data in those databases. We also looked at how to use SQL Server Management Studio (SSMS) and Microsoft Excel to create DAX queries that retrieve data from an SSAS tabular instance.

In this article, we move onto a new client application: SQL Server Reporting Services (SSRS). You'll learn how to incorporate DAX into an SSRS report to return and display tabular data. However, as the previous articles illustrate, using DAX is not always a straightforward process. The Report Designer interface makes it easy enough to use Multidimensional Expressions (MDX) statements in your reports, but not so with DAX. In fact, you'll find little indication, if any, that you can use DAX. But you can, and knowing how to do so is essential if you're working with a tabular database configured in DirectQuery mode, which accepts DAX queries but not MDX.