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.
- Download SQL Server 2016 Report Builder
- Download SQL Server Data Tools – Release Candidate
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.
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.
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.
You can see this underlying DAX query text by toggling the design mode from graphical to text.
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.
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.
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.