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.
