Write DAX queries in Report Builder #ssrs #dax #ssas #tabular
Posted
by Marco Russo (SQLBI)
on SQL Blog
See other posts from SQL Blog
or by Marco Russo (SQLBI)
Published on Wed, 21 May 2014 07:28:00 GMT
Indexed on
2014/05/26
21:53 UTC
Read the original article
Hit count: 342
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 DataSet 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!
© SQL Blog or respective owner