使用MDX进行行号分区 [英] Row number partition with MDX

问题描述

你好。我有下面的MDX代码。我是MDX的新手，所以寻求一些帮助。我需要在索赔号上获得一个行号，这样我才能选择第一个。它将按索赔号递增，商业单位代码递增，
索赔费用USD Descending排序。然后我想只选择等于一的数字。我认为排名功能可能是一个好方法，但不确定。 

SELECT NON EMPTY 

{[Measures]。[本地保修索赔成本]，&bbsp
[度量]。[保修索赔成本USD]，b
[度量]。[索赔行数]  

} ON COLUMNS， 

$
NON EMPTY

{ 

（

[索赔]。[索赔号]。[索赔号] .ALLMEMBERS

* [索赔不明飞行物]。[UFO代码]。[UFO代码] .ALLMEMBERS 

* [声明不明飞行物]。[UFO说明]。[UFO说明] .ALLMEMBERS 

* [声明]。[声明状态]。[声明状态] ] .ALLMEMBERS

* [索赔]。[索赔类型]。[索赔类型] .ALLMEMBERS

- * [索赔状态]。[索赔状态代码]。[索赔状态代码] .ALLMEMBERS

- * [索赔状态]。[索赔状态说明]。[索赔状态说明] .ALLMEMBERS

* [公司]。[公司]姓名]。[公司名称] .ALLMEMBERS 
* [公司]。[公司编号]。[公司编号] .ALLMEMBERS 

* [索赔决议]。[业务单位代码]。[业务单位代码] .ALLMEMBERS

* [客户]。[Market-Continent-Country-Region-SalesRep]。[国家] .ALLMEMBERS

* [Item - F]。[Item Number 02]。[Item Number 02] .ALLMEMBERS 

* [Item - F]。[Item Description] 。[项目描述] .ALLMEMBERS 

- * [索赔]。[占有声明]。[占有声明] .ALLMEMBERS 

* [已解决的日期]。[日期]。[日期] .ALLMEMBERS 

* [项目 - F]。[SRP 5 - 类别]。[SRP 5 - 类别] .ALLMEMBERS

* [项目 - F]。[SRP 1 - 类型]。[SRP 1 - 类型] .ALLMEMBERS

- *除外（[Claim]。[Approved By]。[Approved By]，{[claim] 。[已批准]。& [EUBATCH]，[声明]。[已批准]。& [USBATCH]}）

* [声明]。[已批准]。[已批准] .ALLMEMBERS

）

}

行数



来自[保修]



WHERE

（b
{[已解决的日期]。[财政日历]。[财政年度]。 & [2018]，[已解决的日期]。[财政日历]。[财政年度]。& [2019]}

）

解决方案

< blockquote>

Hi Nywi6100，

感谢您的提问。

根据您的描述，您可以使用Order函数，rank函数来实现此目的。所以mdx查询可能是这样的。

 WITH SET OrderMembers AS 
 ORDER 
（
 ORDER（$ b） $ b [索赔]。[索赔号]。[索赔号] .ALLMEMBERS，
 [索赔]。[索赔号]。[索赔号] .CURRENTmember.member_name，
 BASC）
 * 
订单（[索赔决议]。[业务单位代码]。[业务单位代码] .ALLMEMBERS，
 [索赔决议]。[业务单位代码]。[业务单位代码] .CURRENTmember.member_name ，
 BASC 
）
 * 
 [声称不明飞行物]。[UFO代码]。[UFO代码] .ALLMEMBERS 
 * [声称不明飞行物]。[UFO描述] 。[UFO描述] .ALLMEMBERS 
 * [索赔]。[索赔状态]。[索赔状态] .ALLMEMBERS 
 * [索赔]。[索赔类型]。[索赔类型] .ALLMEMBERS 
 * [公司]。[公司名称]。[公司名称] .ALLMEMBERS 
 * [公司]。[公司编号]。[公司编号] .ALLMEMBERS 
 
 * [客户]。 [Market-Continent-Country-Region-SalesRep]。[Country] .ALLMEMBERS 
 * [Item  -  F]。[Item Number 02]。[Item Number 02 ] .ALLMEMBERS 
 * [Item  -  F]。[Item Description]。[Item Description] .ALLMEMBERS 
 * [Date Resolved]。[Date]。[Date] .ALLMEMBERS 
 * [项目 -  F]。[SRP 5  - 类别]。[SRP 5  - 类别] .ALLMEMBERS 
 * [项目 -  F]。[SRP 1  - 类型]。[SRP 1  - 类型] .ALLMEMBERS 
 * [声明]。[批准者]。[批准者] .ALLMEMBERS，
 [措施]。[保修索赔费用美元]，
 DESC 
