TM1 Active Forms TM1RPTROW filter rows based on values

[damientaylorcreata]

Hi Guys,

I have created an active form that is comprised of 4 row dimensions which are set to dynamically generate based on an MDX statement in TM1RPTROW for each of the 4 dimensions. I have also set zero suppression on the form. This works well, however there are some values which are less than 2 decimal places in size (e.g. 0.00001) that when rounding in the active form display as zero. Therefore I am wondering if there is a way for me to apply a value filter to the active form to only display values that are say > 1 or < -1? Is there any place to add such a filter? So that when I regenerate the active form it will only display rows that contain a number that is greater than 1 or less than -1?

Any thoughts?

Thanks,
Damien

[rmackenzie]

I don't believe that there is such a filter available (e.g. like in the view extract dialog).

As a work-around, you could create new measures for the cube and have them rule in the measures with the raw values and apply rounding to the new measures so that they will be 0 and not 0.000x. Then base the active form on those new measures and the zero suppression should work in a more appropriate fashion.

[lotsaram]

Hi Damien,

You could also try
1/ change the MDX on one of your dimensions to include a FILTER function where the logical part of the filter references the cube and filters out elements with values smaller than the filter constraint
2/ use Excel filtering on the columns after the active form is rebuilt, you could chain this in automatically with VBA (will work in Excel but not in web)

[damientaylorcreata]

Thanks Guys... I have looked at all three solutions:

1) Create a seperate measure element and create a rule that makes values between 1 and -1 equal to zero.. Good solution, but this will result in my memory being used.. So I will keep this as a last resort

2) Using an Excel filter trigged by a VBA macro.. I will be wanting to build this in to TM1Web, so this solution may be out

3) MDX with cube value filter. This is my favourite solution. However I have not been able to get the FILTER function work..

I have applied the following MDX to our project dimension to filter the projects for which contain values greater than 1 for 2011 only. However it returns no project elements.

Code: Select all

{FiLTER({TM1FILTERBYLEVEL( {TM1SubsetBasis()}, 0)},[PROFITLOSS].([YEAR].[2011]) > 1)}

Cubename= PROFITLOSS
Dimension applied to = PROJECT
Dimension of filter = YEAR and element "2011"

I have also tried to literally specify every dimension with the top level consolidations (e.g. [Period].[Q1], [Customer].[All Customers],[CostCentre].[All CostCentres],....) to see if that would help, but still no returned elements.

Any ideas?

[lotsaram]

Hi Damien,

1/ The TM1SubsetBasis function is not meaningful within the context of an Excel report or any external MDX query as the function is attached to a specific subset in that it looks at the saved static list of members in the .sub file and performs subsequent actions in the MDX on that starting set (it is just an abbreviation to TM1 having to write out long hand in the expression window {[Dim].[Ele1],[Dim].[Ele2], ..[Dim].[EleN]} You should replace the TM1SubsetBasis reference with the MDX for the member set.

2/ To filter on a cube value you need to specify a fixed element for all dimension other that the one you want to filter, you can't just specify one dimension.

In a cube view you can't filter on a view with more than one row dimension. I don't think that's relevant to your case though as the FILTER function is self-contained and just being used to generate a member set. I think you can do what you want with other row dimensions and zero suppression, you just need to get the syntax right.

[GPC]

I have a similar challenge to Damien's - I have 3 row dimensions and I want to filter the cube based on a string value but I don't want to restrict the elements in the row dimensions.
I know I can just use an Excel filter but I'd like to use an MDX expression if possible. I've tried using subsets, all Members etc. but as you say Lotsaram and from what I've tried it seems that the only way it will work is by specifying a fixed element. Is there another way?

thanks,

Gregory