How to handle "All Others" in dimensional reporting

Check list of all posts

Sample

While it is relative easy to build “All others” report for relational package, it is not easy to build it for dimensional package. This document is intended to provide a sample as reference when such requirements occur.

In order to explain the concept, the following report is used. Check the report specification for reference ( version 10.2) sample package Go Sales (Analysis) is used.

Query

All crosstabs in report shows the same query

Year

[Sales (analysis)].[Time].[Time].[Year]->[Time].[2013]

All Set

[Sales (analysis)].[Products].[Products].[Product type]

Main Set

topCount ([Sales (analysis)].[Products].[Products].[Product type],2,[Revenue])

All Others Set

except ([Sales (analysis)].[Products].[Products].[Product type],[Main Set])

All Others Aggregate

Aggregate(currentMeasure within set [All Others Set])

Union Set

union ([Main Set],[All Others Aggregate])

Revenue

[Sales (analysis)].[Sales].[Revenue]

All Others Member 1

member ([All Others Aggregate],'All Others','All Others',[Sales (analysis)].[Products].[Products])

All Others Set 1

set ([All Others Member 1])

Union Set1

union ([Main Set],[All Others Set 1])

Case 1

Case 2

Case 3

This is to show All others in detail.

Case 4

This is the solution option 1:  we can turn the detail data into aggregated one row with Aggregate(currentMeasure within set [All Others Set]).

This solution physically separates main set and all other data, therefore we can handle main set differently from the whole set.

Case 5

This is the solution option 2:  we can turn the detail data into aggregated one row with Aggregate(currentMeasure within set [All Others Set]), and then merge it together with union ([Main Set],[All Others Aggregate]).  This solution doesn’t separate main set and all other data, therefore we can sort the whole set with the same criteria.

Case 6

This is the solution option 3:  we can turn the detail data into aggregated one row with Aggregate(currentMeasure within set [All Others Set]), and turn it to member with member ([All Others Aggregate],'All Others','All Others',[Sales (analysis)].[Products].[Products]), and then make it to set with set ([All Others Member 1]) ]), and then merge it to an new union set union ([Main Set],[All Others Set 1])

Report should have the same behavior as Case 5