Filter elements in view

[jviegas@bi4all.pt]

Hi all,

I'm facing a problem regarding the filtering of some elements in a view and although I was able to check some possibilities to solve this I'm haven't been able to close this.

I have a view (see picture in attach) and I want to replicate the following behavior:

- The cube view just presents the elements in GL accounts, products and company org only when the Adjustment Reconciliation difference is not zero.

In the example in the attach, the elements for the 3 dimensions should be filtered to the ones identified with the square.

I'm trying to use MDX and filter elements based on the reconciliation within the cube but I continue to get no values in the view when refreshing it.

Thank you in advance,

Jorge Viegas

[BariAbdul]

Can't you select required element manually and create name view.

[jviegas@bi4all.pt]

Hi,

the ideia is to select Time in a dimension and get product, company org and gl accounts elements filtered based on the information on adjustments.reconciliation differences

Thanks

[declanr]

Google the TM1 MDX Primer (or search this forum) and you want to write an MDX statement that filters on a cube value not equalling zero. It is quite simple to do.

[jviegas@bi4all.pt]

Declanr,

I also think it should be like that but when doing the MDX should I do this for the 3 dimensions (gl accounts, company org and products) or do it only on the one on the right of the "cascade" (in this case the gl account).

I ask this because when I try to filter the elements on the cube I get "No values" and in some cases I should get elements.

Thanks,

[BariAbdul]

This is what Declan mentioning: MDX resource/doc,Please go through it.

http://goo.gl/hN9j4R

Thanks

[BariAbdul]

Hi jviegas@bi4all.pt ,You might need something along the lines:

Code: Select all

FILTER(   
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, 
[Test].([Posting Measures].[Amount]) >= 
[Test].([Product].[MidasJCFI],[Posting Measures].[Amount]) 
)}

[jviegas@bi4all.pt]

Hi Bariabdul,

Thanks for the help

I had already read the document before openning the post but I was trying to do this

Code: Select all

FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Products] )}, 0)},
[Sales Adjustments].([ADJUSTMENTS].[measure])<>0
)}

Code: Select all

However I was getting a "No value" information when placing the dimensions in cascade on row.

I have other cubes with mdx filters working perfectly but this one with the cascade and having more dimensions is getting on my nerves

[uchow10]

Declanr,

I also think it should be like that but when doing the MDX should I do this for the 3 dimensions (gl accounts, company org and products) or do it only on the one on the right of the "cascade" (in this case the gl account).

I ask this because when I try to filter the elements on the cube I get "No values" and in some cases I should get elements.

Thanks,

I think you should do a filter for each dimension just to have a N-level subset of only the elements you are interested. Also I think you really need to go through the document again that BariAbdul provided. I provided the excerpt that you should reread for when doing your MDX filter:

The FILTER function is used to filter the dimension based on some kind of data values rather than just the members and their hierarchy on their own. This data might be cube data (numeric or string) or attribute data. This requires a change of thinking from straightforward single dimensions (lists with a hierarchy and occasionally some attributes) to a multi-dimensional space, where every dimension in these cubes must be considered and dealt with.

This example returns the leaf members of Product that have an Amount value in the Test cube above zero.
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
[Test].([Posting Measures].[Amount]) > 0 )}

Since the Test cube only has 2 dimensions – Product and Posting Measures this is a simplistic example. Most cubes will have more than just the dimension being filtered and the dimension with the filter value in. However, it is simple to extend the first example to work in a larger cube.

This example returns the leaf members of Product that have an Amount value for All Entities in the Test3 cube above zero.
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
[Test3].([Entity].[All Entities],[Posting Measures].[Amount]) > 0 )}

As you can see from the above, simply include all the requisite dimension references inside the round brackets. Usually you will just need a specific named member (e.g. ‘All Entities’). If the dimension is omitted then the CurrentMember is used instead which is similar to using !dimension (i.e. “for each”) in a TM1 rule, and could return different results at a different speed.

As menitoned in the excerpt you should include ALL dimension references and for starter, I suggest you test your MDX filters in the expression window of a dimension (Products for this example) subset editor

[BariAbdul]

Hi, jviegas@bi4all.pt ,Have you able to resolve it.Thanks

[jviegas@bi4all.pt]

Hi Bari,

Sorry on the delay answering but I had to be some days off.

Actually I'm trying to solve this today since I'm still clueless regarding the behavior I'm getting. I read again MDX primer in the part you've pointed out in the previous posts and tried to replicate this in the cube but with no success.

Every time I try to filter the one of the dimensions with a tuple to get the elements with the column value not null I get the "no value" information. And following what you've said and I was also doing I'm testing this still in architect to try to solve this.

Krs,

Jorge

[Harvey]

This article might help you. It discusses various approaches that will achieve what you want, with examples and screenshots.

TM1 Applications and cross dimensional security or Access tables with NoData in TM1