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

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.

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.

• Download SQL Server 2016 Report Builder
• Download SQL Server Data Tools – Release Candidate
• Post in the Reporting Services forum (or if you prefer, send us an email)
• Join the conversation on Twitter: @SQLServerBI, #SSRS

Comments

• Anonymous
  March 13, 2017
  This is extremely cool. Would this be enabled to pass a CompanyID sort of parameter thru, so the dataset becomes multi-tenant...ish ?thanks!
  • Anonymous
    April 02, 2017
    Yup, the query designer supports query parameters. (Note that DAX supports only single-valued parameters today; we're looking into what we could do for multi-valued parameters.)
    • Anonymous
      April 05, 2017
      Riccardo,By any chance is there an estimate on when the multi-valued parameters will be supported in the designer view?
      • Anonymous
        April 07, 2017
        It's something we need to revisit and investigate further, so no timeframe to share at this time.
• Anonymous
  March 15, 2017
  This is a game changer for us! We still have non-SQL/DAX savvy users on Report Builder using Report Models created in 2008R2. This gives us a path forward using tabular models!
• Anonymous
  March 22, 2017
  Hey if anyone is reading this..... please let's talk about some ssrs needed features:1) JSON as a dataset and nested collections in json as parameters to sub reports.2) web services as a datasource3) a client report view that is not tied to web forms and win forms, time for a true html 5 view control that can plug in to MVC , angualr and such.
  • Anonymous
    April 02, 2017
    Thanks for the feedback, Denny!
• Anonymous
  April 06, 2017
  How about adding Excel services rendering into SSRS? Microsoft already has the technology. Interactive Excel documents work in Sharepoint and Office Online Apps. Then we would have PowerBI, SSRS, and Excel as web reporting tools to our on premises databases.
  • Anonymous
    April 07, 2017
    Yup, we've been focusing on Power BI reports first, but we do plan to integrate web-based viewing of Excel workbooks in future.