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.
Cognos Paul or multi dimensional gurus, could you please help?
try using the suppression functionality
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.
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
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.
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
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.