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

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

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

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

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: https://docs.microsoft.com/en-us/sql/mdx/subset-mdx?view=sql-server-2017

1 Like

Hi @pbuncik,

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

Regards

Tat

Hi @pbuncik,

SUBSET(SET, START, COUNT)

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