SSAS Cube中的计算不适用于零数据值 [英] Calculation in SSAS Cube not working for zero data values

问题描述

我正在使用SSAS 2008 R2多维数据集，并通过Excel 2010询问数据。在多维数据集中，我有一个计算来有条件地格式化数据。逻辑是，如果一个度量包含正在查看的事实的具体值，那么事实应该是红色的，如下所示：

I am working with a SSAS 2008 R2 cube and interrogating the data through Excel 2010. In the cube I have a calculation to conditionally format the data. The logic is if a measure contains a specific value for the fact being viewed, then the fact should be colored red, as follows:

CALCULATE;     

SCOPE
(
    [Measures].[Data value]
); 

If([Fact Base].[Confidentiality Status].[Confidential]) Then
    Fore_Color(This) = 255
End If;    

END SCOPE;

当我的[数据值]为0（零）时，fore_color不呈现为红色在 Excel 中。如果我将数据值更改为零以外的任何值，并重新处理多维数据集，则呈现为红色。有没有什么我错过的，还是有一个更好的 MDX 脚本，也可以用于零值？

When I have a [Data value] of 0 (zero) the fore_color doesn't render as red in Excel. If I change the data value to anything other than zero, and reprocess the cube it renders as Red. Is there something I'm missing, or is there a better MDX script that would also work for values of zero?

推荐答案

您应该使用

SCOPE
(
    [Measures].[Data value], [Fact Base].[Confidentiality Status].[Confidential]
); 

    Fore_Color(This) = 255

END SCOPE;

或者，效率较低：

SCOPE
(
    [Measures].[Data value]
); 

If([Fact Base].[Confidentiality Status].CurrentMember IS [Fact Base].[Confidentiality Status].[Confidential]) Then
    Fore_Color(This) = 255
End If;    

END SCOPE;

设置度量的前景色数据值和机密成员。

This sets the foreground color for the measure Data value and the Confidential member.

您的代码的作用是设置前景色，如果为机密成员，当前度量值（在SCOPE中始终为数据值）的值不为null或0.当评估一个数值表达式作为条件，在MDX中，所有不为0或null的值都将被计算为true。如果您在此处使用某个成员，则MDX将检查其当前度量值。要检查层次结构的当前成员是否是特定的，请使用 IS ，就像我在上面的第二个版本。

What your code does is setting the foreground color if, for the Confidential member, the value of the current measure (which is always Data value within the SCOPE) is not null or 0. When evaluating a numeric expression as a condition, in MDX everything not 0 or null is evaluated as true. And if you are using a member here, MDX checks its value for the current measure. To check if the current member of a hierarchy is a specific one, you use IS as I did in my second version above.

编辑
由于要求是检查当前单元格中是否包含与机密状态机密相关的数据，无论该维度是否包含在查询中是否可以使用：

EDIT As the requirement is to check if in the current cell, there is data contained which is related to the confidentiality status "Confidential", no matter if that dimension is contained in the query or not, you can use:

SCOPE
(
    [Measures].[Data value]
); 

If(Intersect(EXISTING [Fact Base].[Confidentiality Status].[Confidentiality Status].Members,
             {[Fact Base].[Confidentiality Status].[Confidential]}).Count = 1 Then
    Fore_Color(This) = 255
End If;    

END SCOPE;

这将检查保密状态的所有成员之间的交集， code>属性层次结构与当前单元格的数据和由 Confidential 成员组成的一个元素集存在，具有一个元素，换句话说，如果成员包含在该级别的现有成员中。

This checks if the intersection between all members of the Confidentiality Status level of the Confidentiality Status attribute hierarchy that exist with the data of the current cell and the one element set consisting of the Confidential member has one element, with other words, if the member is contained in the existing members of the level.

这篇关于SSAS Cube中的计算不适用于零数据值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！