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.