Warning: PAX and Rest API do NOT support filters in Views

[John Hammond]

All

If you are moving from Perspectives to PAX be aware of this limitation.

In Perspectives the Cube Viewer and Dynamic Reports (Active Forms) support filters in Views. (Right click on a column to choose sort order or Top 10 values etc)

In PAX and I assume by extension the API, view filters are not supported in either Dynamic Reports or the Viewer.

I hope IBM can rectify this as, unless features are replicated, users will be reluctant to migrate.

The only way round this I can see is to code a VBA button that sorts any report retrieved.

Any other solutions much appreciated, but this means you cant replicate Top 10 type reports in PAX natively.

[burnstripe]

You can still filter and sort using cube values, but you need to use MDX on the dimension you wish to sort

{Order(
{TM1FilterByLevel({TM1SubsetAll([Organization])},0)}
,[Revenue].([channel].[channel total],[product].[20000],[month].[year], [year].[2020], [version].[budget], [revenue].[volume - units])
,BDESC
)}

See attachment for illustration

Similarly you can filter by cube value the below will show only orgnanizations where the volume - units measure is greater than 14000

{Filter(
{TM1FilterByLevel({TM1SubsetAll([Organization])},0)}
,[Revenue].([channel].[channel total],[product].[20000],[month].[year], [year].[2020], [version].[budget], [revenue].[volume - units])>14000
)}

Equally if i wanted to show the top 5 organizations I could adapt the first mdx expression slightly, like the below.

{TopCount(
{Order(
{TM1FilterByLevel({TM1SubsetAll([Organization])},0)}
,[Revenue].([channel].[channel total],[product].[20000],[month].[year], [year].[2020], [version].[budget], [revenue].[volume - units])
,BDESC
)}
,5)}

With this you should be able to replicate your top 10 type reports in PAX without having to resort to VBA.

** Just overlook the fact that my screenshot is on the legacy client it's just what I had readily available, the mdx expressions can also replicated in PAX or PAW using the new subset editor and clicking on MDX at the top right and updating the MDX to the statements given.

[declanr]

In PAX and I assume by extension the API, view filters are not supported in either Dynamic Reports or the Viewer.

Not sure if I am missing something on the "viewer" side - I assume you mean the cube viewer in PaFE?
If so you can right click on a column header (element) and you have sort options (ascending/descending) and a "Top or Bottom Filter".

Dynamic reports are a bit more annoying though, as Burnstripe pointed out you can just use MDX or you can actually use the tm1rptfilter function
which covers the same basic filter/sort functionality without you having to do the MDX yourself (it just writes it for you.)

The documentation isn't fantastic but if you have an existing Dynamic Report, you just add the tm1rptfilter function in an additional cell (e.g. where the Title Filters are) and pointing it to the TM1RptView cell lets it know where to apply the filter.
If you are trying to create something a bit dynamic for the users you can have the TM1RptFilter in place and have it running off of Excel lists that the user can swap around.

[Steve Rowe]

MDX will only get you so far IMO on a dynamic report, though I would love to wrong!

There is some good stuff in brunstripes post but it only holds if we have a single dimension on the rows. This is a special case of the general problem, "I want to be able to order the data in my reports"

They key point is that the sorting and filtering is applied to one dimension at a time and so if you have a nested dimensions on the rows, the use of MDX only allows the ordering of subsets independantly of each other. This is won't give the same result as sorting on the data.

Since generally we need to sort the data we need to be able to sort on the tuples on the rows, AFAIK this isn't possible in a dynamic report and I don't know of a way to have a dynamic MDX view driven report in Excel in Pafe at this point.

My understanding is that the next reporting variant to be introduced will support MDX views.

[declanr]

Since generally we need to sort the data we need to be able to sort on the tuples on the rows, AFAIK this isn't possible in a dynamic report and I don't know of a way to have a dynamic MDX view driven report in Excel in Pafe at this point.

This is exactly what the TM1RptFilter allows you to do, you only specify the columns for which it should do the filtering based/sorting based on and then it applies that automatically to the rows (even when nested).
A basic example below where it is sorted based on the gross profit column:

Screenshot 2021-08-21 173328.jpg (490.23 KiB) Viewed 26692 times

[Steve Rowe]

Thanks Declan, I'll give it a try, last time I tried to use TM1RptFilter I couldn't get pursuade it to do what I wanted, obviously need yo have another crack....