SQL Server Reporting Services Report Builder with DAX Query Support

By:   |   Comments (4)   |   Related: > Reporting Services Report Builder


I want to use DAX in Report Builder and wasn't sure if this can be done or how to do this.  Is it possible to use DAX in Report Builder?


With all the excitement about Power BI and its ongoing development, Report Builder sometimes lags in the attention category. However, development continues on Reporting Services (SSRS) and specifically the Report Builder tool. Although the development is not as swift as Power BI, Report Builder continues to receive enhancements and updates. One recent update in Report Builder added functionality to provide a report designer with the ability to write and handle DAX related queries. Furthermore, Report Builder can do the heavy lifting for you by using the GUI to design the DAX query instead of hand coding the DAX details.

However, before we get started, you may be wondering what DAX is and how and why might it be used. The following three tips / tutorials by Siddharth Metha and Daniel Calbimonte offer great introductions into the DAX world:

You might also be wondering why this new functionality matters. Simply put, added GUI functionality provides an efficient way for Business Users to quickly connect and develop a data source against a SSAS database. You certainly can hand code each DAX query especially with the use of various third-party tools available online. However, for many business users who have access to their SSAS database via Report Builder, having the report designer built into the tool is a significant advantage and provides an integrated approach to report development.

As with any tool, there are a few caveats to its use. First, the ability to utilize this functionality is only available for use with SSAS tabular models, and not multidimensional models. Additionally, these tabular models must be using SQL Server 2016 or above.

Furthermore, to get started you will need the latest version of Report Builder which is available at: https://www.microsoft.com/en-us/download/confirmation.aspx?id=53613.  Additionally, a functioning version SQL Server Reporting Service 2016 or higher is needed to publish the report online, and SQL 2017 Report Server version can be downloaded from: https://www.microsoft.com/en-us/download/details.aspx?id=55252.

Report Builder and DAX

To begin the development process, we first must open Report Builder and create a new report by clicking on the Blank Report option.

New Report

Once a new report is created, the following step is to create a new data source. For this example, we will create a data source connecting to a local SSAS Tabular database.

New data source

The data source must be assigned a name and we must select "Use a connection embedded in my report" as shown below. Next, from the Connection Type List, Microsoft SQL Server Analysis Services should be selected and then the server and database (Initial Catalog) are added to the connection string.  You can use the build button to complete this last step also. When finished entering information you would click OK.

SSAS Datasource

Now, we must create a data set based on this newly created data source.

New Data Set

Similar to the data source, we must define the data set name, select "Use a dataset embedded in my report", and then select the Data Source we just created with the SSAS data type. Finally, we can "design" our query by selecting the Query Designer option.

Enter Dataset Detail

Upon selecting Query Designer, the query window opens, similar to the below screen print. This next step is very important. The query type selection box must be set to DAX as illustrated in the subsequent screen print. If this option is set to MDX, we will get MDX queries instead of DAX queries.

DAX / MDX Query Type

You will notice that the Object Explorer is available on the left side to allow for drag and drop designing of a DAX query. Like the MDX design process, and as shown below, measures and dimension values can be dragged into the main grid or the filter area. Upon selecting all the proper values, OK can be selected to keep the designed query.


Now we have a simple DAX query completed. For those familiar with DAX, you will notice the commonly used EVALUATE and SUMMARIZECOLUMNS commands in the below command.

Finished DAX Query

Clicking OK one more time brings us back to the main report design grid in Report Builder. From here we can easily use the new query as a source for a report, similar to the one shown below.

New Report Design
New Report Result

Our next steps shows the true power of using the DAX designer to create a much more complicated DAX query with filters and parameters and multiple dimensions. Thus, our first step is to right click on the dataset and then select Query for the query designer.

Query Designer

Our previously created query opens in the design mode. We now will make several additions to our previous query by adding the calendar year, quarter, and month along with the Customer Education to our result set. Next, we will drag Education to the filter area and set it as a parameter by checking the parameter box.

Query Designer with Parameter

Now reviewing the query in the below screen print, we can see that the new DAX query is significantly more complicated than our previous query.

Complicated query result

However, the query does handle all the dimensions and measures being added along with creating a parameter in the report, the Education Level field was added as filter. I will note that I did make two changes to the parameter that was created; first I remove the check mark next to the Allow blank values option. Second, I removed the check mark next to the Allow multiple values option. I had difficulty getting the multi-value parameters option to work with the designed queries outside of the All option (note as of this tip’s writing, it appeared this option was still being worked on, but was available in Power BI and would at some time be developed for SSRS and Report Builder).

New parameter

Also, you will note that default value for the report parameter is set to "0|". This value signifies the level and value to be used (remember we are using a SSAS database dimension which notes a hierarchy style of selecting values).

default parameter 0

We could easily change it to level "1|Bachelors" which would use a default parameter value of Bachelors.

default parameter 1

After making a quick addition to the report to display the year on the column axis, we can now see a report which pulls our sales and margin data by commute distance and year all while using an Education Level parameter which is currently set to default as "Bachelors".

ssrs report builder dax 017

One other caveat I have noted about adding parameters in the Query Designer is upon switching to the text view of the query designer and attempting to execute a query returns a Query Preparation Error which is illustrated below. Unchecking the parameter option allows the query to run without error in both views. Hopefully, Microsoft will correct this item soon.

manual designer error

Using the new built in DAX query designer for queries against a SSAS tabular database allows for a much quicker and somewhat cleaner design process.  Designing similar queries by hand would take significant longer than using the designer. Of course, as with any design tool, you need to be careful to review the query that is designed for you to make sure it makes sense and is performant for your environment. The designed query can be easily modified to accommodate changes such as not allowing blanks.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, July 10, 2023 - 4:24:45 AM - Naveen Kumar Back To Top (91372)
My connection type is Microsoft SQL Server. I don't have option to choose DAX in Query Designer.
Option of DAX is only limited to Microsoft SQL Server Analysis Services?

Saturday, December 7, 2019 - 5:38:26 AM - Pedro Martins Back To Top (83315)

Hi Scott,

Thank you for this post, it is most useful.

I'm having a hard time displaying aggregated data in Report Builder reports, when the data source is a tabular model.

For instance I have a DAX measure that is a distinct count, the aggregation of this measure cannot be achieved by summing the individual values.

I tried the Aggregate function in Report Builder but it does not work so far.

Have you faced this issue before?

Many thanks,


Wednesday, June 26, 2019 - 6:03:36 PM - Scott Murray Back To Top (81602)

I would make sure the filter is applied to the query or just use a dataset filter.

Wednesday, June 26, 2019 - 4:48:15 PM - Henri Back To Top (81600)


Here is the problem when I set the parameter, I still can see the retst of the records in the table and I am not be able to filter out properly. Is there anything else that I should have set before run the report? Any thoughts?

get free sql tips
agree to terms