By: Scott Murray | Updated: 2019-01-09 | Comments (4) | Related: > Reporting Services Report Builder
Problem
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?
Solution
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:
- Getting Started with the DAX queries for SQL Server Analysis Services
- SQL Server DAX Tutorial Overview
- SQL Server DAX Basics an Introduction
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.
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.
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.
Now, we must create a data set based on this newly created data source.
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.
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.
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.
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.
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.
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.
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.
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).
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).
We could easily change it to level "1|Bachelors" which would use a default parameter value of Bachelors.
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".
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.
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
- Check out these other resources:
About the author
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
Article Last Updated: 2019-01-09
