If you are unable to create a new account, please email support@bspsoftware.com

 

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

Started by Filomena.Mignelli, 30 May 2023 03:31:44 PM

Previous topic - Next topic

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 :-)!