PA Crosstab: Show measures based on filtered children

[Commander Cognos]

Hi everyone,
I'm stuck with a very specific topic and maybe there is someone out there who has a clue and help me with this? Cognos Analytics 11.7.1. FP1, PA 2.0.9
I have a crosstab with product groups in rows and measure 1 and 2 in columns. I have the requirement to display only the product groups with children having values for measure 1. We can solve this with the filter command: filter(<Product Groups>,Measure1 > 0) (maybe some more details with a tuple)
However measure 2 calculates the total over all products. As soon as I nest the articel next to the product group it works. But once I remove it the values are wrong again. How can I give the crosstab the context to include the articel in the query? Or is there another way to make sure that both measure use the filter function?

Example data:

Product Group	Article	Measure 1	Measure 2
1	1	0	20
1	2	10	30
2	4	0	30
2	5	20	40

Example crosstab requirement:

Product Group	Measure 1	Measure 2
1	10	30
2	20	40

Example crosstab as-is:

Product Group	Measure 1	Measure 2
1	10	50
2	20	70

Thanks and best regards,
CC

[MFGF]

Hi everyone,
I'm stuck with a very specific topic and maybe there is someone out there who has a clue and help me with this? Cognos Analytics 11.7.1. FP1, PA 2.0.9
I have a crosstab with product groups in rows and measure 1 and 2 in columns. I have the requirement to display only the product groups with children having values for measure 1. We can solve this with the filter command: filter(<Product Groups>,Measure1 > 0) (maybe some more details with a tuple)
However measure 2 calculates the total over all products. As soon as I nest the articel next to the product group it works. But once I remove it the values are wrong again. How can I give the crosstab the context to include the articel in the query? Or is there another way to make sure that both measure use the filter function?

Example data:

Product Group	Article	Measure 1	Measure 2
1	1	0	20
1	2	10	30
2	4	0	30
2	5	20	40

Example crosstab requirement:

Product Group	Measure 1	Measure 2
1	10	30
2	20	40

Example crosstab as-is:

Product Group	Measure 1	Measure 2
1	10	50
2	20	70

Thanks and best regards,
CC

Hi,

Is Measure 2 being calculated (over all products) within the report using an expression (and if so, can you detail what the expression is), or is it being derived within the PA cube and just displayed in the report? Can you tell us where the filter() expression for Product Groups is currently being used in the report - is it in a detail filter, a slicer filter, in the row headings, somewhere else? Is Article a child level of Product Group within the same dimension, or is it a level from a completely different dimension?

Apologies for bombarding you with questions - we just need to understand more about how the report and the data are structured.

Cheers!

MF.

[Commander Cognos]

Thanks a lot MF for your fast response!

I will answer the question as best as I can  8)

Is Measure 2 being calculated (over all products) within the report using an expression (and if so, can you detail what the expression is), or is it being derived within the PA cube and just displayed in the report?

Measure 1 and 2 are from the PA Cube and dragged adjacent to each other - so no calculation.

Can you tell us where the filter() expression for Product Groups is currently being used in the report - is it in a detail filter, a slicer filter, in the row headings, somewhere else?

The filter is in a query calculation as a data item in the query. However, I'm bit struggeling to define that I need to display Product Group, but filter on Article. So the filter is actually on Article. Once I change this to Product Group, only those will be displayed with a total Measure 1 > 0 on the Product Group - which is not correct. I think in a relational world this might be easier - since you can filter on the detail data.
Edit: one helpful hint was the slicer. I actually moved the filter expression to the slicer and that indeed solved the issue!


Thanks MFGF!