计算成员中的WHERE子句 [英] WHERE clause in calculated member

问题描述

我在通过比较维的值来计算值时遇到了一些问题.我有3个维度(数据类型，客户，产品)和1个度量(GrossSales).

I am facing some problem to calculate values from comparing dimension's value. I have 3 dimensions (Datatype, Customer, Product) and one measure (GrossSales).

如果我想让ProductID = 1,2,3的GrossSales和Dataype = 4,5,6的MDX查询是什么?

What would be the MDX query if I want GrossSales for ProductID = 1,2,3 and Dataype = 4,5,6?

此处数据类型与GrossSales有关系，客户与GrossSales有关系，产品与客户有关系.

Here Datatype has relationship with GrossSales, Customer has relationship with GrossSales and Product has relationship with Customer.

我正在尝试这种方法，但是不起作用

I am trying this but doesn't work

CREATE MEMBER CURRENTCUBE.[Measures].Forecast_Gross_Sales AS 
( 
    SELECT NON Empty [Measures].[Gross Sale] 
    FROM [Measures] 
    WHERE (
        [Data Type].[ID].[ID] = 4 
        AND [Chain].[Customer ID] = [Measures].[Customer ID]
    ) 
), VISIBLE = 1 
, DISPLAY_FOLDER = 'Forecast' 
, ASSOCIATED_MEASURE_GROUP = 'Data Types';

推荐答案

似乎您刚刚开始使用MDX.有一些基本概念可以帮助您获得所需的东西. 比较SQL和MDX 可能会有所帮助. MDX使用where子句作为切片器(以选择某些维成员)而不是过滤器.您不能将 member = somevalue 放在where子句中.而且，您实际上不能使用where子句来定义与其他表的关系.

It looks like you are just getting started with MDX. There are some fundamental concepts that will help you get what you need. This comparison of SQL and MDX might be helpful. MDX uses the where clause as a slicer (to select certain dimension members) rather than a filter. You can't put member = somevalue in the where clause. And you can't really use the where clause to define a relationship to some other table.

相反，您的where子句更像

Instead, your where clause would be something more like

[Data Type].[ID].[ID].&[4]

由于我看不到您的数据模型，因此无法确定，但是我猜想 [Chain].[Customer ID] = [Measures].[Customer ID] 是您想要定义多维数据集的维度用法的内容，而不是查询中的内容.

Since I can't see your data model, I can't be sure, but I would guess that [Chain].[Customer ID] = [Measures].[Customer ID] is something that you want to define the the dimension usage of your cube rather than in the query.

现在，问题已被编辑，看起来您正在创建一个计算成员.在这种情况下，没有select或where子句.看起来会更像这样:

Now that the question has been edited, it looks like you are creating a calculated member. in this case there is no select or where clause. It will look more like this:

CREATE MEMBER CURRENTCUBE.[Measures].Forecast_Gross_Sales AS 
Aggregate([Data Type].[ID].[ID].&[4], [Measures].[Gross Sale])
, VISIBLE = 1 
, DISPLAY_FOLDER = 'Forecast' 
, ASSOCIATED_MEASURE_GROUP = 'Data Types';

从度量值组到客户"维度到链"维度的关系是应该在维度用法中定义的.这称为参考尺寸关系.

The relationship from the measure group through the Customer dimension to the Chain dimension is something that should be defined in the dimension usage. This is called a Reference dimension relationship.

这篇关于计算成员中的WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！