Dimensional Report - how to filter based on Dim's attribute

[Prof User]

Hello,

I would like to create the following result
Expected result in Cognos report studio:

              Cat1            Cat2            Cat3
           EN NA UG   EN NA UG    EN NA UG
          -----------  -----------  ------------
2015    22  11  8    12   13 6     7   6   9
2016     3    6                 8
2017     9    11        1     6             4   4

based on my TM1 cube structure:

Dim: "Category"  (Cat1,Cat2,Cat3 are members of this Dim)
Dim: "Year"        (2015,2016,2017 are membes of this Dim)
Dim: "Product"   ("ProductLevel" has been defined as an attribute on this Dim. EN, NA and UG are the values of this attribute)
Measure: "Qty"

if I just drag and drop the "ProductLevel" as column, it will be repeated against every single product which has this value:

              Cat1                           Cat2                                       Cat3
           EN EN EN EN NA UG   EN EN NA NA NA UG UG UG UG    EN NA NA UG UG UG
          ---------------------    --------------------------------    -----------------------
2015    2   5    5  10  8                                                          12   13 6     7   6   9
2016          1    1  1   6                          2  2
2017    7          2       11        2             4   4

when I add the ProductLevel into the Detail Filters ([ProductLevel]='NA') area then the measure is aggregated but I won't have the rest of columns related to UG and EN  :(

Could you please assist how I can use any function to achieve the expected result.

Thanks

[BigChris]

Repeat after me "Detail filters with dimensional data is a bad idea"  8)

It looks to me like you've got something odd set up in terms of aggregation...I would have expected you to get the results in your original example. Might be worth checking (or have someone check for you) the structure that's been set up in your TM1 cube.

[Prof User]

Actually the design of this TM1 cube is not perfect but it is the best to the other restrictions we face with.


I ask my question in another way:
Is it technically possible to show the crosstab below, in Cognos report studio, when the columns(EN,NA,UG) are the attributes of a dimension?


              Cat1            Cat2            Cat3
           EN NA UG   EN NA UG    EN NA UG
          -----------  -----------  ------------
2015    22  11  8    12   13 6     7   6   9
2016     3    6                 8
2017     9    11        1     6             4   4


TM1 cube structure:

Dim: "Category"  (Cat1,Cat2,Cat3 are members of this Dim)
Dim: "Year"        (2015,2016,2017 are membes of this Dim)
Dim: "Product"   ("ProductLevel" has been defined as an attribute on this Dim. EN, NA and UG are the values of this attribute)
Measure: "Qty"

Thanks

[rockytopmark]

I suspect it is not possible.  Each column is representing a unique member of the Product level.

Is it possible to add a "ProductLevel" level (above the Product level) that would represent the natural grouping by the (current) product level attribute?

All Products (all level)
|--- ProductLevel (level)
|-------- Product  (level)

[Invisi]

I agree, to me it sounds like you should tell Cognos this is a level in your hierarchy. Or check the summary settings of your measure fields?