MDX parameters in Reporting Services 2005

Hello,
I've been trying to create a report using a dataset filled with a MDX query.
That query needs to have parameters (they come from report parameters). I've
done everything, including what is described in msdn2 and still cant get it
to work.
(The problem is not with the query, if i substitute the @.vars with strings
it works!)
Has everyone ever done this?
Thx.You should use the filter area in the Analysis Services query designer and
check the parameter checkbox. The MDX query will contain:
STRTOSET(@.variablename, CONSTRAINED)
And your parameters will need to be member unique names. The MSDN article
might be for SQL 2000 RS and therefore outdated.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in message
news:9E0D1946-C659-4FE2-A862-2005743BCFD8@.microsoft.com...
> Hello,
> I've been trying to create a report using a dataset filled with a MDX
> query.
> That query needs to have parameters (they come from report parameters).
> I've
> done everything, including what is described in msdn2 and still cant get
> it
> to work.
> (The problem is not with the query, if i substitute the @.vars with strings
> it works!)
> Has everyone ever done this?
> Thx.|||Hello Brian,
Im not using query designer, i am creating my own query.
Below I provide my previous query and the query with the change you
sugested. It still doensn't work... it gives the following error:
"Parser: The syntax for '=' is incorrect. (Microsoft SQL Server 2005
Analysis Services)"
The query im using is this:
WITH MEMBER [Measures].[Visitas Previstas]
AS '[Measures].[OBJECTIVE]'
SELECT
{[Measures].[Visitas],
[Measures].[Visitas Previstas],
[Measures].[% Real],
[Measures].[% Por Potencial],
[Measures].[% nos VH e H]}
on columns,
{[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
on rows
FROM TEAMS
WHERE CROSSJOIN([TEAMS DIM LINE].[LINE DESC].[@.lines1],
[TEAMS DIM PRODUCT].[PRODUCT DESC].[@.products1],
[TEAMS DIM BRICK].[TEAMS DIM BRICK].[ALL].[@.bricks1])
The query with changes:
WITH MEMBER [Measures].[Visitas Previstas]
AS '[Measures].[OBJECTIVE]'
SELECT
{[Measures].[Visitas],
[Measures].[Visitas Previstas],
[Measures].[% Real],
[Measures].[% Por Potencial],
[Measures].[% nos VH e H]}
on columns,
{[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
on rows
FROM TEAMS
WHERE CROSSJOIN(strtoset(@.lines1,CONSTRAINED),
strtoset(@.products1,CONSTRAINED),
strtoset(@.bricks1,CONSTRAINED))
Thanks for the help... I really need to get this to work...
"Brian Welcker [MSFT]" wrote:
> You should use the filter area in the Analysis Services query designer and
> check the parameter checkbox. The MDX query will contain:
> STRTOSET(@.variablename, CONSTRAINED)
> And your parameters will need to be member unique names. The MSDN article
> might be for SQL 2000 RS and therefore outdated.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in message
> news:9E0D1946-C659-4FE2-A862-2005743BCFD8@.microsoft.com...
> > Hello,
> >
> > I've been trying to create a report using a dataset filled with a MDX
> > query.
> > That query needs to have parameters (they come from report parameters).
> > I've
> > done everything, including what is described in msdn2 and still cant get
> > it
> > to work.
> > (The problem is not with the query, if i substitute the @.vars with strings
> > it works!)
> >
> > Has everyone ever done this?
> >
> > Thx.
>
>|||Sorry, forgot to mencion that i must use report parameters (I have cascading
parameters that i must use in the MDX query).
I've defined this parameters before defining the MDX query, and now i want
to use them in the query.
Thanks again.
"Fernando Marçal" wrote:
> Hello Brian,
> Im not using query designer, i am creating my own query.
> Below I provide my previous query and the query with the change you
> sugested. It still doensn't work... it gives the following error:
> "Parser: The syntax for '=' is incorrect. (Microsoft SQL Server 2005
> Analysis Services)"
> The query im using is this:
> WITH MEMBER [Measures].[Visitas Previstas]
> AS '[Measures].[OBJECTIVE]'
> SELECT
> {[Measures].[Visitas],
> [Measures].[Visitas Previstas],
> [Measures].[% Real],
> [Measures].[% Por Potencial],
> [Measures].[% nos VH e H]}
> on columns,
> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
> on rows
> FROM TEAMS
> WHERE CROSSJOIN([TEAMS DIM LINE].[LINE DESC].[@.lines1],
> [TEAMS DIM PRODUCT].[PRODUCT DESC].[@.products1],
> [TEAMS DIM BRICK].[TEAMS DIM BRICK].[ALL].[@.bricks1])
> The query with changes:
> WITH MEMBER [Measures].[Visitas Previstas]
> AS '[Measures].[OBJECTIVE]'
> SELECT
> {[Measures].[Visitas],
> [Measures].[Visitas Previstas],
> [Measures].[% Real],
> [Measures].[% Por Potencial],
> [Measures].[% nos VH e H]}
> on columns,
> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
> on rows
> FROM TEAMS
> WHERE CROSSJOIN(strtoset(@.lines1,CONSTRAINED),
> strtoset(@.products1,CONSTRAINED),
> strtoset(@.bricks1,CONSTRAINED))
> Thanks for the help... I really need to get this to work...
>
> "Brian Welcker [MSFT]" wrote:
> > You should use the filter area in the Analysis Services query designer and
> > check the parameter checkbox. The MDX query will contain:
> >
> > STRTOSET(@.variablename, CONSTRAINED)
> >
> > And your parameters will need to be member unique names. The MSDN article
> > might be for SQL 2000 RS and therefore outdated.
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in message
> > news:9E0D1946-C659-4FE2-A862-2005743BCFD8@.microsoft.com...
> > > Hello,
> > >
> > > I've been trying to create a report using a dataset filled with a MDX
> > > query.
> > > That query needs to have parameters (they come from report parameters).
> > > I've
> > > done everything, including what is described in msdn2 and still cant get
> > > it
> > > to work.
> > > (The problem is not with the query, if i substitute the @.vars with strings
> > > it works!)
> > >
> > > Has everyone ever done this?
> > >
> > > Thx.
> >
> >
> >|||Why do you have an = sign in your query? Don't use an expression for your
query, just type the literal MDX in the MDX window. Ignore the SQL 2000 RS
directions - I would first build the query with the query designer and then
you can hand edit the generated MDX.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in message
news:E2AFD1FD-8909-4028-AC86-3592CCCE69C6@.microsoft.com...
> Sorry, forgot to mencion that i must use report parameters (I have
> cascading
> parameters that i must use in the MDX query).
> I've defined this parameters before defining the MDX query, and now i want
> to use them in the query.
> Thanks again.
> "Fernando Marçal" wrote:
>> Hello Brian,
>> Im not using query designer, i am creating my own query.
>> Below I provide my previous query and the query with the change you
>> sugested. It still doensn't work... it gives the following error:
>> "Parser: The syntax for '=' is incorrect. (Microsoft SQL Server 2005
>> Analysis Services)"
>> The query im using is this:
>> WITH MEMBER [Measures].[Visitas Previstas]
>> AS '[Measures].[OBJECTIVE]'
>> SELECT
>> {[Measures].[Visitas],
>> [Measures].[Visitas Previstas],
>> [Measures].[% Real],
>> [Measures].[% Por Potencial],
>> [Measures].[% nos VH e H]}
>> on columns,
>> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
>> on rows
>> FROM TEAMS
>> WHERE CROSSJOIN([TEAMS DIM LINE].[LINE DESC].[@.lines1],
>> [TEAMS DIM PRODUCT].[PRODUCT DESC].[@.products1],
>> [TEAMS DIM BRICK].[TEAMS DIM BRICK].[ALL].[@.bricks1])
>> The query with changes:
>> WITH MEMBER [Measures].[Visitas Previstas]
>> AS '[Measures].[OBJECTIVE]'
>> SELECT
>> {[Measures].[Visitas],
>> [Measures].[Visitas Previstas],
>> [Measures].[% Real],
>> [Measures].[% Por Potencial],
>> [Measures].[% nos VH e H]}
>> on columns,
>> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
>> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
>> on rows
>> FROM TEAMS
>> WHERE CROSSJOIN(strtoset(@.lines1,CONSTRAINED),
>> strtoset(@.products1,CONSTRAINED),
>> strtoset(@.bricks1,CONSTRAINED))
>> Thanks for the help... I really need to get this to work...
>>
>> "Brian Welcker [MSFT]" wrote:
>> > You should use the filter area in the Analysis Services query designer
>> > and
>> > check the parameter checkbox. The MDX query will contain:
>> >
>> > STRTOSET(@.variablename, CONSTRAINED)
>> >
>> > And your parameters will need to be member unique names. The MSDN
>> > article
>> > might be for SQL 2000 RS and therefore outdated.
>> >
>> > --
>> > Brian Welcker
>> > Group Program Manager
>> > Microsoft SQL Server Reporting Services
>> >
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> > "Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in
>> > message
>> > news:9E0D1946-C659-4FE2-A862-2005743BCFD8@.microsoft.com...
>> > > Hello,
>> > >
>> > > I've been trying to create a report using a dataset filled with a MDX
>> > > query.
>> > > That query needs to have parameters (they come from report
>> > > parameters).
>> > > I've
>> > > done everything, including what is described in msdn2 and still cant
>> > > get
>> > > it
>> > > to work.
>> > > (The problem is not with the query, if i substitute the @.vars with
>> > > strings
>> > > it works!)
>> > >
>> > > Has everyone ever done this?
>> > >
>> > > Thx.
>> >
>> >
>> >|||editing is the problem. I cant change anything in the query, it always gives
the error "An MDX expression was expected. An empty expression was found"
invariably!
PS: The = sign didnt come from the query but from the parameters I was
referencing in the query parameters window.
"Brian Welcker [MSFT]" wrote:
> Why do you have an = sign in your query? Don't use an expression for your
> query, just type the literal MDX in the MDX window. Ignore the SQL 2000 RS
> directions - I would first build the query with the query designer and then
> you can hand edit the generated MDX.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in message
> news:E2AFD1FD-8909-4028-AC86-3592CCCE69C6@.microsoft.com...
> > Sorry, forgot to mencion that i must use report parameters (I have
> > cascading
> > parameters that i must use in the MDX query).
> >
> > I've defined this parameters before defining the MDX query, and now i want
> > to use them in the query.
> >
> > Thanks again.
> >
> > "Fernando Marçal" wrote:
> >
> >> Hello Brian,
> >>
> >> Im not using query designer, i am creating my own query.
> >>
> >> Below I provide my previous query and the query with the change you
> >> sugested. It still doensn't work... it gives the following error:
> >>
> >> "Parser: The syntax for '=' is incorrect. (Microsoft SQL Server 2005
> >> Analysis Services)"
> >>
> >> The query im using is this:
> >>
> >> WITH MEMBER [Measures].[Visitas Previstas]
> >> AS '[Measures].[OBJECTIVE]'
> >> SELECT
> >> {[Measures].[Visitas],
> >> [Measures].[Visitas Previstas],
> >> [Measures].[% Real],
> >> [Measures].[% Por Potencial],
> >> [Measures].[% nos VH e H]}
> >> on columns,
> >> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
> >> on rows
> >> FROM TEAMS
> >> WHERE CROSSJOIN([TEAMS DIM LINE].[LINE DESC].[@.lines1],
> >> [TEAMS DIM PRODUCT].[PRODUCT DESC].[@.products1],
> >> [TEAMS DIM BRICK].[TEAMS DIM BRICK].[ALL].[@.bricks1])
> >>
> >> The query with changes:
> >>
> >> WITH MEMBER [Measures].[Visitas Previstas]
> >> AS '[Measures].[OBJECTIVE]'
> >> SELECT
> >> {[Measures].[Visitas],
> >> [Measures].[Visitas Previstas],
> >> [Measures].[% Real],
> >> [Measures].[% Por Potencial],
> >> [Measures].[% nos VH e H]}
> >> on columns,
> >> {[TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NACIONAL],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[VERY HIGH],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[HIGH],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[MEDIUM],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[LOW],
> >> [TEAMS DIM POTENTIAL].[POTENTIAL DESC].[ALL].[NO POTENTIAL]}
> >> on rows
> >> FROM TEAMS
> >> WHERE CROSSJOIN(strtoset(@.lines1,CONSTRAINED),
> >> strtoset(@.products1,CONSTRAINED),
> >> strtoset(@.bricks1,CONSTRAINED))
> >>
> >> Thanks for the help... I really need to get this to work...
> >>
> >>
> >>
> >> "Brian Welcker [MSFT]" wrote:
> >>
> >> > You should use the filter area in the Analysis Services query designer
> >> > and
> >> > check the parameter checkbox. The MDX query will contain:
> >> >
> >> > STRTOSET(@.variablename, CONSTRAINED)
> >> >
> >> > And your parameters will need to be member unique names. The MSDN
> >> > article
> >> > might be for SQL 2000 RS and therefore outdated.
> >> >
> >> > --
> >> > Brian Welcker
> >> > Group Program Manager
> >> > Microsoft SQL Server Reporting Services
> >> >
> >> > This posting is provided "AS IS" with no warranties, and confers no
> >> > rights.
> >> >
> >> > "Fernando Marçal" <FernandoMaral@.discussions.microsoft.com> wrote in
> >> > message
> >> > news:9E0D1946-C659-4FE2-A862-2005743BCFD8@.microsoft.com...
> >> > > Hello,
> >> > >
> >> > > I've been trying to create a report using a dataset filled with a MDX
> >> > > query.
> >> > > That query needs to have parameters (they come from report
> >> > > parameters).
> >> > > I've
> >> > > done everything, including what is described in msdn2 and still cant
> >> > > get
> >> > > it
> >> > > to work.
> >> > > (The problem is not with the query, if i substitute the @.vars with
> >> > > strings
> >> > > it works!)
> >> > >
> >> > > Has everyone ever done this?
> >> > >
> >> > > Thx.
> >> >
> >> >
> >> >
>
>