Use parameters in your #DAX queries

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

EVALUATE

‘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.

EVALUATE

CALCULATETABLE(

‘Product Subcategory’,

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

</Statement>

</Command>

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

</PropertyList>

</Properties>

<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).

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 <a href= DB Data Source using MSOLAP in Reporting Services” style=”padding-top:0px;padding-right:0px;padding-left:0px” alt=”Define OLE DB Data Source using MSOLAP in Reporting Services” src=”https://www.sqlbi.com/wp-content/uploads/F07pv03_thumb_3ABF357D.png” border=”0″ />

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