Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Report Builder with DAX Query Support


By:   |   Last Updated: 2019-01-09   |   Comments   |   Related Tips: > 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:

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.

GUI DAX 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".

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


Last Updated: 2019-01-09


next webcast button


next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools