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?

multiselect result changes while add additional dimension to filter?

Every Query I introduce with multiselect to restrict the my rows works only as long as I do not add a second dimension to the filter fields ?

My Query (derived from Management Studio) - if you run it once like this and once with the comment line enabled the count of columns returned change - what I really did not understand, because only the values should change (different slice)?

Could anyone explain this to me? or point me to a location with detailed explanation? (I have checked http://www.mosha.com/msolap/articles/mdxmultiselectcalcs.htm this but it does not hit specially my question)

WITH
MEMBER [Sales Territory].[Sales Territory Group].[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__] AS 'AGGREGATE({ [Sales Territory].[Sales Territory Group].&[North America], [Sales Territory].[Sales Territory Group].&[Pacific] })'
SET [{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] AS '{ { [Sales Territory].[Sales Territory].[All Sales Territories] }, AddCalculatedMembers([Sales Territory].[Sales Territory].[Group].MEMBERS)}'
SELECT
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME , [Sales Territory].[Sales Territory].[Country].[Sales Territory Group] ON COLUMNS
FROM [Adventure Works]
WHERE
(
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__]
// , [Sales Channel].[Sales Channel].&[Internet]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

Noone with an explanation or hint to further documentation ?

Who is it possible that an aditional slice of an independ dimension changes the rows / columns if not filter or empty is used ?

HANNES

multiselect result changes while add additional dimension to filter?

Every Query I introduce with multiselect to restrict the my rows works only as long as I do not add a second dimension to the filter fields ?

My Query (derived from Management Studio) - if you run it once like this and once with the comment line enabled the count of columns returned change - what I really did not understand, because only the values should change (different slice)?

Could anyone explain this to me? or point me to a location with detailed explanation? (I have checked http://www.mosha.com/msolap/articles/mdxmultiselectcalcs.htm this but it does not hit specially my question)

WITH
MEMBER [Sales Territory].[Sales Territory Group].[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__] AS 'AGGREGATE({ [Sales Territory].[Sales Territory Group].&[North America], [Sales Territory].[Sales Territory Group].&[Pacific] })'
SET [{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] AS '{ { [Sales Territory].[Sales Territory].[All Sales Territories] }, AddCalculatedMembers([Sales Territory].[Sales Territory].[Group].MEMBERS)}'
SELECT
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME , [Sales Territory].[Sales Territory].[Country].[Sales Territory Group] ON COLUMNS
FROM [Adventure Works]
WHERE
(
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__]
// , [Sales Channel].[Sales Channel].&[Internet]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

Noone with an explanation or hint to further documentation ?

Who is it possible that an aditional slice of an independ dimension changes the rows / columns if not filter or empty is used ?

HANNES

Multiselect problem

hi ,
following parameter @.a has multi values. but when i select the multi value it gives some error.
is this the correct method i use? and is there any differrent way that i should use in the report designer when i use the value of that parameter ... Field!a.value etc.?\

SET [FilteredBUList] AS descendants(strtoset(@.a),[Account—BillingCodeDsc].[Billing Code Description],leaves)

The descendants function expects a single member for the first parameter, to get this to work with mulitple parameters you would need to use the generate function. I'm not exactly sure what your structures look like so you will need to fill in the dimension and hierarchy of the members in @.a in the code below

eg.

SET [FilteredBUList] AS generate(strtoset(@.a), descendants(<dimension>.<hierarchy>.CurrentMember),[Account—BillingCodeDsc].[Billing Code Description],leaves)

|||cant use generate function like that. giving syntax error.|||Sorry, it's missing a closing bracket.|||that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.
|||hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset
|||

lk_wick wrote:

that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.

If you want only the selected member(s) on the rows, then you would just put the parameter directly in the row axis.

Code Snippet

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
strtoset(@.BU) on rows

FROM Profitability

Also I would not do the "if empty return 0" logic in the MDX, if you are using Reporting Services, you can use a format string in the report to return 0 for null values. I often use format strings like "$0;($0);-;-" which returns negative values in brackets and 0 and null as dashes.|||

lk_wick wrote:

hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset

Sorry, I don't really understand what you are trying to do.

|||i have created dataset1 and define the query parameter named bu. i have created another dataset and i need do access that bu parameter like following.

set [test ] as strtoset(@.BU) bu query parameter is in differrent datset.
|||Query parameters are fed from Report parameters, so you should be able to map both datasets to using the same report parameter.

Multiselect problem

hi ,
following parameter @.a has multi values. but when i select the multi value it gives some error.
is this the correct method i use? and is there any differrent way that i should use in the report designer when i use the value of that parameter ... Field!a.value etc.?\

SET [FilteredBUList] AS descendants(strtoset(@.a),[Account—BillingCodeDsc].[Billing Code Description],leaves)

The descendants function expects a single member for the first parameter, to get this to work with mulitple parameters you would need to use the generate function. I'm not exactly sure what your structures look like so you will need to fill in the dimension and hierarchy of the members in @.a in the code below

eg.

SET [FilteredBUList] AS generate(strtoset(@.a), descendants(<dimension>.<hierarchy>.CurrentMember),[Account—BillingCodeDsc].[Billing Code Description],leaves)

|||cant use generate function like that. giving syntax error.|||Sorry, it's missing a closing bracket.|||that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.
|||hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset
|||

lk_wick wrote:

that is fine. but how can i get only the selected value.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS generate(strtoset(@.BU),descendants([Account—BillingCodeDsc].CURRENTMEMBER,[Account—BillingCodeDsc].[Billing Code Description],leaves))

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

this is my mdx. so once i trying to get only selected values there is some error. i mean some times it will appear all the BU . sometime it will display only the first BU. how can i get only selected value to the report design.

If you want only the selected member(s) on the rows, then you would just put the parameter directly in the row axis.

Code Snippet

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
strtoset(@.BU) on rows

FROM Profitability

Also I would not do the "if empty return 0" logic in the MDX, if you are using Reporting Services, you can use a format string in the report to return 0 for null values. I often use format strings like "$0;($0);-;-" which returns negative values in brackets and 0 and null as dashes.|||

lk_wick wrote:

hey darren
u knw how to solve following problem?

HI ,
how can i access the different dataset parameter in th SSRS. i have created one dataset and define Bu as a parameter. then i created separate dataset called dataset2 and need to access that BU parameter from dataset2. i cant access it using normal way. ex strtoset(@.Bu)..... ?

i need to access it thru the mdx query like...
set as strtoset(@.BU) this bu is from some other dataset

Sorry, I don't really understand what you are trying to do.

|||i have created dataset1 and define the query parameter named bu. i have created another dataset and i need do access that bu parameter like following.

set [test ] as strtoset(@.BU) bu query parameter is in differrent datset.
|||Query parameters are fed from Report parameters, so you should be able to map both datasets to using the same report parameter.

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273033&SiteID=1

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273033&SiteID=1

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273033&SiteID=1

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox