SQL SSAS计算成员显示#NUM!在多维数据集

Question

我在SQL Server 2008 SSAS Cube中定义了3个计算成员。

CALCULATE;         
/* Calculate Sales Volume */
CREATE MEMBER CURRENTCUBE.[Measures].[Ship Volume]
 AS [Measures].[QTY SHIPPED] + [Measures].[QTY ADJUST], 
FORMAT_STRING = "Standard", 
NON_EMPTY_BEHAVIOR = { [QTY SHIPPED], [QTY ADJUST] }, 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales';       
CREATE MEMBER CURRENTCUBE.[Measures].[Avg Price USD]
 AS [Measures].[NET SALES] / [Measures].[SHIP VOLUME], 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  ;    
CREATE MEMBER CURRENTCUBE.[Measures].[Avg Price CAD]
 AS [Measures].[NET SALES CAD] / [Measures].[SHIP VOLUME], 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  ; 

语法工作得很好，但是当我通过Excel查看我的立方体时，一些值有#NUM!作为值…不知道为什么会这样?这是因为除以0的问题吗?我该如何解决这个问题?

Answer 1

如果B不等于0，可能检查- A/B = C ?:

CALCULATE;         
/* Calculate Sales Volume */
CREATE MEMBER CURRENTCUBE.[Measures].[Ship Volume]
 AS [Measures].[QTY SHIPPED] + [Measures].[QTY ADJUST], 
FORMAT_STRING = "Standard", 
NON_EMPTY_BEHAVIOR = { [QTY SHIPPED], [QTY ADJUST] }, 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales';       
CREATE MEMBER CURRENTCUBE.[Measures].[Avg Price USD]
 AS Case
    When IsEmpty([Measures].[SHIP VOLUME] )
    THEN 0
    ELSE [Measures].[NET SALES] / [Measures].[SHIP VOLUME] END, 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  ;    
CREATE MEMBER CURRENTCUBE.[Measures].[Avg Price CAD]
 AS Case
    When IsEmpty([Measures].[SHIP VOLUME] )
    THEN 0
    ELSE [Measures].[NET SALES CAD] / [Measures].[SHIP VOLUME] END, 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  ; 

也可以尝试更改IsEmpty([Measures].[SHIP VOLUME] )

to [Measures].[SHIP VOLUME]=0

编辑

尝试将IsEmpty([Measures].[SHIP VOLUME] )更改为

[Measures].[SHIP VOLUME] is null OR [Measures].[SHIP VOLUME]=0