Pagination in MDX

IF you want to Pagination the set of data using MDX. Following article might be helpful.We go through this using example

E.g. you need to display set of data in Page Number = 2 and Page Size = 10

Following mention two ways might be help.

Using SUBSET () Function

Subset function returns a subset that contains the specified number of tuples, beginning at the specified start position. The start position is based on a zero-based index. Therefore when we called page number we are reduce -1 from page number and passes to start position and multiply by page size because we need to get correct index point to start the page.

SELECT {[Measures]. [Measure1]

              } ON COLUMNS,

              SUBSET

                     (

                     ORDER

                           ({Dimension.ALLMEMBERS}, NULL, BASC)

                           , (2 -1)* 10

                           , '10

                     )

                     ON ROWS

FROM [Cube]

Using TAIL () and HEAD () Functions

The Head function returns the specified number of tuples from the beginning of the specified set. The order of elements is preserved. The default value of Count is 1

The Tail function returns the specified number of tuples from the end of the specified set. The order of elements is preserved. The default value of Count is 1

Process of this query here is.

When we multiply page number and page size you have 20 rows count starting from head to bottom. But we need final 10 rows only. For that we use tail function.

SELECT {[Measures]. [Measure1]

              } ON COLUMNS,

              TAIL (

                     HEAD (

                           {Dimension.ALLMEMBERS}

                     , 2 * 10)

              , 10)

       )} ON ROWS 

FROM [Cube]

When we do the pagination my most preferable function is subset function.

Because when we only have 1 value in return. But when we think about it should not capture in page 2. But using tail and head function that display it. If you use subset function that does not display.

Therefore it depends on what requirement you have and what output you need.