Avoid Detail Filter in Page Set

Here is a cool puzzle posted by CommandoCognoise and fully solved by CognosPaul in cognoise.com

The simple report displays a crosstab with two sets of rows, [Product] and [Sales Employee]. Columns consist of [Year] and measure is the [Sales].

However there is a requirement to display the crosstab across multiple pages per employee. Thus page 1 will look like:

and page 2 will look like:

At first glance, the requirement could easily be accomplished using page sets grouped by [Sales Employee] and creating a master-detail relationship on that column. However this will result to the [Product] getting filtered by the specific employee.

To get around this, I nested the [Product] row with the root member of [Employee] dimension.

Nesting the columns with the [Employee] root member will also work.

But there is another solution which will not require nesting the rows or column. This is particularly useful if the format is required as the report is getting exported to excel to be processed further.

First step is to create another query with an [Employee] data item. This will be used for the page set.

The second query is the one which contains the items to be used in the crosstab. Add a data item calculation which looks like:

#prompt('emp','mun','[MUN OF ROOT MEMBER OF EMPLOYEE]')#

Replace the [Employee] row with this data item. The last step is to replace the master-detail relationship linking the [Employee] data item from the first query used in the page set with the 'emp' parameter.

One coolest solution!