Filtering multiple dimensions

[rajini]

I need to show members from 3 different dimensions. Since they are from 3 different dimensions, it forms a Cartesian product.

Example:

Dimension 1 - Country
Dimension 2 - Store
Dimension 3 - Product Line

If I include these 3 dimensions in the report, it forms a Cartesian product.  When I include a measure, the values should appear only for the relevant dataset. How do I filter for this?

I used the detail filter [Measure] is not null and is working fine but I would like to do this using Dimensional functions and dont want to use detail filter for this. Cognos Paul's blog was helpful in understanding the perils of using detail filter over dimensional data sources. Any help will be appreciated.

[rajini]

Cognos Paul or multi dimensional gurus, could you please help?

[wyconian]

try using the suppression functionality

[rajini]

Thanks a lot for the reply. Suppress function suppresses but I would like to filter so that the unwanted rows don't appear in the first place. Is there any dimensional function available to avoid this cross join?

Thanks.

[cognostechie]

Yes, there is. You can use the 'filter' which is a dimensional function in the rows and/or columns. I don't know what you are
putting in the rows and columns so I will explain it with an example. This is working in my environment:

Crosstab:

                                  2009               2010              2011            2012            2013

Sales Qty                     100                200                  50               75                100                 
Sales Dollars              2000              4000                200             150              1000

The year 2008 does not appear as the values for 2008 are zero. In the column, I have a query calculation with this expression:

filter(children([All Years]),tuple([All Measures]) is not null ))

All Measures is a data item with this expression:

[Sales Qty] + [Sales Dollars]

So it will only show the column when there is a value either for the Qty or the Dollars

The idea is to embed the 'filter' function in the row/column itself instead of using a detailed filter or a slicer

[rajini]

Thanks for the reply cognostechie.
My requirement is bit different. I use everything in the list for bursting (crosstabs can't be burst).

I have 4 columns in the list. First 3 columns are members from 3 different dimensions and the fourth column is the common measure for all the 3 dimensions.

Example List output :

Country (Dimension1) || Store (Dimension 2)|| Product (Dimension 3)|| Sales.

Because I use members from 3 different dimensions, I am getting cartesian product for country, store and product dimensions and lot of null values for the sales. I should only get the relevant dataset i.e., only rows which have got sales value (row suppression should work but ideally it should be filtered so that I don't get lot of unwanted rows). Hope this makes sense.

[cognostechie]

Moderators - I just posted a reply and the system says 'cannot access attachment upload path'. I even put that attachment in C: root folder with no security but still the same message :o

@ rajini

I just did it in a list and it works. I have Country and CategoryID from different dimensions and Quantity is the measure. When I drag and drop Country and CategiryID, it shows Argentina with Category 6 as blank because there is no quantity for that combination. When I use the function in the column then it suppresses Category 6 for Argentina. The function for the column CategoryD is filter([Northwind Cube].[Products].[Products].[CategoryID], [Quantity] is not null)

Too bad can't post the screenshots

[rajini]

Thanks a lot cognostechie for looking into this..WHen I add the third dimension, I am not getting the result I wanted... It looks like I need to add 3 filters...Cognos Paul had suggested a technique...It is working in sample package but I am struggling with my TM1 ragged hierarchy (getting extra errors which I am trying to resolve)...

filter( country, measure is not null)
filter(store, tuple(currentMember(country),measure) is not null)
filter(productline,tuple(currentMember(country),currentMember(store), measure) is not null)


Thanks a lot.