Parameters in MDX

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.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
"Wannabe_Kiwi" <amkessel@.gmail.com> wrote in message
news:1148941733.833408.226390@.g10g2000cwb.googlegroups.com...
> 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.
>|||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.
Kaisa M. Lindahl Lervik wrote:
> 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
>
> "Wannabe_Kiwi" <amkessel@.gmail.com> wrote in message
> news:1148941733.833408.226390@.g10g2000cwb.googlegroups.com...
> > 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.
> >