MDX:在RDL报告中生成层次结构级别 [英] MDX: generate hierarchy level within RDL report

问题描述

比方说，我们有一个多维数据集为杂货店"的多维数据集，它具有2级层次结构:

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

• 水果&蔬菜部
  • 水果
  • 蔬菜
  • 西红柿
  • 黄瓜
  • Fruits & Vegetables Dep.
    • Fruits
    • Vegetables
    • Tomatoes
    • Cucumbers

    问题是-如何在RDL报表的mdx查询中添加层次结构级别，以便番茄"和黄瓜"成员将移动到本机第二层蔬菜"下的新第三层.我需要在不对多维数据集进行任何更改的情况下进行此操作，而仅通过纯mdx即可.

    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.

    推荐答案

    无法即时创建新关卡.

    解决这个问题的方法不是很漂亮，但是一种解决方法是在不相关的维度上创建几个新的计算成员，然后交叉连接到第二级.

    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]
    

    这篇关于MDX:在RDL报告中生成层次结构级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！