How to get data from tabular models using DAX queries in various programs
Part three of a six-part series of blogs

Knowing how to write DAX queries is one thing, but where are you going to use them? This blog shows how you can integrate DAX queries into SSMS, SSRS, SSIS, Excel and PowerPivot.

  1. Using DAX queries in SQL Server and other applications
  2. Running DAX queries within Management Studio
  3. Basing SSRS (Reporting Services) reports on DAX queries (this blog)
  4. Writing DAX queries in Integration Services (SSIS)
  5. Retrieving data into Excel using DAX queries
  6. Loading data into PowerPivot data models using DAX queries

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 18 February 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Basing SSRS (Reporting Services) reports on DAX queries

This page shows how to use DAX queries within SSRS.

I've assumed that you already know Reporting Services (if you don't, you could do worse than follow my colleague Andy Gould's excellent video tutorial, or book on a Wise Owl SSRS course).

Creating a data source

The first step, as ever in SSRS, is to create a new data source:

Creating a data source

Right-click on Shared Data Sources to create a data source.


Choose to base your data source on Analysis Services:

Analysis Services data source

Choose SSAS as the type, then click on the Edit button (half-shown here).]

Now choose which deployed or current SSAS Tabular model you want to connect to:

Choosing to connect to a database

Type in the server name, then choose your SSAS tabular database from the list as shown. The Wise Owl server shown here includes both workspace and deployed models.


When you choose OK a few times, you'll see your new data source:

Data source created

I wish I'd renamed it, but hey ho ...


Creating a dataset

You can now create a report based on this data source (I've missed out all of these stages), and in this report create a dataset:

Creating a dataset

Right-click on your data source to create a new dataset.


In the dialog box which appears, choose to go into the Query Designer:

Query Designer button

Click on the button shown to go into the query designer.


Now the weird bit - tell SSRS that you want to write a DMX query (even though you don't):

Click on the DMX tool

Click on the Command Type DMX tool shown (you'll get a scary message asking if you want to proceed).


Now switch to Design Mode:

Design Mode

Click on this tool to change your query view.

You can now either type your query into this window, or paste it in:

Pasting in DAX query

As for Management Studio, the onus is on you to make sure that your query makes sense. The best way to achieve this is to write the query in a DAX editor like DAX Studio first.


Here is this query, should you want to try this yourself:






"Total sales",



You can now choose OK to return to SSRS, where you'll see your new dataset:

New dataset

The column names aren't very snappy!


I've then renamed the dataset and all of the columns in it, to make them easier to work with:

The renamed dataset

The same dataset, but with better names for everything.


Testing out your dataset

As things stand, you have no idea whether your dataset actually returns any data!  The easiest way to test this is to create a tablix item, such as a table or (as below) a matrix:

A matrix

A matrix showing total sales by species and quadrant.


When you preview your report, you should see data!

The matrix previewed

Things are looking good! SSRS ran your DAX query to show data.


For the next part of this blog I'll show how to import data from Integration Services using DAX queries based on a tabular model.

This blog has 0 threads Add post