Top 10 Projects in a TM! Perspectives report

[talsam]

Hi All,

I want to create a report in TM1 persepectives, which could generate top N Projects of a Department at a given time.
Cube: A
Dimension : 1) project 2)Department 3)time 4)Version

Can anyone help me out ?
Thanks !

[qml]

The simple and quick way is as follows. Build a Cube View with Projects on rows and the remaining dimensions as title or column dimensions. Right-click anywhere within the column that contains the measure which you want the Top 10 to be applied to and select 'Filter...'. Apply a Top Count filter with the value of 10 and some sorting if needed. Then generate an Active Form out of that Cube View and voila, you have the basis of your report with no coding. It is also possible to add/edit/remove a Filter directly in an Active Form, so you don't have to do it in the Cube Viewer.

[BariAbdul]

This is according to MDX premier

TopCount and BottomCount

A classic Top 10 command:
{ TOPCOUNT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, 10, [Test].([Posting Measures].[Amount]) )}
By omitting a sort order it sorts in the default order (which has the values descending in value and breaks any hierarchies present).

A Top 10 query with an explicit sort order for the results.
{ ORDER( {TOPCOUNT( {TM1FILTERBYLEVEL({TM1SUBSETALL( [Product] )},0)}, 10, [test].([Posting Measures].[Amount]))}, [test].([Posting Measures].[Amount]), BDESC) }
BDESC means to “break” the hierarchy.
Note how the chosen measure is repeated for the sort order. Although the same measure is used in the sample above you could actually find the top 10 products by sales but then display them in the order of, say, units sold or a ‘Strategic Importance’ attribute.

This is the top 10 products based on Test2's Rate values, not ordered so will be sorted according to the values in Test2.
{TOPCOUNT( {TM1FILTERBYLEVEL({TM1SUBSETALL( [Product] )},0)}, 10, [Test2].([Rate Measures].[Rate]))}

This is the top 10 products based on test2's data in the Rate measure, ordered from 10 through 1.
{ORDER( {TOPCOUNT( {TM1FILTERBYLEVEL({TM1SUBSETALL( [Product] )},0)}, 10, [test2].([Rate Measures].[Rate]))}, [test2].([Rate Measures].[Rate]), ASC)}

TopCount automatically does a descending sort by value to get the TOP members. If this is not desired, you might want to use the Head function (detailed below) instead.

BottomCount is the opposite of TopCount and so is used to find the members with the lowest values in a cube. Beware that the lowest value is often zero and if that value needs to be excluded from the query you will need to refer to the section on the Filter function later in this document.

A Bottom 10 query with an explicit sort order for the results.
{ ORDER( {BOTTOMCOUNT( {TM1FILTERBYLEVEL({TM1SUBSETALL( [Product] )},0)}, 10, [test].([Posting Measures].[Amount]))}, [test].([Posting Measures].[Amount]), BASC) }

Please go through it and you will get there! Thanks