TM1 Forum

I searched for that topic, but didn't find anything usefull. Except that there is a function named TM1RPTROW.
The documentation was not explaning how to use the filter in detail (not enough for me).

I have a large web report and want to set a filter (like usual autofilter in excel) on every column with element names. When the report is shown, the drop down listbox should show every element name from the rows below. It would be great if there are no duplicates!

If I select e.g. in one column element "china", the report should be updated, filtering everything different than china. Now the other listboxes should base again on the remaining rows, e.g. show in the article column all articels which are sold in china. And if I select "teddy bear" all rows not showing teddy bears should be disappear.

How could i handle this with mdx and TM1RPTROW? Where should I add this function???

ADMIN : Please try and post in the correct forum, ty

In the TM1RPTROW function you can pass a MDX query which can apply filters on the query and your results will be displayed in the Active Form

I tried a lot, but nothing worked. Do you have an example (hardcoded?) for my example?
MyCube(Dim1 ... Dim5)

Filter everything which has not $A$1 in Dim3. (means show everything with $A$1 in Dim3)

I would try this:

{Filter( TM1FilterByLevel(TM1SubSetAll([Dim3]), 0) , [MyCube].([Dim3].[" & $A$1 &"]) <> 0)}

Where is my fault?

The MDX statement which you have written does not seem to be right. Suppose if you want to filter on Dim3 then you will have to put Dim3 within TM1SubSetAll(). This will tell the MDX on which dimension you are working on. Later you apply Filter() in the MDX - when you apply the filter you skip Dim3 and use all other dimension in your syntax.

You syntax should look something like (following query will return all the elements in Dim3 which are non-zero in MyCube):

Hello Rizwan,

thank you for the mdx.
I have tried and it works but it does not deliver the subset for all non zero cells in MyCube - like suppressing zeroes in the cube viewer.
It delivers the subset for non zero cells in MyCube for the four elements defined Dim1/2/4/5.

I am looking for a solution for something that represents suppressing zeroes for really all cells of the cube - like this mdx (it is wrong of course):

Filter(
TM1SubSetAll([Dim3])
,
[Mycube].(
TM1SubSetAll([Dim1]),
TM1SubsetAll([Dim2]),
TM1SubsetAll([Dim4]),
TM1SubsetAll([Dim5]),
)
<> 0
)

Do you know a solution?

Thanks!
Tilo

Tilo,

With active forms zero suppression is not controlled by the tm1rptrow formula but it is instead controlled in a formula in the hidden row 8; the cell contains the TM1RPTView formula, one of the parameters is a 0 or a 1; 1 indicates all rows with zeroes will be suppressed, 0 indicates that none will.

Cheers,
Declan


Edit - I should specify that you can do zero suppression with MDX as shown above but if that's all you want the MDX to do then it is not the best method.

Tilo wrote:I have tried and it works but it does not deliver the subset for all non zero cells in MyCube - like suppressing zeroes in the cube viewer.
It delivers the subset for non zero cells in MyCube for the four elements defined Dim1/2/4/5.

I am looking for a solution for something that represents suppressing zeroes for really all cells of the cube - like this mdx (it is wrong of course):

A subset, by definition, applies to a single dimension and thus cannot return all cells in the cube. All it CAN return is a list of the filtered elements in the dimension it is created in. If you are trying to get a zero suppressed list of all the intersections in the cube then the answer is to create a view on the cube, and don't select anything for the dimensions (since by default that will mean everything) and then zero-suppress that view, making sure to not skip consolidated values and rule-calculated values (if you want those too). This will give you all the intersections in the cube with a value.

Hello Declan and Tomok,

thank you for your answers!
I would like to attach the mdx to a subset.

Correct - I would like to have the subset of a dimension that results from a cube view with all cells selected and then with zero suppression.
This subset shall be dynamic - e.g. to use it in a TI process that creates the subset itself after the cells of the cube have been sparsely populated.
The subset shall also be used live and be dynamically for end users entering data into the cube.
That is why i am looking for an mdx-solution to attach to a dynamic subset.

As you know when recording a subset creation in the subset editor the button for cube filtering is becoming greyed out.
According to IBMs answer to my official usage request there is no solution to dynamically filter a dimension for cube values with mdx.
Even the solution that works for one element (as posted in this forum) IBM did not tell me.

Cheers
Tilo