Crosstab - set with total on page break

[Commander Cognos]

Hi guys,

I have a crosstab with products on rows, time in columns and revenue as measure. In addtion i drag the employees below the products. Now I'd like to do page break on each employees which is working fine (crosstab is shown only for the employee). However, the whole crosstab is filtered by the Master/Detail on the employee.

What I'd like to achieve is to keep the numbers for the products in the rows in the top over all employees. Is it possible to define an own tuple?

Screenshots attached.

Thanks and cheers

[Francis aka khayman]

i assume you are using dimensional reporting.

sorry I'm a little rusty with dimensional reporting. closest i got is to nest [products] with a [data item] = tuple(currentMember([xx]),[Y])

where [xx] = heirarchy of [products]
[Y] = root member of [employee]

your crosstab will look like:

                                [Years]
[product][data item]   <measure>
[employee]                <measure>


first page of output will look something like



Revenue                                                        2010
Camping Equipment       Data Item                 332986338
Golf Equipment              Data Item                 153553850
Bjorn Winkler                                                2396897


i'll post again if i am able to cook up something better

[Commander Cognos]

Thanks for the quick reply. So at least I was not totally wrong since I suspected to use a separate tuple. But somehow I thought maybe there is an easier solution for this.

[Francis aka khayman]

almost but not quite...

you can instead have [Data Item] = tuple(currentMember([XX]),[Y])

where
[XX]=heirarchy of Years
[Y] = root member of [employee]

you then nest [Data Item] with [Year]

Crosstab will now look like

                [Year]
                [Data Item]
[product]   <measure>
[employee]<measure>

[Commander Cognos]

Great it is working, indeed. However, the only challenge left is to keept the results after the page break. It is still filtered by the employee - no wonder due to the master/detail relationship. Is there a way to avoid the master/detail for a page break? Do you have any other idea?

Thanks already!

[CognosPaul]

master details on data items will essentially create a detail filter on each detail item.

You should have two queries
First is feeding the page set. That can be your staff level.

In the target report, replace your employee data item with a calculation:
#prompt('staff','mun','[all member]')#

In the master detail set the link to the staff parameter.

[Francis aka khayman]

you mean 1 page for each employee? use page set

[Francis aka khayman]

wow!! awesome! it worked. mind if i use this topic solution in my blog? this is really cool

master details on data items will essentially create a detail filter on each detail item.

You should have two queries
First is feeding the page set. That can be your staff level.

In the target report, replace your employee data item with a calculation:
#prompt('staff','mun','[all member]')#

In the master detail set the link to the staff parameter.

[CognosPaul]

Go for it! I've always thought the more people that blog about Cognos the better.

[Commander Cognos]

master details on data items will essentially create a detail filter on each detail item.

You should have two queries
First is feeding the page set. That can be your staff level.

In the target report, replace your employee data item with a calculation:
#prompt('staff','mun','[all member]')#

In the master detail set the link to the staff parameter.

You're genius, it works like a charm! Thanks a lot, I really appreciate your help!