MDX: generate hierarchy level within RDL report

Question

Let's say, we have a cube with dimention 'Grocery store', which has 2-level hierarchy:

• Fruits & Vegetables Dep.
  • Fruits
  • Vegetables
  • Tomatoes
  • Cucumbers
• Bakery Dep.
• Milk Dep.

The question is - how can I add hierarchy level within mdx query of RDL-report, so that 'Tomatoes' and 'Cucumbers' members would move to the new 3rd level under native 2nd level 'Vegetables'. I need to do that without any changes to the Cube, all by means of pure mdx only.

When I tried to build a calculated set for the desired 3rd level and use it along with the rest part of initial hierarchy like this:

 WITH SET [Level 3] AS 
    case 
    when [Grocery store].[Hierarchy].[Level 2].CURRENTMEMBER = [Grocery store].[Hierarchy].&[Tomatoes] OR
         [Grocery store].[Hierarchy].[Level 2].CURRENTMEMBER = [Grocery store].[Hierarchy].&[Cucumbers]
    then [Grocery store].[Hierarchy].[Level 2].CURRENTMEMBER
    else null end

SELECT {[Measures].[Sales]} ON COLUMNS,

CrossJoin(Hierarchize( {[Grocery store].[Hierarchy].[Level 2]}
                      -{[Grocery store].[Hierarchy].&[Tomatoes],
                        [Grocery store].[Hierarchy].&[Cucumbers]}),
          [Level 3]) ON ROWS 

FROM [CUBE]

I faced an error telling, that crossjoin function cannot take one same dimention 'Grocery store' two times.

Answer 1

Creating a new level on the fly isn't possible.

Not pretty but one way of getting around this is to create a couple of new calculated members in some unrelated dimension and then cross join to level two.

WITH 
  MEMBER [Geography].[Tomatoes] AS ([Geography].[All],[Grocery store].[Hierarchy].&[Tomatoes])
  MEMBER [Geography].[Cucumbers] AS ([Geography].[All],[Grocery store].[Hierarchy].&[Cucumbers])
  SET [Level 3] AS 
    {
      [Geography].[Tomatoes],
      [Geography].[Cucumbers]
    }    
SELECT 
  {[Measures].[Sales]} ON COLUMNS,
  CrossJoin(
     Hierarchize( 
       {[Grocery store].[Hierarchy].[Level 2]}
       {[Grocery store].[Hierarchy].&[Tomatoes],
        [Grocery store].[Hierarchy].&[Cucumbers]}
      )
     ,[Level 3]
   ) ON ROWS 
FROM [CUBE]