Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
213 views
in Technique[技术] by (71.8m points)

ssas - MDX query to ORDER a Measure based on Multiple dimensions

I have a simple mdx query shown below. I want to order [Measures].[NumberOfCustomers] based on multiple dimensions on the ROW Axis.

SELECT
NON EMPTY
{ [Measures].[TotalCost]
  [Measures].[Damages]
  [Measures].[SpecialDamages]
  [Measures].[NumberOfCustomers]
} ON COLUMNS,
NON EMPTY
{
  ([OpenDate].[FullDate].[FullDate].ALLMEMBERS *
  [CloseDate].[FullDate].[FullDate].ALLMEMBERS *
  [DimCustomer].[Customer Group].[Customer Group].ALLMEMBERS *
  [DimType].[Type].[Type].ALLMEMBERS)
} ON ROWS
FROM Cube

I want to order [Measures].[NumberOfCustomers] to return below

*******************************************************************************************************************
|OpenFullDate   |ClosedFullDate |Customer Group |Type   |TotalCost  |Damages    |SpecialDamages |NumberofCustomers|
|*****************************************************************************************************************|
|2020-01-20     |2020-03-30     |Retail         |Active |£2,000     |£400       |£200           |10               |
|2020-02-10     |2020-03-10     |Commercial     |Active |£10,000    |£500       |£100           |8                |
|2020-01-02     |2020-04-30     |Manufacturing  |Active |£2,000     |£40        |£100           |6                |
|2020-03-20     |2020-10-30     |Servicing      |Active |£5,000     |£350       |£300           |5                |
|2020-01-20     |2020-04-30     |Law            |Active |£1000      |£100       |£50            |3                |
|*****************************************************************************************************************|

I use the mdx query below

SELECT 
NON EMPTY 
{ [Measures].[TotalCost]
  [Measures].[Damages]
  [Measures].[SpecialDamages]
  [Measures].[NumberOfCustomers]
} ON COLUMNS,
NON EMPTY 
ORDER (
         { ([OpenDate].[FullDate].[FullDate].ALLMEMBERS *
          [CloseDate].[FullDate].[FullDate].ALLMEMBERS *
          [DimCustomer].[Customer Group].[Customer Group].ALLMEMBERS *
          [DimType].[Type].[Type].ALLMEMBERS)}
          ,[Measures].[NumberOfCustomers],BDESC ) ON ROWS
FROM [Cube];

I get the following error message enter image description here

Any help will be appreciated.

question from:https://stackoverflow.com/questions/65862404/mdx-query-to-order-a-measure-based-on-multiple-dimensions

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
Waitting for answers

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...