Query designer support for DAX now available in Report Builder and SQL Server Data Tools

  • Article

image

Today, we’re pleased to announce a long requested feature from the SQL Server Reporting Services community.  With the latest releases of Report Builder and SQL Server Data Tools – Release Candidate, you now have the ability to create native DAX queries against supported SQL Server Analysis Services tabular data models.  This includes the ability to use the query designer in both tools to drag and drop the fields required for your query and have the DAX query generated for you vs. writing it manually.  Please note that the ability to use the query designer for DAX is limited to SSAS tabular data sources built in 2016+ only.

How to get started

Once you’ve downloaded the latest development tools, you’ll find the new functionality available either when building a shared dataset or an embedded dataset against a SQL Server Analysis Services 2016+ data source in the built-in query designer.

image

Once the query designer is opened, you’ll see a new dropdown menu that allows you to select to either have your query generated in DAX or MDX against your data source.

image

If you select DAX, when you drag and drop your fields from the Metadata tab into the query designer, a DAX query will be generated behind the scenes.

image

You can see this underlying DAX query text by toggling the design mode from graphical to text.

image

You may switch between DAX and MDX by simply changing the option in the dropdown menu.  You will be warned that by doing so, you will need to regenerate your query.

image

If you are using either a 2012 or 2014 SSAS tabular model as your data source, while you can’t use the graphic designer, you’re still able to use the text editor and write DAX queries against those sources.  Simply select DAX from the dropdown and write your query accordingly.

image

In either scenario, any reports or datasets you build using this functionality can be saved and used with SQL Server 2016 Reporting Services instances or later, including the current preview releases that support Power BI reports.

Try it now and give us your feedback!

We’re excited to hear your thoughts on this new query design experience and how we can make this new functionality even more valuable for you moving forward.