THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Use parameters in your #DAX queries

The DAX query syntax allows you to insert a parameter by using the @ symbol.

EVALUATE

CALCULATETABLE(

    'Product Subcategory',

    'Product Category'[Product Category Name] = @Category )

Depending on the client and on the driver you are using to execute your DAX query, this syntax may be supported or not. Here is a short recap:

  • XMLA Execute Command: supported
  • OLE DB MSOLAP driver with ADO connection: supported
  • OLE DB MSOLAP driver with ADO.NET connection: not supported
  • OLE DB for OLAP MSOLAP driver with ADOMD library: supported
  • OLE DB for OLAP MSOLAP driver with ADOMD.NET library: supported
  • Reporting Services with Analysis Services driver: supported using DMX editor - not supported in Report Builder
  • Reporting Services with OLE DB driver: not supported (but a workaround is available)

Here is an example of an XMLA command that executes the initial DAX query passing Bikes as Category parameter.

<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">

    <Command>

        <Statement>

            EVALUATE

            CALCULATETABLE(

            'Product Subcategory',

            'Product Category'[Product Category Name] = @Category )

        </Statement>

    </Command>

    <Properties>

        <PropertyList>

            <Catalog>AdventureWorks Tabular Model SQL 2012</Catalog>

        </PropertyList>

    </Properties>

    <Parameters>

        <Parameter>

            <Name>Category</Name>

            <Value>Bikes</Value>

        </Parameter>

    </Parameters>

</Execute> 

Passing the parameter to the OLE DB MSOLAP driver doesn’t work in .NET because of a problem in System.Data.OleDb assembly, as described in a Connect bug that has never been fixed despite the “Fixed” state of the issue (please vote it!). As a workaround, you can use the ADOMD.NET library in .NET and you can find a working example written in C# in this article on SQLBI web site.

Finally, you can use DAX queries in Reporting Services by using the DMX editor in BIDS/SSDT/Visual Studio (you know, there are a lot of names for the same thing!). In fact, standard user interface for MDX only supports MDX queries, but if you select the "Command Type DMX" button (which is used to insert data mining queries) then you will be able to insert a DAX query with a parameter (remember to manually insert the parameters without the @ prefix by using the Query Parameters button).

image

UPDATE: A Step-by-Step guide describing how to write queries in Report Builder is available in a new blog post.

However, you cannot use DAX queries at all in Report Builder by using the standard Microsoft Analysis Services Data Source with Report Builder, because the user interface doesn't support DMX and only supports MDX queries. Instead, you can use the standard OLE DB connection type, by passing MSOLAP as a provider name.

Define OLE DB Data Source using MSOLAP in Reporting Services

UPDATE: A step-by-step guide describing how to write queries in Report Builder is available in a new blog post.

However, even in this case parameters in DAX queries are not supported, probably because of the same bug on System.Data.OleDb. Maybe that when this bug will be fixed, also Report Builder (and Reporting Services deisgner in Visual Studio) will work. In the meantime, you have to rely on string concatenation in order to pass a parameter to a query. In other words, you should set the Query.CommandText properties with the following expression:   

= "EVALUATE CALCULATETABLE(
'Product Subcategory',
'Product Category'[Product Category Name] = """ & Parameters!Category.Value & """ )"

I hope that the OLE DB support from ADO.NET will be fixed by Microsoft. I understand that OLE DB has been deprecated, but I don’t understand why we should wait a months if not years in order to get an ODBC driver for Analysis Services instead of a quick fix for that. Reporting Services developer using DAX would really appreciate this gift!

UPDATE: I fixed the post on January 5, 2012 thanks to Greg Galloway that pointed me to DMX Builder in Visual Studio

Published Thursday, January 05, 2012 4:47 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Omar Sultan said:

Hello Marco,

I was wondering if you have done anything similar however using Hierarchies?  I'm trying to create a tree filter that will work on the Date Hierarchy without having to break it down into three separate filters (Year - Quarter - Month)

June 29, 2013 6:07 AM
 

Marco Russo (SQLBI) said:

In DAX a hierarchy is just a set of 3 columns and you cannot apply a Direct parameter - probably you should rely on MDX in that case, or writing a complex IF statement in Reporting Services in order to build the proper DAX string.

July 7, 2013 5:00 PM
 

Ed said:

Hi Marco,

Is there a reason why using the @ symbol for dynamic columns would not work?

For example:

'Product Category'[@CategoryName]

I have a query that works properly using string concatenation and dynamic columns, but it does not work with the @ symbol. I also need multi-valued parameter filtering in the same query and following Chris Webb's post on that uses the @ symbol, which restricts me from using string concatenation.

[http://cwebbbi.wordpress.com/2012/06/01/handling-ssrs-multi-valued-parameters-in-dax-queries/]

Thanks!

July 18, 2013 2:02 PM
 

Marco Russo (SQLBI) said:

It's semantic problem, it would not work in SQL too and strong concatenation is the only way.

July 18, 2013 2:13 PM
 

SQLBI - Marco Russo said:

If you use Report Builder with Reporting Services, you can use DAX queries even if the editor for Analysis

May 21, 2014 7:40 AM
 

Andrew said:

Hi Marco,

Is it possible to execute XMLA command in DAX Studio? It would be nice to use this to add parameters to some DAX queries that I wrote for data validation purposes.

October 22, 2014 7:54 PM
 

Marco Russo (SQLBI) said:

In the upcoming release there will be a Direct support for merging parameters!

October 23, 2014 12:48 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement