Parameterized MDX queries using AdomdParameter

Are there any good examples out there of performing parameterized MDX queries, using the new AdomdParameter class in 9.0? The only sample I've found so far involves querying KPIs, but I just want to query standard measures and dimensions.

In our experimentation, we're trying to use a parameter for the measure name in a query, eg.
SELECT @.measure on columns from [CubeName]

Running that command throws an error:
The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used

However, it works when I change the command to:
SELECT strtomember(@.measure) on columns from [CubeName]

Is wrapping all the parameter values in strtomember required? That seems a bit onerous.

Also, are there restrictions about where parameters can be used? Can you use them for the cube name? Slicer? Can you specify sets? The documentation around the usage of AdomdParameter is very slim.

To answer one of your questions;

You should simply change the query to:

SELECT {@.measure} on columns from [CubeName]

As for the rest. I dont think there are any limitations. You can simply try the combinations you need.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Doh! Sorry, I pasted in the wrong query (I've been playing around with different options). I actually did have the braces in my query, and it still failed. Here's some test code that I wrote against the AW database:

AdomdConnection connection = new AdomdConnection();
connection.ConnectionString = "Data Source=localhost;Initial Catalog=Adventure Works DW Standard Edition";
connection.Open();
try
{
AdomdCommand command = new AdomdCommand();
command.Connection = connection;
command.CommandText = "SELECT {@.measure} on columns from [Adventure Works]";
AdomdParameter param = command.CreateParameter();
param.ParameterName = "measure";

param.Value = "[Measures].[Internet Order Count]";

command.Parameters.Add(param);
command.Execute();

}
finally
{
connection.Close();
}

it throws the exception:
Query (1, 8) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

When I change it to:
"SELECT {strtomember(@.measure)} ..."

it works.

Am I missing something obvious here?
|||

hello Kevin,

i think what you observe is as expected. in the example above query has a string parameter, and as such if there is no strtomember call, the error is raised that a string expression was used.

Whether you need to call strtomember or other function depends on what the parameter means and where it is used in query. For example if you had a query doing filtering on member name containing or starting with a parameterized string - then you'd just use @.param. If your parameter contained a set, you'd probably use strtoset function. If you had an integer parameter to use in some comparison for example, then again you'd probably use it as is.

hope this helps.