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:

  1. DAX expressions can only be used against Tabular Model or Power Pivot (hosted in SharePoint) as Data source
  2. 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:

  • How to set datasource while creating dataset

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:

  • SSRS Query Designer

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.

  • DMX or DAX Query Designer In SSRS

7.  Smallest DAX tabular expression can be

EXECUTE ‘DATE’

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. 🙂

Advertisements

Posted on July 23, 2012, in DAX, SSRS Reporting and tagged , , , , , . Bookmark the permalink. 1 Comment.

  1. It’s going to be ending oof mine day, but before finish I am
    reading this enormous post to increase my knowledge.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: