Can this type of Top N be formed in view?

[bunchukokoy]

Hi Experts,

May I just ask if the this particular Top N is doable (can be formed in a view).
I have four dimensions (1. Fruit, 2. Source, 3. Customer, 4. Measures (Rev & Qty) ). I want to put in view the top 2 customers that have the highest revenues based on the combination of the Fruit and the Source.

I have attached an Excel file showing my TM1 sample data and my desired view.
In my desired view, I want the Fruit and the Source to be row dimensions, not context. So from the cube, you'll see first look which customers are the top.

I am not sure, but I know it cannot be done in a view.
I know let's say for instance if customer AA is ranked 1st in the first combination, but is ranked 3rd in the second combination, there's no way we can hide the intersection of this second combination and of the customer AA. Simply because it's TM1's behaviour.

I just need a confirmation that there's no workaround in the view.

Please advise.

Maraming Salamat.


Bunch.

[EvgenyT]

Hi there,

The closest thing would be to play around with MDX filter/topcount functions (based on the cube values). Google mdx primer for details on how to use it in details

[rmackenzie]

I am not sure, but I know it cannot be done in a view.
I know let's say for instance if customer AA is ranked 1st in the first combination, but is ranked 3rd in the second combination, there's no way we can hide the intersection of this second combination and of the customer AA. Simply because it's TM1's behaviour.

This type of view is known as asymmetric because on an axis with nested dimensions, you want to 'reverse cherry pick' certain intersections. Typically, people do this more statically on column dimensions - e.g. with Month and Version dimensions: Jan-Dec, FY for Actual; FY for Budget and Variance. What you're trying to do is more complex as the selection of row-axis tuples is both asymmetric and dynamic.

I just need a confirmation that there's no workaround in the view.

You can't do asymmetric views in the cube viewer but you can in Excel, EV (now on 10.1?), and with Cognos Insight which AIUI has some sort of asymmetric view functionality. I don't think you're every going to be able to write a dynamic subset that will give you what you want, or get a result out of the active form filters. What you probably could do is write a cube-based MDX query to get the result you want, but unfortunately there aren't any standard tools, outside of an Excel/VBA combination, with which to easily view or disseminate the result.

[bunchukokoy]

Thanks for the replies.

I actually managed to get the result I wanted using of Excel fucntions. Though I used VBA. I had to create a view that contains all row dims' elements and slice it on the Excel to become the nearest-formed source.

Supposedly, these users wanted to prepare the data view, initially in the view so when they slice, it's just coloring that they'll do after. But technically the view spec is not possible.

rmackenzie wrote:
and with Cognos Insight which AIUI has some sort of asymmetric view functionality.

This one is manually done right? Meaning, the user will identify which has to be hidden and use the 'Hide' function to hide it. So by the next time he opens the Cognos Insight, he has to know if the hidden row should still be hidden or not.


Thanks.