蒙德里安的条件 [英] Where condition in Mondrian

问题描述

MDX查询以查找超过5000的员工薪水

select 
    {
      [Measures].[Total Employee],
      [Measures].[Total Salary]
    } on columns,
NON EMPTY 
    {
     (
      [Department].[All Department],
      [Position].[All Position],
      [Employee].[All Employee])
    } on rows
from Salary
where 
      [Measures].[Total Salary]>5000

我的模式

<Schema name="Foodmart">
  <Cube name="Salary" visible="true" cache="true" enabled="true">
    <Table name="employee" alias="">
    </Table>
    <Dimension type="StandardDimension" visible="true" foreignKey="department_id" name="Department">
      <Hierarchy name="All Department" visible="true" hasAll="true" allMemberName="All Department" primaryKey="department_id" primaryKeyTable="department">
        <Table name="department">
        </Table>
        <Level name="Dept" visible="true" column="department_description" uniqueMembers="true">
        </Level>
      </Hierarchy>
    </Dimension>
    <Dimension type="StandardDimension" visible="true" foreignKey="position_id" name="Position">
      <Hierarchy name="All Position" visible="false" hasAll="true" allMemberName="All Position" primaryKey="position_id" primaryKeyTable="position">
        <Table name="position">
        </Table>
        <Level name="position" visible="true" table="position" column="position_title" uniqueMembers="false">
        </Level>
      </Hierarchy>
    </Dimension>
    <Dimension type="StandardDimension" visible="true" name="Employee">
      <Hierarchy name="All Employee" visible="true" hasAll="true" allMemberName="All Employee">
        <Table name="employee" alias="">
        </Table>
        <Level name="New Level 0" visible="true" column="full_name" uniqueMembers="false">
        </Level>
      </Hierarchy>
    </Dimension>
    <Measure name="Total Salary" column="salary" aggregator="sum" visible="true">
    </Measure>
    <Measure name="Total Employee" column="employee_id" aggregator="distinct-count" visible="true">
    </Measure>
  </Cube>
</Schema>

推荐答案

我知道您是否来自slq，看起来不错，但是以下错误:

I know if you're coming from slq it looks ok but the following is wrong:

WHERE
      [Measures].[Total Salary]>5000

您可以在SELECT子句中使用过滤器功能:

You could use the filter function inside the SELECT clause:

SELECT
    {
      [Measures].[Total Employee],
      [Measures].[Total Salary]
    } ON COLUMNS,
NON EMPTY 
    {
     (
      [Department].[All Department],
      [Position].[All Position]
     )
    } ON ROWS
FROM [Salary]
WHERE FILTER(
        [Employee].[All Employee].CHILDREN,
        [Measures].[Total Salary]>5000
      );

以上内容将过滤所有时间总薪金大于5000的员工.

The above will be filtering employees whose total salary for all time is greater than 5000.

如果您喜欢以下内容，则过滤器可能会绕过完整的交叉集，但您将获得所有返回的所有员工的列表:

The filter could go around the complete cross set if you like the following but you'll get a list of all the respective employees returned:

SELECT
    {
      [Measures].[Total Employee],
      [Measures].[Total Salary]
    } ON COLUMNS,
NON EMPTY 
    FILTER(
     {
      (
       [Department].[All Department],
       [Position].[All Position],
       [Employee].[All Employee].CHILDREN
      )
     }
    ,[Measures].[Total Salary]>5000
   ) ON ROWS
FROM [Salary];

或在整个集合中使用HAVING子句-尽管从逻辑上讲，与上面的子句不同:

Or use a HAVING clause across the whole set - although this is logically different that the above:

SELECT
    {
      [Measures].[Total Employee],
      [Measures].[Total Salary]
    } ON COLUMNS,
NON EMPTY 
       [Department].[All Department]
     * [Position].[All Position],
     * [Employee].[All Employee].CHILDREN
   HAVING [Measures].[Total Salary]>5000 ON ROWS
FROM [Salary];

---

修改

---

Edit

如果仍然需要ROWS上的[All EmplyeeS]成员，则可以将过滤器移至子多维数据集:

You can move the filter to a sub-cube if you still require the [All EmplyeeS] member on ROWS:

SELECT
    {
      [Measures].[Total Employee],
      [Measures].[Total Salary]
    } ON COLUMNS,
NON EMPTY 
      [Department].[All Department]
     *[Position].[All Position]
     *[Employee].[All Employee]
    ON ROWS
FROM 
(
SELECT
    FILTER(
        [Employee].[All Employee].CHILDREN,
        [Measures].[Total Salary]>5000
      ) ON 0
FROM [Salary] 
);

这篇关于蒙德里安的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！