MDX: generate hierarchy level within RDL report -

let's say, have cube dimention 'grocery store', has 2-level hierarchy:

• fruits & vegetables dep.
  • fruits
  • vegetables
  • tomatoes
  • cucumbers
• bakery dep.
• milk dep.

the question - how can add hierarchy level within mdx query of rdl-report, 'tomatoes' , 'cucumbers' members move new 3rd level under native 2nd level 'vegetables'. need without changes cube, means of pure mdx only.

when tried build calculated set desired 3rd level , use along rest part of initial hierarchy this:

 set [level 3]      case      when [grocery store].[hierarchy].[level 2].currentmember = [grocery store].[hierarchy].&[tomatoes] or          [grocery store].[hierarchy].[level 2].currentmember = [grocery store].[hierarchy].&[cucumbers]     [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   [cube] 

i faced error telling, crossjoin function cannot take 1 same dimention 'grocery store' 2 times.

creating new level on fly isn't possible.

not pretty 1 way of getting around create couple of new calculated members in unrelated dimension , cross join level two.

with    member [geography].[tomatoes] ([geography].[all],[grocery store].[hierarchy].&[tomatoes])   member [geography].[cucumbers] ([geography].[all],[grocery store].[hierarchy].&[cucumbers])   set [level 3]      {       [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  [cube]