MDX忽略Excel过滤器 [英] MDX ignoring Excel filter

问题描述

我才刚开始了解MDX，但遇到了一位有经验的成员时遇到了麻烦.我正在使用以下MDX:

I'm just starting to get my head around MDX and I'm having trouble with a calculated member. I'm using the following MDX:

IIF( ISEMPTY((Axis(1).Item(0).Item(0).Dimension.CurrentMember，[Measures].[Qty])) ，空值 ，([[Product].[Product Code] .CurrentMember.Parent，[Measures].[Qty])

IIF( ISEMPTY((Axis(1).Item(0).Item(0).Dimension.CurrentMember, [Measures].[Qty])) ,NULL ,([Product].[Product Code].CurrentMember.Parent, [Measures].[Qty])

)

我想做的是获取显示在多维数据集中的一组产品的总数.然后，我使用该总数除以每种产品的数量，得出总数的百分比"度量.上面的MDX确实可以正确返回任何维度上显示的产品总数.但是，当用户使用Excel更改显示产品的过滤器时，上面的MDX仍会显示整个组的总数，而忽略用户检查了哪些产品.我假设我对MDX缺乏基本的了解，如何获得计算得出的度量值来说明用户在Excel中选择的产品代码?

What I'm trying to do is get a total quantity of the group of products displayed in a cube. I then use that total to divide by each product's quantity to get a "percent of total" measure. The above MDX does correctly return the total quantity of products displayed in any dimension. However, when a user in Excel changes the filter on which products are displayed, the MDX above still displays the total quantity for the whole group, ignoring which products the user has checked. I assume I'm lacking some basic understanding of MDX, how do I get the calculated measure to account for the product codes the user has selected in Excel?

推荐答案

使用Visual Studio SQL Server数据工具(如果正在使用该工具)，可以浏览到多维数据集，选择计算"选项卡，然后在计算工具"下>模板中有一个模板总计百分比".该工具提供的MDX非常灵活，因此百分比可以根据您拉入数据透视表的层次结构的属性进行调整.

With Visual Studio SQL Server Data Tools (if you are using that tool), you can browse to your cube, select the Calculations tab, and under Calculations Tools > Templates there is a template "Percentage of Total". The MDX this tool provides is flexible so that the percentage adjusts with the attributes of the hierarchy you've pulled into the pivot.

Case
// Test to avoid division by zero.
When IsEmpty
 ( 
    [Measures].[<<Target Measure>>] 
 ) 
Then Null

Else ( [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember,
   [Measures].[<<Target Measure>>] ) 
 /
 ( 
   // The Root function returns the (All) value for the target dimension.
   Root     
   ( 
      [<<Target Dimension>>] 
    ), 
    [Measures].[<<Target Measure>>] 
 )
End

我发现此选项可以在实现您所提到的目标时起作用.

I found this option to work when developing to achieve what you've mentioned.

这篇关于MDX忽略Excel过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！