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
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
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.
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>
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!
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 mean 1 page for each employee? use page set
wow!! awesome! it worked. mind if i use this topic solution in my blog? this is really cool
Quote from: CognosPaul on 22 Mar 2019 09:22:14 AM
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.
Go for it! I've always thought the more people that blog about Cognos the better.
Quote from: CognosPaul on 22 Mar 2019 09:22:14 AM
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!