）
会员[措施] 。[会员等级] AS等级
（
（[索赔]。[索赔号]。[索赔号] .CURRENTMEMBER，
 [索赔决议]。[业务单位代码]。[业务单位代码] .CURRENTmember，
 [索赔UFO]。[UFO代码]。[UFO代码] .CURRENTMEMBER，
 [索赔UFO]。[UFO描述]。[UFO描述] .CURRENTMEMBER，
 [Claim]。[Claim Status]。[Claim Status] .CURRENTMEMBER，
 [Claim]。[Claim Type]。[Claim Type] .CURRENTMEMBER，
 [Company]。[Company Name] 。[公司名称] .CURRENTMEMBER，
 [公司]。[公司编号]。[公司编号] .CURRENTMEMBER，
 [客户]。[市场 - 大陆 - 国家 - Region-SalesRep]。[Country] .CURRENTMEMBER，
 [Item  -  F]。[Item Number 02]。[Item Number 02] .CURRENTMEMBER，
 [Item  -  F]。[Item Description]。 [项目描述] .CURRENTMEMBER，
 [已解决的日期]。[日期]。[日期] .CURRENTMEMBER，
 [项目 -  F]。[SRP 5  - 类别]。[SRP 5  - 类别]。 CURRENTMEMBER，
 [索赔]。[核准方]。[核准方] .CURRENTMEMBER 
），OrderMembers 
）
 SELECT 
非空过滤器（OrderMembers，[措施] ]。[会员等级] = 1）ON ROWS，
 NON EMPTY 
 {
 [Measures]。[本地保修索赔成本]，
 [度量]。[保修索赔成本]美元]，
 [措施]。[索赔行数]，
 [措施]。[会员等级] 
 
} ON COLUMNS 
来自[保修] 
 
 WHERE 
（
 {[已解决的日期]。[财政日历]。[财政年度]。& [2018]，[已解决的日期]。[财政日历]。[财政年份。& [2019]} 
）

有关详细信息，请参阅这两篇文章以获取帮助。

在SQL Server Analysis Services中使用MDX进行排序和排序

SQL Server Analysis Services排名和行号订购

最好的问候，

将

Hi there. I have the MDX code below. I am new to MDX, so looking for some help. I need to essentially get a row number on the Claim Number so I can select just the first one. It would be ordered by claim number ascending, business unit code ascending, Claim cost USD Descending. Then I would want to select only numbers equal to one. I think the rank function might be a good way to go, but not sure. 

SELECT NON EMPTY 
{ [Measures].[Warranty Claim Cost Local], 
[Measures].[Warranty Claim Cost USD],
[Measures].[Claim Line Count] 
} ON COLUMNS, 

NON EMPTY
{ 
(
[Claim].[Claim Number].[Claim Number].ALLMEMBERS
* [Claim UFO].[UFO Code].[UFO Code].ALLMEMBERS 
* [Claim UFO].[UFO Description].[UFO Description].ALLMEMBERS 
* [Claim].[Claim Status].[Claim Status].ALLMEMBERS
* [Claim].[Claim Type].[Claim Type].ALLMEMBERS
--* [Claim Status].[Claim Status Code].[Claim Status Code].ALLMEMBERS
--* [Claim Status].[Claim Status Description].[Claim Status Description].ALLMEMBERS
* [Company].[Company Name].[Company Name].ALLMEMBERS 
* [Company].[Company Number].[Company Number].ALLMEMBERS 
* [Claim Resolution].[Business Unit Code].[Business Unit Code].ALLMEMBERS
* [Customer].[Market-Continent-Country-Region-SalesRep].[Country].ALLMEMBERS
* [Item - F].[Item Number 02].[Item Number 02].ALLMEMBERS 
* [Item - F].[Item Description].[Item Description].ALLMEMBERS 
--* [Claim].[Possession Declaration].[Possession Declaration].ALLMEMBERS 
* [Date Resolved].[Date].[Date].ALLMEMBERS 
* [Item - F].[SRP 5 - Category].[SRP 5 - Category].ALLMEMBERS
* [Item - F].[SRP 1 - Type].[SRP 1 - Type].ALLMEMBERS
--* Except([Claim].[Approved By].[Approved By],{[Claim].[Approved By].&[EUBATCH],[Claim].[Approved By].&[USBATCH]})
* [Claim].[Approved By].[Approved By].ALLMEMBERS
)
}
ON ROWS

FROM [Warranty]

WHERE
(
{[Date Resolved].[Fiscal Calendar].[Fiscal Year].&[2018],[Date Resolved].[Fiscal Calendar].[Fiscal Year].&[2019]}
)

解决方案

Hi Nywi6100,

Thanks for your question.

Per your description, you could use Order function, rank function to achieve this. So the mdx query is probably like this.

WITH SET OrderMembers AS
ORDER
(
ORDER(
    [Claim].[Claim Number].[Claim Number].ALLMEMBERS,
	[Claim].[Claim Number].[Claim Number].CURRENTmember.member_name,
	BASC)
	 	*
ORDER([Claim Resolution].[Business Unit Code].[Business Unit Code].ALLMEMBERS,
      [Claim Resolution].[Business Unit Code].[Business Unit Code].CURRENTmember.member_name,
	  BASC
	 )
	 *
   [Claim UFO].[UFO Code].[UFO Code].ALLMEMBERS 
 * [Claim UFO].[UFO Description].[UFO Description].ALLMEMBERS 
 * [Claim].[Claim Status].[Claim Status].ALLMEMBERS
 * [Claim].[Claim Type].[Claim Type].ALLMEMBERS
 * [Company].[Company Name].[Company Name].ALLMEMBERS 
 * [Company].[Company Number].[Company Number].ALLMEMBERS 

 * [Customer].[Market-Continent-Country-Region-SalesRep].[Country].ALLMEMBERS
 * [Item - F].[Item Number 02].[Item Number 02].ALLMEMBERS 
 * [Item - F].[Item Description].[Item Description].ALLMEMBERS 
 * [Date Resolved].[Date].[Date].ALLMEMBERS 
 * [Item - F].[SRP 5 - Category].[SRP 5 - Category].ALLMEMBERS
 * [Item - F].[SRP 1 - Type].[SRP 1 - Type].ALLMEMBERS
 * [Claim].[Approved By].[Approved By].ALLMEMBERS,
 [Measures].[Warranty Claim Cost USD],
 DESC
 )
 MEMBER [Measures].[member Rank] AS Rank
 (
  ([Claim].[Claim Number].[Claim Number].CURRENTMEMBER,
   [Claim Resolution].[Business Unit Code].[Business Unit Code].CURRENTmember,
   [Claim UFO].[UFO Code].[UFO Code].CURRENTMEMBER,
   [Claim UFO].[UFO Description].[UFO Description].CURRENTMEMBER,
   [Claim].[Claim Status].[Claim Status].CURRENTMEMBER,
   [Claim].[Claim Type].[Claim Type].CURRENTMEMBER,
   [Company].[Company Name].[Company Name].CURRENTMEMBER,
   [Company].[Company Number].[Company Number].CURRENTMEMBER,
   [Customer].[Market-Continent-Country-Region-SalesRep].[Country].CURRENTMEMBER,
   [Item - F].[Item Number 02].[Item Number 02].CURRENTMEMBER,
   [Item - F].[Item Description].[Item Description].CURRENTMEMBER,
   [Date Resolved].[Date].[Date].CURRENTMEMBER,
   [Item - F].[SRP 5 - Category].[SRP 5 - Category].CURRENTMEMBER,
   [Claim].[Approved By].[Approved By].CURRENTMEMBER
   ),OrderMembers
 )
SELECT 
NON EMPTY FILTER( OrderMembers, [Measures].[member Rank]=1) ON ROWS,
NON EMPTY
{
 [Measures].[Warranty Claim Cost Local], 
 [Measures].[Warranty Claim Cost USD],
 [Measures].[Claim Line Count] ,
 [Measures].[member Rank]

} ON COLUMNS
FROM [Warranty]

 WHERE
 (
 {[Date Resolved].[Fiscal Calendar].[Fiscal Year].&[2018],[Date Resolved].[Fiscal Calendar].[Fiscal Year].&[2019]}
 ) 

For more details, you could refer to the two articles for help.

Order and Sort with MDX in SQL Server Analysis Services

SQL Server Analysis Services Rank and Row Number Ordering

Best Regards,

Will

这篇关于使用MDX进行行号分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！