Pagination using MDX (And knowing how many pages required in Canvas)

Canvas Canvas Questions
1

Hi all,

I know there are a few pagination features already available in Canvas, but I was trying to use MDX to build up the view and needed to know how many rows returned so that I can build a pagination on the Canvas page I am working on at the moment.

But so far I don’t have any idea how I can make it possible, anyone have any brilliant idea?

Thanks

(Looping in @rwang in this discussion)

Tat

2

Hi @twong,

Have you tried the $tm1Ui.resultsetTransform()? These are some of the samples where it has been utilized:

http://localhost:8080/samples/#/sample/component/mdx-chart

http://localhost:8080/samples/admin#/editor

It will consume the result returned by an MDX query through the cubeExecuteMdx() function, and transform it into an object that should be easier to manage with an HTML table for example.

–
Cheers,
Paul

3

You can get number of rows from Cellset object. It will be Axes[1][‘Cardinality’] property.

4

Hello,
if you use MDX to do pagination then there is one interesting function that could be utilized (I have used that in past):
SUBSET(SET, START, COUNT)
The function works in TM1 (despite undocumented - EDIT: DOCUMENTED and SUPPORTED!) - but you should not supply START as negative number or being larger than SET size, otherwise I have experienced random server crashes.
MS docs: Subset (MDX) - SQL Server | Microsoft Learn

1 Like
5

Hi @pbuncik,

We are using this function already, just we were unsure about the total size.

Regards

Tat

6

Hi @pbuncik,

SUBSET(SET, START, COUNT)

would return the unexpecting number of rows when using non empty function on row