Author Topic: PA Crosstab: Show measures based on filtered children  (Read 414 times)

Offline Commander Cognos

  • Full Member
  • ***
  • Join Date: Sep 2018
  • Posts: 38
  • Forum Citizenship: +0/-0
PA Crosstab: Show measures based on filtered children
« on: 17 Sep 2021 03:24:26 am »
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 GroupArticleMeasure 1Measure 2
11020
121030
24030
252040

Example crosstab requirement:
Product GroupMeasure 1Measure 2
11030
22040

Example crosstab as-is:
Product GroupMeasure 1Measure 2
11050
22070

Thanks and best regards,
CC

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,376
  • Forum Citizenship: +665/-10
  • Cognos Software Muppet
Re: PA Crosstab: Show measures based on filtered children
« Reply #1 on: 17 Sep 2021 07:21:40 am »
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 GroupArticleMeasure 1Measure 2
11020
121030
24030
252040

Example crosstab requirement:
Product GroupMeasure 1Measure 2
11030
22040

Example crosstab as-is:
Product GroupMeasure 1Measure 2
11050
22070

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.
Meep!

Offline Commander Cognos

  • Full Member
  • ***
  • Join Date: Sep 2018
  • Posts: 38
  • Forum Citizenship: +0/-0
Re: PA Crosstab: Show measures based on filtered children
« Reply #2 on: 17 Sep 2021 09:41:32 am »
Thanks a lot MF for your fast response!

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

Quote
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.

Quote
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!

« Last Edit: 22 Sep 2021 02:27:56 am by Commander Cognos »