MDX scripts

Hi all,
I would like to retrive data from my datawarehouse cube and make a report. But I got a problem when I ran a report dataset by MDX. First I checked the same MDX script in the MDX sample application and it worked. Then I ran it in the report, it took a long time and gave me an internal error. So, I simplified the script to remove one crossjoin and got the results. I don't know what's wrong with the reporting service.
I would very appreciate if anyone give me some suggestions.
Thanks
ymliu
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Use SQL Profiler to see the SQL being executed
Jeff
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:#VN8fF2lEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I would like to retrive data from my datawarehouse cube and make a report.
But I got a problem when I ran a report dataset by MDX. First I checked the
same MDX script in the MDX sample application and it worked. Then I ran it
in the report, it took a long time and gave me an internal error. So, I
simplified the script to remove one crossjoin and got the results. I don't
know what's wrong with the reporting service.
> I would very appreciate if anyone give me some suggestions.
> Thanks
> ymliu
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

MDX Parser

Hello,
Im currently running SEPT CTP, and im trying to create a report based on a
dataset filled by a MDX query. That query must have parameters.
I have defined the query in query designer, but after that I must edit the
query because some of my parameters include spaces ' ' and I must edit
StrToSet to surround my @.var with '[ ]' .
The problem is that i cant make any change to the query, it always gives the
error "An MDX expression was expected. An empty expression was found.". Even
when I switch from designer to hand-edit it gives the same error if I process
it.
I think it has somethin to do with the parser, because the StrTo... family
of functions expect a valid MDX expression as a parameter but all I have is
@.varname.
Help anyone? Thanks.hello Fernando,
How exactly are you changing your mdx query to accept the parameter values?
If you show me i may be able to help.
I've done it by running the query without any parameters to get the fields,
then editing the query to accept the parameters. This is done by placing an
'=' sign at the beginning of the query then at the end of each string of text
insert " & "
i.e
="with" & "member" &
and
"select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
etc etc
dont know if taht helps but i tried.
bobfoc
"Fernando Marçal" wrote:
> Hello,
> Im currently running SEPT CTP, and im trying to create a report based on a
> dataset filled by a MDX query. That query must have parameters.
> I have defined the query in query designer, but after that I must edit the
> query because some of my parameters include spaces ' ' and I must edit
> StrToSet to surround my @.var with '[ ]' .
> The problem is that i cant make any change to the query, it always gives the
> error "An MDX expression was expected. An empty expression was found.". Even
> when I switch from designer to hand-edit it gives the same error if I process
> it.
> I think it has somethin to do with the parser, because the StrTo... family
> of functions expect a valid MDX expression as a parameter but all I have is
> @.varname.
> Help anyone? Thanks.|||sorry made an error the parameter string should say [" &
Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
the wrong way round!
"bobfoc" wrote:
> hello Fernando,
> How exactly are you changing your mdx query to accept the parameter values?
> If you show me i may be able to help.
> I've done it by running the query without any parameters to get the fields,
> then editing the query to accept the parameters. This is done by placing an
> '=' sign at the beginning of the query then at the end of each string of text
> insert " & "
> i.e
> ="with" & "member" &
> and
> "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> etc etc
> dont know if taht helps but i tried.
> bobfoc
> "Fernando Marçal" wrote:
> > Hello,
> >
> > Im currently running SEPT CTP, and im trying to create a report based on a
> > dataset filled by a MDX query. That query must have parameters.
> > I have defined the query in query designer, but after that I must edit the
> > query because some of my parameters include spaces ' ' and I must edit
> > StrToSet to surround my @.var with '[ ]' .
> > The problem is that i cant make any change to the query, it always gives the
> > error "An MDX expression was expected. An empty expression was found.". Even
> > when I switch from designer to hand-edit it gives the same error if I process
> > it.
> >
> > I think it has somethin to do with the parser, because the StrTo... family
> > of functions expect a valid MDX expression as a parameter but all I have is
> > @.varname.
> >
> > Help anyone? Thanks.|||Hi bobfoc,
I've manage to use the parameters by starting the query with query designer.
What is absurd is this: I start the query with query designer, and when I
switch to query mode the query is auto-generated and the error is still
produced.
"bobfoc" wrote:
> sorry made an error the parameter string should say [" &
> Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
> the wrong way round!
> "bobfoc" wrote:
> > hello Fernando,
> >
> > How exactly are you changing your mdx query to accept the parameter values?
> > If you show me i may be able to help.
> >
> > I've done it by running the query without any parameters to get the fields,
> > then editing the query to accept the parameters. This is done by placing an
> > '=' sign at the beginning of the query then at the end of each string of text
> > insert " & "
> > i.e
> > ="with" & "member" &
> > and
> > "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> >
> > etc etc
> >
> > dont know if taht helps but i tried.
> >
> > bobfoc
> >
> > "Fernando Marçal" wrote:
> >
> > > Hello,
> > >
> > > Im currently running SEPT CTP, and im trying to create a report based on a
> > > dataset filled by a MDX query. That query must have parameters.
> > > I have defined the query in query designer, but after that I must edit the
> > > query because some of my parameters include spaces ' ' and I must edit
> > > StrToSet to surround my @.var with '[ ]' .
> > > The problem is that i cant make any change to the query, it always gives the
> > > error "An MDX expression was expected. An empty expression was found.". Even
> > > when I switch from designer to hand-edit it gives the same error if I process
> > > it.
> > >
> > > I think it has somethin to do with the parser, because the StrTo... family
> > > of functions expect a valid MDX expression as a parameter but all I have is
> > > @.varname.
> > >
> > > Help anyone? Thanks.|||that's just wierd, I've had some strange problems with reporting services
before but nothing that a reboot hasn't fixed.
I've never used query designer, i prefer writing from scratch then you
understand it better, but then i also work on the philosophy of if it works,
leave it unless you have to change it.
Glad you kid of sorted it,
bob
"Fernando Marçal" wrote:
> Hi bobfoc,
> I've manage to use the parameters by starting the query with query designer.
> What is absurd is this: I start the query with query designer, and when I
> switch to query mode the query is auto-generated and the error is still
> produced.
> "bobfoc" wrote:
> > sorry made an error the parameter string should say [" &
> > Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
> > the wrong way round!
> >
> > "bobfoc" wrote:
> >
> > > hello Fernando,
> > >
> > > How exactly are you changing your mdx query to accept the parameter values?
> > > If you show me i may be able to help.
> > >
> > > I've done it by running the query without any parameters to get the fields,
> > > then editing the query to accept the parameters. This is done by placing an
> > > '=' sign at the beginning of the query then at the end of each string of text
> > > insert " & "
> > > i.e
> > > ="with" & "member" &
> > > and
> > > "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> > >
> > > etc etc
> > >
> > > dont know if taht helps but i tried.
> > >
> > > bobfoc
> > >
> > > "Fernando Marçal" wrote:
> > >
> > > > Hello,
> > > >
> > > > Im currently running SEPT CTP, and im trying to create a report based on a
> > > > dataset filled by a MDX query. That query must have parameters.
> > > > I have defined the query in query designer, but after that I must edit the
> > > > query because some of my parameters include spaces ' ' and I must edit
> > > > StrToSet to surround my @.var with '[ ]' .
> > > > The problem is that i cant make any change to the query, it always gives the
> > > > error "An MDX expression was expected. An empty expression was found.". Even
> > > > when I switch from designer to hand-edit it gives the same error if I process
> > > > it.
> > > >
> > > > I think it has somethin to do with the parser, because the StrTo... family
> > > > of functions expect a valid MDX expression as a parameter but all I have is
> > > > @.varname.
> > > >
> > > > Help anyone? Thanks.

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

mdx parameter problem to get a smaller dataset

We have 2 reports in reporting services 2005.
The first one gives a global overview and with a drillthrough link, you
get the second report for more details. The second report is getting
back the whole dataset out of our dwh and is giving an
outofmemoryexception.
I'm trying to reduce the dataset with the parameters from the first
report, but I get always some errors.
Below you can find my mdx-query. When I'm using hard coded values it's
working, but when I'm using the parameters then the preview of my
report gives some nasty erros:
"Report item expressions can only refer to fields within the current
data set scope or, if inside an aggregate, the specified data set
scope."
my report parameters:
GrootboekGrootboekNR
BoekjaarTeller
my mdx-query:
SELECT NON EMPTY { [Measures].[BUDGET BEDRAG BM], [Measures].[GROOTBOEK
BEDRAG BASIS MUNT] } ON COLUMNS, NON EMPTY {
([Boekperiode].[BoekPeriode].[BoekPeriode].ALLMEMBERS *
[Grootboek].[Grootboek_naam].[Grootboek_naam].ALLMEMBERS *
[Kostensoort].[Kostensoort_NR].[Kostensoort_NR].ALLMEMBERS *
[Kostensoort].[Kostensoort_naam].[Kostensoort_naam].ALLMEMBERS *
[Document_datum].[ddate].[ddate].ALLMEMBERS *
[DagboekCode].[DagboekCode].[DagboekCode].ALLMEMBERS *
[Organisatie].[Lid NR].[Lid NR].ALLMEMBERS * [Grootboek Rekening
Courant].[Grootboek Rekening Courant].[Grootboek Rekening
Courant].ALLMEMBERS * [Grootboek Rekening Courant].[Rekening Courant
Naam].[Rekening Courant Naam].ALLMEMBERS * [fd Grootboek Transactie
Omschrijving].[Grootboek Transactie Omschrijving].[Grootboek Transactie
Omschrijving].ALLMEMBERS * [fd Grootboek Document NR].[Grootboek
Document NR].[Grootboek Document NR].ALLMEMBERS ) } DIMENSION
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( {
[BoekjaarTeller].[BoekjaarTeller].&[5] } ) ON COLUMNS FROM ( SELECT ( {
[Grootboek].[Grootboek_GR1].&[6], [Grootboek].[Grootboek_GR1].&[7] } )
ON COLUMNS FROM ( SELECT ( { [Organisatie].[Lid NR].&[ACCO] } ) ON
COLUMNS FROM [AcertFin]))) WHERE
([Grootboek].[Grootboek_GR1].CurrentMember,
[Grootboek].[Grootboek_NR].[Grootboek_NR].[" +
parameters!GrootboekGrootboekNR.Label +"],
[BoekjaarTeller].[BoekjaarTeller].[BoekjaarTeller].["+parameters!BoekjaarTeller.Label+"])
I've read also that you've to use ="..." to get a mdx expression, but
when I try that I'm getting errors about the =-sign.When it comes do dynamic queries, everything needs to be on the same line,
no extra lines are allowed. My best tip is to take the whole statement, put
it into Notepad, and turn off Word Wrap. Then make the whole query into a
very long line (it might break at some point, but try getting it all on one
line.)
If you need quotes inside the query, you need to escape them. Add another
quote in front and one behind, I think. (Properties("Key") will have to be
Properties("""Key""") I think.)
Some strings are casesensitive, so make sure you type Parameters with a big
P and also the parameter names.
I prefer using & instead of + when it comes to adding string values, but I'm
not sure if that has anything to say.
Make sure you have a space between " and & (or +), sometimes it gets funny
if you write it without spaces.
[BoekjaarTeller].[BoekjaarTeller].[BoekjaarTeller].["+parameters!BoekjaarTeller.Label+"]"
should be [BoekjaarTeller].[BoekjaarTeller].[BoekjaarTeller].[" &
Parameters!BoekjaarTeller.Label & "]
Also, check what the output of the Parameters!YourParameter.Label is. If it
sends out the wrong string, the query will fail.
How do you trigger the second report? URL? Again, check what the paramters
are in the link. Not sure if subreports uses the "drop down" parameters of
the main report, or if they use the parameter output from the hyperlink. If
your subreport query expects "1001" and gets "(1001) My book", your query
will fail. (I've had that problem, a link in a main report returned a
different member property than I expected.)
Kaisa
herman" <herman.maes@.gmail.com> wrote in message
news:1136364042.661254.230140@.f14g2000cwb.googlegroups.com...
> We have 2 reports in reporting services 2005.
> The first one gives a global overview and with a drillthrough link, you
> get the second report for more details. The second report is getting
> back the whole dataset out of our dwh and is giving an
> outofmemoryexception.
> I'm trying to reduce the dataset with the parameters from the first
> report, but I get always some errors.
> Below you can find my mdx-query. When I'm using hard coded values it's
> working, but when I'm using the parameters then the preview of my
> report gives some nasty erros:
> "Report item expressions can only refer to fields within the current
> data set scope or, if inside an aggregate, the specified data set
> scope."
> my report parameters:
> GrootboekGrootboekNR
> BoekjaarTeller
> my mdx-query:
> SELECT NON EMPTY { [Measures].[BUDGET BEDRAG BM], [Measures].[GROOTBOEK
> BEDRAG BASIS MUNT] } ON COLUMNS, NON EMPTY {
> ([Boekperiode].[BoekPeriode].[BoekPeriode].ALLMEMBERS *
> [Grootboek].[Grootboek_naam].[Grootboek_naam].ALLMEMBERS *
> [Kostensoort].[Kostensoort_NR].[Kostensoort_NR].ALLMEMBERS *
> [Kostensoort].[Kostensoort_naam].[Kostensoort_naam].ALLMEMBERS *
> [Document_datum].[ddate].[ddate].ALLMEMBERS *
> [DagboekCode].[DagboekCode].[DagboekCode].ALLMEMBERS *
> [Organisatie].[Lid NR].[Lid NR].ALLMEMBERS * [Grootboek Rekening
> Courant].[Grootboek Rekening Courant].[Grootboek Rekening
> Courant].ALLMEMBERS * [Grootboek Rekening Courant].[Rekening Courant
> Naam].[Rekening Courant Naam].ALLMEMBERS * [fd Grootboek Transactie
> Omschrijving].[Grootboek Transactie Omschrijving].[Grootboek Transactie
> Omschrijving].ALLMEMBERS * [fd Grootboek Document NR].[Grootboek
> Document NR].[Grootboek Document NR].ALLMEMBERS ) } DIMENSION
> PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( {
> [BoekjaarTeller].[BoekjaarTeller].&[5] } ) ON COLUMNS FROM ( SELECT ( {
> [Grootboek].[Grootboek_GR1].&[6], [Grootboek].[Grootboek_GR1].&[7] } )
> ON COLUMNS FROM ( SELECT ( { [Organisatie].[Lid NR].&[ACCO] } ) ON
> COLUMNS FROM [AcertFin]))) WHERE
> ([Grootboek].[Grootboek_GR1].CurrentMember,
> [Grootboek].[Grootboek_NR].[Grootboek_NR].[" +
> parameters!GrootboekGrootboekNR.Label +"],
> [BoekjaarTeller].[BoekjaarTeller].[BoekjaarTeller].["+parameters!BoekjaarTeller.Label+"])
> I've read also that you've to use ="..." to get a mdx expression, but
> when I try that I'm getting errors about the =-sign.
>

MDX Parameter in RS2005

Ok, I am not sure if I am going about this the correct way, so I thought I would ask...

I have an MDX query for the dataset in a report. A portion of this query defines a range of numbers to pull.

Example:

FROM ( SELECT ( {[OrderDate].[Business Day Number Of Month].[1]:[OrderDate].[Business Day Number Of Month].[5]} )

returns business day numbers 1-5. Works great in both RS and QA.

Now, for the second value I want to insert a parameter that I can free form enter into a text box. How would I go about putting that parameter into the MDX?

Thanks!

BobP

The only way I can think of to do this is to make your query an expression, e.g.

="...
FROM ( SELECT ( {[OrderDate].[Business Day Number Of Month].[1]:[OrderDate].[Business Day Number Of Month].[" & Parameters!DayOfMonth.Value & "5]} )"

Thanks,
Albert