Hi Guys,

We have a problem with displaying data in a tabular format, from
Parent-Child hierarchy in a dimension using Analysis Services 2005
mdx. Let me explain the problem below in detail.

We have a dynamic Parent-Child relationship in a dimension and the
hierarchy can grow upto 10 levels for some clients and it's only one
level for some. I built this parent-child hierarchy, and it works fine
when i browse the cube graphically. But when i want to retrieve the
data in a tabular format for report presentation, and when i try to
generate the query it only gives the names of all levels in one single
Column and values from Measures in different columns. What i want to
see is, names of each level in seperate columns and values from
Measures in different columns.

I am wondering if we can get around it, otherwise we may have to
flatten out the data and build hierarchies on real attributes on the
dimensions.

Any thoughts are appreciated.

Kind Regards
Raj Chidipudi

Hi Raj,

When using the OLE DB for OLAP 9.0 Provider, rather than the Analysis
Services Provider, the following Adventure Works query returns columns
for each of 4 levels:

select
{[Measures].[Reseller Sales Amount]} on 0,
Non Empty [Employee].[Employees].Members on 1
from [Adventure Works]

- Employee_Employees_Employee_Level_02
- Employee_Employees_Employee_Level_03
- Employee_Employees_Employee_Level_04
- Employee_Employees_Employee_Level_05


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***