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

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
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)



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

Marco Russo (SQLBI) said:

Hi Dave, thanks for the feedback  sorry for my delay in answering, I have a large backlog that I'm trying to recover in these days!

December 27, 2015 3:24 PM

Marco Russo (SQLBI) said:

Hi Dave, thanks for the feedback  sorry for my delay in answering, I have a large backlog that I'm trying to recover in these days!

December 27, 2015 3:24 PM

Dennis said:

Hey Marco,am stack.Am trying to add my measures using dax from my model

to my dataset in SSRS.Can you please direct me on how to go about it?

May 26, 2016 2:36 AM

Jim said:

Can you use query parameters with this method?

July 21, 2016 8:57 AM

Marco Russo (SQLBI) said:

@Dennis: not sure about what you mean.

@Jim: there are limitations, see notes here:

July 21, 2016 3:23 PM

Dan said:

It appears that SSRS 2016 does not support the use of SelectedValue in dax or else my connection string provider is incorrect.

January 4, 2018 6:31 AM

Marco Russo (SQLBI) said:

You need SSAS 2017 to use SELECTEDVALUE - what is the version of SSAS Tabular that you are querying?

January 5, 2018 6:16 AM
New Comments to this post are disabled

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



Privacy Statement