[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1432
  • Last Modified:

NON EMPTY CROSSJOIN With Multiple Levels of a Single Dimenision

I need to do a NON EMPTY  CROSSJOIN where I need to return data from multiple levels of 2 dimensions.  When I try to run this, I get this error "Formula Error - duplicate dimension across (independent) axes in a <set> ..."  An example of the structure is below:

SELECT {[Measures].[Sales],[Measures].[Transactions]} on columns ,    
NON EMPTY  CROSSJOIN(CROSSJOIN({[Region].[Level 1].Members},  {[Region].[Level 2].Members}  ), {[Region].[Level 3].Members})
ON rows  
FROM Cube
WHERE ([Time].[2008].[Quarter 4].[October].[1])

Thanks,
Rob

0
RobDogg27
Asked:
RobDogg27
1 Solution
 
srnarCommented:
Better to use parent (or parent.parent) and a local calculation to get the name of parent. Simple solution that has minimal performace overhead and you get what you want - names of the levels for some members of the same hierarchy.
with member measures.CategoryName
as 
[Product].[Product Categories].CurrentMember.Parent.Parent.Name
member measures.SubcategoryName
as 
[Product].[Product Categories].CurrentMember.Parent.Name
select {measures.CategoryName, measures.SubcategoryName, measures.defaultmember} on columns,
[Product].[Product Categories].[Product Name].&[456] on rows
from [Adventure Works]

Open in new window

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now