Accessing single value using tuple

Hello,

I tried to access a single cell value using tuple but am getting incorrect result. I know the value is there and the tuple exists because I was able to retrieve the value using allmembers. This is a simple MDX query, I hope someone can tell me what I did wrong.

SELECT non empty { [Measures].[Weight]} ON COLUMNS,

non empty

{

([Time].[Year].[2007] ,

[Category Type].[Raw Materials] ,

[Category].allmembers)

}

ON ROWS FROM [MCube]

The query above returns:

2007 Raw Materials All 2,450,000

2007 Raw Materials Yarn & Fiber 1,250,000

2007 Raw Materials Auxiliary Materials 1,200,000

As you can see from the query above that the weight for Auxiliary Materials for 2007 is 1.2M. However, the query below returns no result as if the tuple for Auxiliary Materials does not exist. What did I do wrong?

SELECT non empty { [Measures].[Weight]} ON COLUMNS,

non empty

{

([Time].[Year].[2007] ,

[Category Type].[Raw Materials] ,

[Category].[Auxiliary Materials])

}

ON ROWS FROM [MCube]

Thanks,

SB

Do you have same problem if you remove NON EMPTY from columns and rows? And you are using Analysis Services 2000, right?

Vidas Matelis

|||

Hi Vidas,

Yes, same problem without "non empty" on both row and column. The result is an empty cell with weight column heading and no row heading.

I am using Analysis Services 2005.

Thanks.

SB

|||

SouBee,

Could you please try to specifie full member qualifier: dimensionName.hierarchyName.Member .Try to do this in both statements (One that works, and second that don't). Example: [Category].[Category].AllMembers, etc. Maybe there is some name matching between hierarchy and level.

You can also try build similar query in any front end and see if results are then same inconstent. Capturing statement with SQL Profiler might also help.

Vidas Matelis

|||

Hi Vidas,

Thanks for taking the time to read my posts.I had greatly simplified my query to make it easier to understand. I apologize for not fully qualifying the dimensions. I know I can get the cube browser to return the resultset I need, the only problem is to get RS Query Browser to do the same. When "Aggregate(calculated member)" is used in the report, RS automatically modified the MDX query and caused a big chunk of the resultset to be truncated. Thanks for your suggestion to use SQL Profiler because I was able to capture the MDX query I need (with minor modification) from executing Analysis Service cube browser.

This is the MDX query generated by RS 2005, it truncates the resultset and produces incorrect result when Aggregate(calculated member) is used:

SELECT NON EMPTY { [Measures].[Weight] } ON COLUMNS,

NON EMPTY
{[Time].[Year].[Year].ALLMEMBERS *
[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *
{[Raw Materials].[Category].[All]} *
{[Raw Materials].[Material Type].[All]},

[Time].[Year].[Year].ALLMEMBERS *
[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *
[Raw Materials].[Category].[Category].ALLMEMBERS *
{[Raw Materials].[Material Type].[All]},

([Time].[Year].[Year].ALLMEMBERS *
[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *
[Raw Materials].[Category].[Category].ALLMEMBERS *
[Raw Materials].[Material Type].[Material Type].ALLMEMBERS
)

} ON ROWS FROM [MCube]

This is the solution to my problem. It returns all data without truncating.

SELECT NON EMPTY { [Measures].[Weight} ON COLUMNS,

non empty

{

[Time].[Year].[Year].ALLMEMBERS *

[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *

{[Raw Materials].[Category].[All]} *

{[Raw Materials].[Material Type].[All]},

[Time].[Year].[Year].ALLMEMBERS *

[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *

[Raw Materials].[Category].[Category].ALLMEMBERS *

{[Raw Materials].[Material Type].[All]},

CROSSJOIN

(

AddCalculatedMembers([Time].[Year].[Year].MEMBERS),

CROSSJOIN

(

AddCalculatedMembers([Raw Materials].[Category Type].[Category Type].MEMBERS),

CROSSJOIN

(

AddCalculatedMembers([Raw Materials].[Category].[Category].MEMBERS),

AddCalculatedMembers([Raw Materials].[Material Type].[Material Type].MEMBERS)

)

)

)

}

ON ROWS FROM [MCube]

Thanks.

SB