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
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.
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
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)
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?