Quick start for SSRS Reporting using DAX
Till now I worked on environments of SSRS reporting through SQL and MDX queries (for getting datasets of report). But I was fascinated by the concept of creating SSRS report by DAX expressions.
Keep in mind few things:
- DAX expressions can only be used against Tabular Model or Power Pivot (hosted in SharePoint) as Data source
- DAX is not a query language, although here we are talking about a work around for getting datasets out of it. We need to understand DAX Query Reference, for writing tabular expressions in DAX. For more details go through the link and anyways I will post more examples on same in my upcoming posts.
So let’s quick start with SSRS reporting using DAXs:
1. Open Visual Studio and create shared data source as in your local, which points to “AdventureWorks Tabular Model SQL 2012” (or any Tabular Model) database. My connection string is
|“Data Source=.;Initial Catalog=”AdventureWorks Tabular Model SQL 2012″|
Name of the shared data source is “AW2012TabularModel”
2. Now create dataset (or shared dataset) for new report.
3. While creating dataset, use shared data source AW2012TabularModel as shown below:
4. Now for query of dataset use Query Designer as shown in snapshot above
5. At first you get an option to create MDX query dataset, but instead of that use DMX query editor as shown below:
6. In DMX Query you will get option to prepare a mining query but it also execute DAX Expressions (so this is the work around!!). For writing DAX query, click on Design Mode view (shown below in screenshot) and write small DAX to test.
7. Smallest DAX tabular expression can be
It will result in all the data from ‘Date’ table from the Tabular Model. Also corresponding SQL will be:
|SELECT * FROM [DATE]|
8. Above query will give all the columns of the table ‘Date’ as fields for the dataset, which can be used further in SSRS report (any tabular, matrix, or chart) and parameters.
So this is how we can start creating SSRS report with DAX tabular expressions (which are responsible for resulting in Datasets). But a question here is that “How to create a report in DAX similar to MDX?”.
Well answer for that will be: Create DAX equivalent to MDX of your requirements. Result of this can be used to create any sort of report. DAX runs as an SQL and to get a result similar to MDX is quite a work. I will cover few topics related to this conversion in coming posts. Please enjoy this post and appreciate if you liked. 🙂
Posted on July 23, 2012, in DAX, SSRS Reporting and tagged DAX against MDX, DAX instead of DMX, DAX query Language, DAX reporting expressions, Reporting with DAX, SSRS with DAX. Bookmark the permalink. 1 Comment.