ssas - MDX 中的分页

Question

我是 MDX 的新手，目前正在为此苦苦挣扎。我非常需要有关如何根据 WHERE 子句获取客户总数的帮助。请在下面查看我的查询。我正在尝试实现分页，所以我需要总计数来获得要显示的页数。那可能吗？

WITH 
MEMBER [Measures].[DS_TY] AS 
  '([Transaction Type].[All Transaction Type].[Daily Sales], [Measures].[TY])' 
MEMBER [Measures].[ST_TY] AS 
'([Transaction Type].[All Transaction Type].[Stock Transfer], [Measures].[TY])' 
MEMBER [Measures].[IS_TY] AS 
  '([Transaction Type].[All Transaction Type].[IServ], [Measures].[TY])' 
MEMBER [Measures].[TOT_TY] AS 
  '([Transaction Type].[All Transaction Type], [Measures].[TY])' 
MEMBER [Measures].[DS_LY] AS 
  '([Transaction Type].[All Transaction Type].[Daily Sales], [Measures].[LY])' 
MEMBER [Measures].[ST_LY] AS 
'([Transaction Type].[All Transaction Type].[Stock Transfer], [Measures].[LY])' 
MEMBER [Measures].[IS_LY] AS 
  '([Transaction Type].[All Transaction Type].[IServ], [Measures].[LY])' 
MEMBER [Measures].[TOT_LY] AS 
  '([Transaction Type].[All Transaction Type], [Measures].[LY])' 
MEMBER [Measures].[2] AS 
  'iif([LY]=0 Or [LY]=Null, Null, ([TY] - [LY])/[LY])' 
MEMBER [Measures].[4] AS 
  '[1] - [TY]' 
MEMBER [Measures].[6] AS 
  '[5] - [TY]' 
SET smeasures AS 
'{[DS_TY],[ST_TY],[IS_TY],[TOT_TY],[DS_LY],[ST_LY],[IS_LY],[TOT_LY],[1],[2],[3],[4],[5],[6]}' 
MEMBER [Measures].[TY] AS 
  '[Measures].[Gross Up Sales YTDTY Amt (Allocation)]' 
MEMBER [Measures].[LY] AS 
  '[Measures].[Gross Up Sales YTDLY Amt (Allocation)]' 
MEMBER [Measures].[1] AS 
  '[Measures].[Quota Amt YTDTY (Allocation)]' 
MEMBER [Measures].[3] AS 
  '[Measures].[%Perf YTDTY GrossUp (Allocation)]' 
MEMBER [Measures].[5] AS 
  '[Measures].[Quota Annual Amt TY (Allocation)]' 
SELECT smeasures ON COLUMNS, 
NON EMPTY ORDER ( 
  { 
    [Client Customer].[Client Customer].[Customer].members 
  }, 
  [Client Customer].currentmember.name, 
  basc 
) ON ROWS 
FROM [CClient] 
WHERE ( 
      { 
        [Time].[Time].[Year].[2020].[November] 
      }, 
      [Transaction Type].[All Transaction Type], 
{[Client Org].[Level 02]}- 
DESCENDANTS({[Client Org].&[D2_BTR2099_01], [Client Org].&[D2_DTR2099_99], [Client Org].&[D2_GTR2099_02]}) 
    ) 

先感谢您！

标签： ssasmdx

Answer 1

您需要使用子集进行分页。让我带你看一个例子。它基于 Microsoft AdventureWorks

所以我想看看 2012 年法国所有产品的互联网销售量。

select {[Measures].[Internet Sales Amount]}on 0,

order (
filter({[Product].[Product].[Product]},[Measures].[Internet Sales Amount]>0)
,[Measures].[Internet Sales Amount],desc)
on 1 
from 
[Adventure Works]
where 
([Date].[Calendar Year].&[2012],[Geography].[Country].&[France])

这个结果是

这些是 94 行，因为标题也被计算在内，所以 SSMS 显示 95

现在让我们更改查询以返回行数而不返回实际行。为此，我们使用计算度量，它将具有行轴的整个集合表达式。然后我们对此应用计数函数

---total Row Count 
with member 
Measures.ResultSetCount 
as
(filter({[Product].[Product].[Product]},[Measures].[Internet Sales Amount]>0)).count

select Measures.ResultSetCount  on 0
from [Adventure Works]
where 
([Date].[Calendar Year].&[2012],[Geography].[Country].&[France])

我们现在知道我们有 94 行。现在让我们以 10 页的形式返回它们。

------Paging 
select {[Measures].[Internet Sales Amount]}on 0,

subset(
order (
filter({[Product].[Product].[Product]},[Measures].[Internet Sales Amount]>0)
,[Measures].[Internet Sales Amount],desc)
,0,10)
on 1 
from 
[Adventure Works]
where 
([Date].[Calendar Year].&[2012],[Geography].[Country].&[France])

结果是第 1 页，将“0”替换为 1，在“,0,10)”中的 10 之前，您将拥有第 2 页，依此类推。