PAGINATION in MDX

Question

I am new in MDX and currently struggling in this. I badly need help on how to get the total count of customers based on the WHERE clause. Please see my query below. I am trying to implement pagination so I need the total count to get the number of pages to be displayed. Is that possible?

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]}) 
    )

Thank you in advance!

user_9700035 · Answer

You need to use Subset for paging. Let me walk you through an example. Its based on Microsoft AdventureWorks

So I want to see the INTERNET SALES AMOUNT of all PRODUCTS in FRANCE for 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])

This result is

These are 94 rows, since the header is also counted so SSMS shows 95

Now lets change the query just to return the number of rows without returning actual rows. For this we use a calculated measure, which will have the entire set expresion of your rows axis. We then apply count function on this

---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])

We now know we have 94 rows. Now Lets returns them in pages of 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])

The result is for page 1, replace the "0" with 1, before 10 in ",0,10)" you will have page 2 and so on.

PAGINATION in MDX

1 Answers