Parameters in MDX

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wannabe_Kiwi - 29 May 2006 23:28 GMT

Is it possible to create an mdx query using parameters? For example, in
a SQL query I'm using in Reporting services, I might do this:

SELECT
*
FROM
dtav_ServiceFacts
WHERE
ServiceInstanceHost = 'BizTalkServerApplication' AND
ServiceInstanceState = @state

And then SQL Reporting automatically creates the parameter 'state' that
the user can choose. Is there an equivalence in MDX for this? For
example,

SELECT
{ [Dimension D1].[BizTalkServerApplication] } ON COLUMNS,
{ [Dimension D3].members } ON ROWS
FROM
[Cube D]
WHERE
( [Dimension D2].[@state] )

But when I try this, I get an error saying named parameters are not
supported, so use unnamed. So I put in a "?" in place of "@state",
which gives me a little value box, but then says command parameters are
not supported by this provider. Is there any way to do this,
particularly in SQL Reporting?

Thanks.

Reply to this Message

Kaisa M. Lindahl Lervik - 31 May 2006 11:40 GMT

It's very possible to create a MDX query with parameters. But the way you
write the query is a bit different than with T-SQL.

You need to let the whole query be on one line (might be several lines in
the designer, but with no line breaks), and the parameter is added with a
slightly different syntax: You also need to add a =" in the beginning and "
in the end.

="SELECT {[Dimension D1].[BizTalkServerApplication] } ON COLUMNS, {
[Dimension D3].members } ON ROWS FROM [Cube D] WHERE ([Dimension D2].[" &
Parameters!State.Value & "])"

Also check out William Pearson's article
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount
and BottomCount Parameters
http://www.databasejournal.com/features/mssql/article.php/10894_3504651_11

On the first page, he writes about parameterized MDX queries, regardless of
TopCount.

Kaisa M. Lindahl Lervik

> Is it possible to create an mdx query using parameters? For example, in
> a SQL query I'm using in Reporting services, I might do this:
[quoted text clipped - 26 lines]
>
> Thanks.

Reply to this Message

jhcorey@yahoo.com - 31 May 2006 13:37 GMT

You'll want to be aware of the differences between 2000 and 2005. In
2000 you could create MDX for your report with the technique shown. In
2005 you pretty much have to let the wizard create the MDX for you. It
creates the parameters for you in the designer, but you're pretty much
restricted (as far as I can see) to what it creates.

> It's very possible to create a MDX query with parameters. But the way you
> write the query is a bit different than with T-SQL.
[quoted text clipped - 48 lines]
> >
> > Thanks.

Reply to this Message