This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Using DAX queries in SQL Server and other applications Running DAX queries within Management Studio Basing SSRS (Reporting Services) reports on DAX queries (this blog) Writing DAX queries in Integration Services (SSIS) Retrieving data into Excel using DAX queries 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.

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.

Using DAX queries in SQL Server and other applications
Running DAX queries within Management Studio
Basing SSRS (Reporting Services) reports on DAX queries (this blog)
Writing DAX queries in Integration Services (SSIS)
Retrieving data into Excel using DAX queries
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

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:

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

Choose to base your data source on Analysis Services:

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:

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:

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:

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

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

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 Command Type DMX tool shown (you'll get a scary message asking if you want to proceed).

Now switch to 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:

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:

EVALUATE

SUMMARIZE(

'Transaction',

Species[SpeciesName],

Quadrant[QuadrantName],

"Total sales",

SUM('Transaction'[Quantity])

)

You can now choose OK to return to SSRS, where you'll see your 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 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 showing total sales by species and quadrant.

When you preview your report, you should see data!

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.

Parts of this blog
Using DAX queries in SQL Server and other applications Running DAX queries within Management Studio Basing SSRS (Reporting Services) reports on DAX queries (this blog) Writing DAX queries in Integration Services (SSIS) Retrieving data into Excel using DAX queries Loading data into PowerPivot data models using DAX queries

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC