Skip to Content
0
Former Member
Sep 10, 2011 at 11:38 AM

Top N total and other subtotals - Nofilter

18 Views

Hi All,

Following is how the data looks in the cube for my query:

Item Class Count

-


Item 1 Class 1 4

Item 1 Class 2 2

Item 2 Class 2 1

Item 3 Class 1 2

Item 3 Class 2 8

Item 4 Class 1 4

Item 5 Class 1 3

Item 5 Class 2 4

Item 5 Class 3 1

Item 6 Class 1 3

Item 6 Class 2 6

Item 6 Class 3 2

Now in the report i have table with just item and count, a filter is applied on class and the table looks as below:

Item Count

-


Item 1 2

Item 2 1

Item 3 8

Item 5 4

Item 6 6

-


Total 21

Here is i have to apply rank, say top 2 on the above table with filter on class. The table should have totals and subtotals as below:

Item Count

-


Item 3 8

Item 6 6

-


Others 7

-


Total 21

The issue is "Other" aggregation is not happening properly, with NoFilter function, it just neglects all the filter and returns the sum from the Cube rather than from the table(with filter on class = 2)

How to arrive at the value for Other with Nofilter(Sum<measure>)-Sum<measure> format

Imp: In my actual request i have filters on more than two dimension columns which is not actually displayed in table.