[SOLVED]Define Contents in Crosstab renders same value of Data Item for all rows

[Filomena.Mignelli]

Hello fellow Cognos users,

I have an issue that I can't get around to understanding what Cognos is doing.  I have a crosstab in which I added an additional column using Crosstab space (with fact cells) called Total.  I am using Define Contents to populate the values of my new column Total.

I dragged my Data Item into the Crosstab intersection and added the Data Item in the Node properties.....Data Item displays no problem however it is repeating the same value for all the rows.

                              01-May   02-May   03-May   04-May   Total
Prod#01   Percentage   88.1%   96.8%   49.7%   0.0%   110.2%
Prod#02   Percentage   0.0%   22.8%   60.9%   61.7%   110.2%
Prod#03   Percentage   20.4%   0.0%   0.0%   40.5%   110.2%

when in fact it should be:
                              01-May   02-May   03-May   04-May   Total
Prod#01   Percentage   88.1%   96.8%   49.7%   0.0%   58.6%
Prod#02   Percentage   0.0%   22.8%   60.9%   61.7%   36.3%
Prod#03   Percentage   20.4%   0.0%   0.0%   40.5%   15.2%

If I run the report with just 1 Product....I get the Data Item value no problem.  What a mystery!!!

The Data Item expression used in the Define Contents is:  total ( [Percentage] for [Products] ) / count ( [Percentage] for [Products] )

BTW, when I view Tabular Data on the Query, the Data Item value has the correct value.

What am I doing wrong or what do I need to do so it displays the correct value in the crosstab intersection?

Many thanks,
Fil

SOLUTON:  I created another Query (lets call it Child Query) and I used the required Data Items for the Crosstab from my Mother Query.  I then created a new Data Item with the formula total ( [Percentage] for [Products] ) / count ( [Percentage] for [Products] ) called Total in the Child Query.  In this new Crosstab (pointing to the Child Query), I added the default Summary column and used the Define Contents to define and point to the Total data item.  Works like a charm :-)!

[MrKlar]

Hi Fil,
I ran into the same situation. I have YearMonth in columns and product category in rows.

In order to filter the measure (just for this particular part of the crosstab, I enabled the option "define contents" and added:

Code Select Expand

TOTAL(
CASE [Year Num]
WHEN 2018
THEN [Sales Amount]
END for [Product Category])
In the "properties" property of the Product Category I checked the calculated measure above. What Cognos gave me as a result is the total Sales Amount for each category for the year 2018, which is fine for that. But it completely ignores the YearMonths in the columns and repeats the total 2018 numbers in each column.

I tried your solution with "mother query" and "child query" but it didn't work. Is it possible for you to explain your solution in a bit more detail? Or maybe attach your report specification here?

Thank you in advance.