THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Write DAX queries in Report Builder #ssrs #dax #ssas #tabular

If you use Report Builder with Reporting Services, you can use DAX queries even if the editor for Analysis Services provider does not support DAX syntax. In fact, the DMX editor that you can use in Visual Studio editor of Reporting Services (see a previous post on that), is not available in Report Builder. However, as Sagar Salvi commented in this Microsoft Connect entry, you can use the DAX query text in the query of a Dataset by using the OLE DB provider instead of the Analysis Services one. I think it’s a good idea to show the steps required.

First, create a Data Source using the OLE DB connection type, and provide the connection string the provider (Provider), the server name (Data Source) and the database name (Initial Catalog), such as:
Provider=MSOLAP;Data Source=SERVERNAME\\TABULAR;Initial Catalog=AdventureWorks Tabular Model SQL 2012


Then, create a Dataset using the data source previously defined, select the Text query type, and write the DAX code in the Query pane:


You can also use the Query Designer window, that doesn’t provide any particular help in writing the DAX query, but at least can show a preview of the result of the query execution.


I hope DAX will get better editors in the future… in the meantime, remember you can use DAX Studio to write and test your DAX queries, and DAX Formatter to improve their readability!

If you want to learn the DAX Query Language, I suggest you watching my video Data Analysis Expressions as a Query Language on Project Botticelli!

Published Wednesday, May 21, 2014 10:28 AM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



SQLBI - Marco Russo : Use parameters in your #DAX queries said:

May 21, 2014 5:58 PM

Geoffrey said:

Did not know this option. One small correction: first create a "data source".

May 28, 2014 2:09 AM

Marco Russo (SQLBI) said:

Fixed - thanks!

May 28, 2014 2:24 AM

Dave said:

Marco, thanks for this tip.

Do you know if the SSRS Aggregate function will work using this data source connection with a DAX query?

To improve performance, I am trying to convert an existing SSRS multi select parameter report that uses the designer MDX query to instead use a DAX query as the data source.  Initial testing shows that my DAX query has better performance and I was also able to set up the multi select parameters.  However, the current report does not display detail row data and utilizes the aggregate function to display sum and average measures across various time periods such as Month, QTD, YTD and previous YTD (Month being one of the multi select parameters).  In using the DAX query with the DMX editor, the aggregate function is not supported.

Is there a way to reproduce the server aggregates that MDX creates with DAX to utilize the aggregate function?  Or an alternative suggestion to replicate this aggregation behavior in SSRS without having to re-write aggregation expressions in SSRS?

Thank you for your assistance.

October 30, 2015 12:25 PM

Dave said:

I was able to resolve my SSRS multi select parameter aggregations by simple including an ALLSELECTED filter on Year/Month for my measures.  Thanks again for sharing your knowledge of DAX.

November 2, 2015 11:10 AM

Leave a Comment


About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement