Partager via

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.

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.