Multiselect Slicing MDX Query Question

Hello,

I am trying to understand MDX multiselect / slicing / axis restriction. And there may be some attribute hierarchy vs normal hierarchy input as well. Any thoughts would be very helpful

I've written a few queries from the Adventure Works cube.

Query 1: Multiselect slicing on the Date attribute hierarchy; Displaying the Fiscal hierarchy, Date level on the axis. Normal, expected results.

Code Snippet

select [Date].[Fiscal].[Date].Members on 0
from [Adventure Works]
where ({[Date].[Date].&[1000], [Date].[Date].&[1100]}, [Measures].[Internet Sales Amount])

Results:

March 26, 2004 July 4, 2004
$43,703.84 $1,301.33

Query 2: Multiselect slicing on the Date attribute hierarchy; Displaying the Fiscal hierarchy, Month level on the axis. I believe I am getting different results because the visual total is overwritten. Essentially, when I specify the Month on the axis, I get rid of my initial slice of two days, so I get the entire month's values. Is this correct?

Code Snippet

select [Date].[Fiscal].[Month].Members on 0
from [Adventure Works]
where ({[Date].[Date].&[1000], [Date].[Date].&[1100]}, [Measures].[Internet Sales Amount])

Results:

March 2004 July 2004
$1,480,905.18 $50,840.63

Query 3: Multiselect slicing on the Fiscal hierarchy, Date level; Displaying the [Month of year] attribute hierarchy on the avis. I have absolutely no idea why I am not getting anything back for July. Can anyone explain this to me?

Code Snippet

select [Date].[Month of Year].Members on 0
from [Adventure Works]
where ({[Date].[Fiscal].[Date].&[1000], [Date].[Fiscal].[Date].&[1100]}, [Measures].[Internet Sales Amount])

Results:

All Periods March July
$43,703.84 $43,703.84 (null)

I'm running SP2.

Thanks!
Jessica

If Query 3 is re-written using a subselect, data is returned for July:

Code Snippet

select [Date].[Month of Year].Members on 0

from (select

{[Date].[Fiscal].[Date].&[1000], [Date].[Fiscal].[Date].&[1100]} on 0

from [Adventure Works])

where ([Measures].[Internet Sales Amount])

All Periods March July
$45,005.17 $43,703.84 $1,301.33

Also, if the order of the selected dates is reversed, no data is returned for March instead:

Code Snippet

select [Date].[Month of Year].Members on 0

from [Adventure Works]

where ({[Date].[Fiscal].[Date].&[1100], [Date].[Fiscal].[Date].&[1000]},

[Measures].[Internet Sales Amount])

All Periods March July
$1,301.33 (null) $1,301.33

So, at first glance, this seems like a bug to me - but maybe someone else can explain the results differently?

Multiple values in a parameter

Hi
I have an input parameter 'AccountNumber' in one of my
reports. I would like to make it possible for the user to
put in multiple accountnumbers in this parameter.
I was on a reporting server courser and we did this by
creating a function that accepts the list of
accountnumbers and (comma delimited) and then transforms
it into a table with eacj accountnumber as a row in the
table. The report will then read from the table as a data
driven report.
For the life of me I cannot get this right though. Could
someone please send me the code for the function to do
this and how to code the report to look at the table.
Thanks,
TheresaTake a look at
http://www.gotdotnet.com/Community/Resources/Default.aspx?AFXPath=/Resource%5b@.ResourceId='2E882C0A-8D2B-4EAD-81BE-8E66C0941A18'%5d.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Theresa" <theresa@.discussions.microsoft.com> wrote in message
news:20bd01c4a6e0$e409f740$a601280a@.phx.gbl...
> Hi
> I have an input parameter 'AccountNumber' in one of my
> reports. I would like to make it possible for the user to
> put in multiple accountnumbers in this parameter.
> I was on a reporting server courser and we did this by
> creating a function that accepts the list of
> accountnumbers and (comma delimited) and then transforms
> it into a table with eacj accountnumber as a row in the
> table. The report will then read from the table as a data
> driven report.
> For the life of me I cannot get this right though. Could
> someone please send me the code for the function to do
> this and how to code the report to look at the table.
> Thanks,
> Theresa

Multiple selects in sp

Hi
In a stored procedure, is it possible to check if an input parameter value
has been supplied by user? Furthermore if a parameter's value is supplied
then execute one select statement, if no value is supplied then use a second
select?
Thanks
Regards
try this:
create proc myproc
param int null
as
declare @.sql varchar(100)
if param is null
set @.sql = '...' -- your first select
else
set @.sql = '...' -- your second select
exec (@.sql)
go
|||Ok, I have this sp (attached). Still can't make it work.
Thanks
Regards
"vuht2000" <vuht2000@.yahoo.com> wrote in message
news:1124415729.139947.214020@.g44g2000cwa.googlegr oups.com...
> try this:
> create proc myproc
> param int null
> as
> declare @.sql varchar(100)
> if param is null
> set @.sql = '...' -- your first select
> else
> set @.sql = '...' -- your second select
> exec (@.sql)
> go
>
begin 666 SQLQuery.sql
M__YS`&4`= `@.`$$`3@.!3`$D`7P!.`%4`3 !,`%,`( !/`$X`#0`*`',`90!T
M`" `40!5`$\`5 !%`$0`7P!)`$0`10!.`%0`20!&`$D`10!2`" `3P!.``T`
M"@.!G`&\`#0`*``T`"@.!!`$P`5 !%`%(`( !0`%(`3P!#`$4`1 !5`%(`10`@.
M`%L`9 !B`&\`70`N`%L`0P!L`&D`90!N`'0`<P!3`&4`; !E`&,`= !#`&\`
M;0!M`&$`;@.!D`%T`#0`*`$ `0P!O`&T`< !A`&X`>0!4`'D`< !E`" `=@.!A
M`'(`8P!H`&$`<@.`H`#4`, `I`"P`#0`*`$ `4P!T`&$`= !U`',`( !V`&$`
M<@.!C`&@.`80!R`"@.`-0`P`"D`+ `-``H`0 !"`'4`<P!I`&X`90!S`',`1 !I
M`'8`:0!S`&D`;P!N`" `=@.!A`'(`8P!H`&$`<@.`H`#4`, `I`" `3@.!5`$P`
M3 `-``H`00!3``T`"@.!3`$4`5 `@.`$X`3P!#`$\`50!.`%0`( !/`$X`.P`-
M``H`1 !%`$,`3 !!`%(`10`@.`$ `<P!Q`&P`( !V`&$`<@.!C`&@.`80!R`"@.`
M,0`P`# `*0`-``H`20!&`" `0 !"`'4`<P!I`&X`90!S`',`1 !I`'8`:0!S
M`&D`;P!N`" `:0!S`" `3@.!5`$P`3 `-``H`( `@.`%,`10!4`" `0 !S`'$`
M; `@.`#T`( `G`%,`10!,`$4`0P!4`" `20!$`"P`( !#`&\`;0!P`&$`;@.!Y
M`"P`( !0`&$`<@.!E`&X`= !#`&\`;0!P`&$`;@.!Y`"P`( !!`&0`9 !R`&4`
M<P!S`"P`( !#`&\`=0!N`'0`>0`L`" `4 !O`',`= !C`&\`9 !E`"P`( !#
M`&\`=0!N`'0`<@.!Y`"P`( !4`&4`; `L`" `1@.!A`'@.`+ `@.`$4`30!A`&D`
M; `L`" `5P!E`&(`+ `@.`$D`;@.!V`&\`:0!C`&4`0P!O`&T`< !A`&X`>0`L
M`" `20!N`'8`;P!I`&,`90!!`&0`9 !R`&4`<P!S`"P`( !)`&X`=@.!O`&D`
M8P!E`$,`;P!U`&X`= !Y`"P`( !)`&X`=@.!O`&D`8P!E`% `;P!S`'0`8P!O
M`&0`90`L`" `20!N`'8`;P!I`&,`90!#`&\`=0!N`'0`<@.!Y`"P`( !"`'4`
M<P!I`&X`90!S`',`5 !Y`' `90`L`" `4P!T`&$`= !U`',`+ `@.`%,`= !A
M`'0`=0!S`$0`80!T`&4`+ `@.`%,`;P!U`'(`8P!E`"P`( !3`&\`=0!R`&,`
M90!$`&$`= !E`"P`( !#`&\`;0!P`&$`;@.!Y`%0`>0!P`&4`+ `@.`$(`; !O
M`&,`:P!E`&0`+ `@.`$(`; !O`&,`:P!E`&0`1 !E`' `80!R`'0`;0!E`&X`
M= `L`" `0@.!L`&\`8P!K`&4`9 !$`&$`= !E`"P`( !"`&P`;P!C`&L`90!D
M`%(`90!A`',`;P!N`"P`( !"`'4`<P!I`&X`90!S`',`00!R`&4`80`L`" `
M4P!T`&$`9@.!F`$<`<@.!A`&0`90`Q`"P`( !3`'0`80!F`&8`1P!R`&$`9 !E
M`#(`+ `@.`%,`= !A`&8`9@.!'`'(`80!D`&4`,P`L`" `4P!T`&$`9@.!F`$<`
M<@.!A`&0`90`T`"P`( !#`&P`:0!E`&X`= !.`&\`= !E`',`+ `@.`$8`:0!N
M`&$`;@.!C`&D`80!L`$,`;P!M`&T`90!N`'0`<P`L`" `4P!P`&4`8P!I`&$`
M; !2`&4`<0!U`&D`<@.!M`&4`;@.!T`',`+ `@.`%4`;@.!I`&8`;P!R`&T`<P!.
M`&4`90!D`&4`9 `L`" `00!C`&,`;P!U`&X`= !2`&4`9@.!E`'(`90!N`&,`
M90!.`&\`+ `@.`$,`;P!M`' `80!N`'D`4@.!E`&<`:0!S`'0`<@.!A`'0`:0!O
M`&X`3@.!O`"P`( !3`'4`< !P`&P`:0!E`'(`3@.!O`"P`( !6`&4`;@.!T`'4`
M<@.!E`$0`90!B`'0`;P!R`$X`;P`L`" `4 !A`'D`;0!E`&X`= !4`&4`<@.!M
M`',`+ `@.`%,`= !A`'0`90!M`&4`;@.!T`$$`9 !D`'(`90!S`',`+ `@.`$4`
M=@.!E`&X`= !.`&\`= !E`',`+ `@.`$0`:0!R`&4`8P!T`&D`;P!N`',`+ `@.
M`$T`80!P`% `80!T`&@.`+ `@.`%,`;P!R`'0`1P!R`&\`=0!P`"P`( !3`&\`
M<@.!T`"P`( !$`&$`= !E`$,`<@.!E`&$`= !E`&0`+ `@.`$0`80!T`&4`30!O
M`&0`:0!F`&D`90!D`"P`( !#`'(`90!A`'0`90!D`$(`>0`L`" `30!O`&0`
M:0!F`&D`90!D`$(`>0`L`" `=0!P`',`:0!Z`&4`7P!T`',`( `G`" `)@.`@.
M``T`"@.`@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`"<`1@.!2`$\`30`@.`&0`
M8@.!O`"X`0P!L`&D`90!N`'0`<P`@.`"<`( `F`" `#0`*`" `( `@.`" `( `@.
M`" `( `@.`" `( `@.`" `)P!7`$@.`10!2`$4`( `H`$,`;P!M`' `80!N`'D`
M5 !Y`' `90`@.`&P`:0!K`&4`( ! `$,`;P!M`' `80!N`'D`5 !Y`' `90`@.
M`"8`( `B`"4`(@.`I`" `00!.`$0`( `G`" `)@.`@.``T`"@.`@.`" `( `@.`" `
M( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`"<`* !3`'0`80!T`'4`<P`@.
M`&P`:0!K`&4`( ! `%,`= !A`'0`=0!S`" `)@.`@.`"(`)0`B`"D`)P`-``H`
M10!,`%,`10`-``H`( `@.`%,`10!4`" `0 !S`'$`; `@.`#T`( `G`%,`10!,
M`$4`0P!4`" `20!$`"P`( !#`&\`;0!P`&$`;@.!Y`"P`( !0`&$`<@.!E`&X`
M= !#`&\`;0!P`&$`;@.!Y`"P`( !!`&0`9 !R`&4`<P!S`"P`( !#`&\`=0!N
M`'0`>0`L`" `4 !O`',`= !C`&\`9 !E`"P`( !#`&\`=0!N`'0`<@.!Y`"P`
M( !4`&4`; `L`" `1@.!A`'@.`+ `@.`$4`30!A`&D`; `L`" `5P!E`&(`+ `@.
M`$D`;@.!V`&\`:0!C`&4`0P!O`&T`< !A`&X`>0`L`" `20!N`'8`;P!I`&,`
M90!!`&0`9 !R`&4`<P!S`"P`( !)`&X`=@.!O`&D`8P!E`$,`;P!U`&X`= !Y
M`"P`( !)`&X`=@.!O`&D`8P!E`% `;P!S`'0`8P!O`&0`90`L`" `20!N`'8`
M;P!I`&,`90!#`&\`=0!N`'0`<@.!Y`"P`( !"`'4`<P!I`&X`90!S`',`5 !Y
M`' `90`L`" `4P!T`&$`= !U`',`+ `@.`%,`= !A`'0`=0!S`$0`80!T`&4`
M+ `@.`%,`;P!U`'(`8P!E`"P`( !3`&\`=0!R`&,`90!$`&$`= !E`"P`( !#
M`&\`;0!P`&$`;@.!Y`%0`>0!P`&4`+ `@.`$(`; !O`&,`:P!E`&0`+ `@.`$(`
M; !O`&,`:P!E`&0`1 !E`' `80!R`'0`;0!E`&X`= `L`" `0@.!L`&\`8P!K
M`&4`9 !$`&$`= !E`"P`( !"`&P`;P!C`&L`90!D`%(`90!A`',`;P!N`"P`
M( !"`'4`<P!I`&X`90!S`',`00!R`&4`80`L`" `4P!T`&$`9@.!F`$<`<@.!A
M`&0`90`Q`"P`( !3`'0`80!F`&8`1P!R`&$`9 !E`#(`+ `@.`%,`= !A`&8`
M9@.!'`'(`80!D`&4`,P`L`" `4P!T`&$`9@.!F`$<`<@.!A`&0`90`T`"P`( !#
M`&P`:0!E`&X`= !.`&\`= !E`',`+ `@.`$8`:0!N`&$`;@.!C`&D`80!L`$,`
M;P!M`&T`90!N`'0`<P`L`" `4P!P`&4`8P!I`&$`; !2`&4`<0!U`&D`<@.!M
M`&4`;@.!T`',`+ `@.`%4`;@.!I`&8`;P!R`&T`<P!.`&4`90!D`&4`9 `L`" `
M00!C`&,`;P!U`&X`= !2`&4`9@.!E`'(`90!N`&,`90!.`&\`+ `@.`$,`;P!M
M`' `80!N`'D`4@.!E`&<`:0!S`'0`<@.!A`'0`:0!O`&X`3@.!O`"P`( !3`'4`
M< !P`&P`:0!E`'(`3@.!O`"P`( !6`&4`;@.!T`'4`<@.!E`$0`90!B`'0`;P!R
M`$X`;P`L`" `4 !A`'D`;0!E`&X`= !4`&4`<@.!M`',`+ `@.`%,`= !A`'0`
M90!M`&4`;@.!T`$$`9 !D`'(`90!S`',`+ `@.`$4`=@.!E`&X`= !.`&\`= !E
M`',`+ `@.`$0`:0!R`&4`8P!T`&D`;P!N`',`+ `@.`$T`80!P`% `80!T`&@.`
M+ `@.`%,`;P!R`'0`1P!R`&\`=0!P`"P`( !3`&\`<@.!T`"P`( !$`&$`= !E
M`$,`<@.!E`&$`= !E`&0`+ `@.`$0`80!T`&4`30!O`&0`:0!F`&D`90!D`"P`
M( !#`'(`90!A`'0`90!D`$(`>0`L`" `30!O`&0`:0!F`&D`90!D`$(`>0`L
M`" `=0!P`',`:0!Z`&4`7P!T`',`( `G`" `)@.`@.`" `#0`*`" `( `@.`" `
M( `@.`" `( `@.`" `( `@.`" `)P!&`%(`3P!-`" `9 !B`&\`+@.!#`&P`:0!E
M`&X`= !S`" `)P`@.`"8`#0`*`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `
M)P!7`$@.`10!2`$4`( `H`$,`;P!M`' `80!N`'D`5 !Y`' `90`@.`&P`:0!K
M`&4`( ! `$,`;P!M`' `80!N`'D`5 !Y`' `90`@.`"8`( `B`"4`(@.`I`" `
M00!.`$0`( `G`" `)@.`-``H`( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.
M`" `( `@.`" `( `G`"@.`4P!T`&$`= !U`',`( !L`&D`:P!E`" `0 !3`'0`
M80!T`'4`<P`@.`"8`( `B`"4`(@.`I`" `00!.`$0`( `G`" `)@.`-``H`"0`)
M`" `( `@.`" `( `@.`" `( `@.`" `( `G`"@.`20!$`" `20!.`" `* !3`&4`
M; !E`&,`= `@.`$,`;P!M`' `80!N`'D`20!$`" `9@.!R`&\`;0`@.`$,`;P!M
M`' `80!N`'D`0@.!U`',`:0!N`&4`<P!S`$0`:0!V`&D`<P!I`&\`; @.`@.`%<`
M2 !%`%(`10`@.`$(`=0!S`&D`;@.!E`',`<P!$`&D`=@.!I`',`:0!O `&X`/0!
M`$(`=0!S`&D`;@.!E`',`<P!$`&D`=@.!I`',`:0!O`&X`*0`I` "<`#0`*``T`
D"@.!%`%@.`10!#`" `* ! `',`<0!L`"D`#0`*`&<`;P`-``H`
`
end
|||There is no & string operator in T-SQL.
Change to + and your proc "might" work
You don't have to use exec to do this, you can do:
if @.param is null
select ...
else
select ...
Although I would suggest (in order to keep the benefits of stored
procedures:
if @.param is null
exec procedureName
else
exec procedureName @.param = @.param
Then you write two other procedures. The if block with optionally executed
queries can cause poor plans, but having single procedures like this can
really improve performance.
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:exulEIGpFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Ok, I have this sp (attached). Still can't make it work.
> Thanks
> Regards
>
> "vuht2000" <vuht2000@.yahoo.com> wrote in message
> news:1124415729.139947.214020@.g44g2000cwa.googlegr oups.com...
>
>

Multiple selects in sp

Hi
In a stored procedure, is it possible to check if an input parameter value
has been supplied by user? Furthermore if a parameter's value is supplied
then execute one select statement, if no value is supplied then use a second
select?
Thanks
Regardstry this:
create proc myproc
param int null
as
declare @.sql varchar(100)
if param is null
set @.sql = '...' -- your first select
else
set @.sql = '...' -- your second select
exec (@.sql)
go|||There is no & string operator in T-SQL.
Change to + and your proc "might" work
You don't have to use exec to do this, you can do:
if @.param is null
select ...
else
select ...
Although I would suggest (in order to keep the benefits of stored
procedures:
if @.param is null
exec procedureName
else
exec procedureName @.param = @.param
Then you write two other procedures. The if block with optionally executed
queries can cause poor plans, but having single procedures like this can
really improve performance.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:exulEIGpFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Ok, I have this sp (attached). Still can't make it work.
> Thanks
> Regards
>
> "vuht2000" <vuht2000@.yahoo.com> wrote in message
> news:1124415729.139947.214020@.g44g2000cwa.googlegroups.com...
>
>

Multiple selects in sp

Hi
In a stored procedure, is it possible to check if an input parameter value
has been supplied by user? Furthermore if a parameter's value is supplied
then execute one select statement, if no value is supplied then use a second
select?
Thanks
Regardstry this:
create proc myproc
param int null
as
declare @.sql varchar(100)
if param is null
set @.sql = '...' -- your first select
else
set @.sql = '...' -- your second select
exec (@.sql)
go|||There is no & string operator in T-SQL.
Change to + and your proc "might" work :)
You don't have to use exec to do this, you can do:
if @.param is null
select ...
else
select ...
Although I would suggest (in order to keep the benefits of stored
procedures:
if @.param is null
exec procedureName
else
exec procedureName @.param = @.param
Then you write two other procedures. The if block with optionally executed
queries can cause poor plans, but having single procedures like this can
really improve performance.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:exulEIGpFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Ok, I have this sp (attached). Still can't make it work.
> Thanks
> Regards
>
> "vuht2000" <vuht2000@.yahoo.com> wrote in message
> news:1124415729.139947.214020@.g44g2000cwa.googlegroups.com...
>> try this:
>> create proc myproc
>> param int null
>> as
>> declare @.sql varchar(100)
>> if param is null
>> set @.sql = '...' -- your first select
>> else
>> set @.sql = '...' -- your second select
>> exec (@.sql)
>> go
>
>