How to remove the repeated rows when calculating classified counts?

[lookingforK]

Hi,

I am using Cognos Report Studio 8.4 to extract the sales data for Last Week.

The data should be pulled out like:
Product Class Type...... Last Week Sales
Prom ...... ...... ...... .......  *
Non-Prom ...... ...... ....... *

Here, "Last Week Sales" should be the sales figures for 2 product classes (i.e. Prom or Non-Prom). "Product Class Type" means that: if "Product Category Number = 999", then it refers to 'Prom'; otherwise, it is classified as 'Non-Prom'.

For achieving this goal, I used 2 steps for "Product Class Type":
1). Used the following expression

Code Select Expand

CASE
WHEN Product Category Number = '999' THEN 'Prom'
ELSE 'Non-Prom'
END

2). Used the aggregation property as Automatic

For "Last Week Sales", just used Count Distinct for the aggregation property.

But, the results I got for 2 product classes are repeated by several rows.
For example, I should get:
Product Class Type...... Last Week Sales
Prom ...... ...... ...... ....260
Non-Prom ...... ...... .. 370

But, I got:
Product Class Type...... Last Week Sales
Prom ...... ...... .......... 260
Non-Prom ...... ...... .. 370
Prom ...... ...... .......... 260
Non-Prom ...... ...... .. 370
Prom ...... ...... .......... 260
Non-Prom ...... ...... .. 370
Prom ...... ...... .......... 260
Non-Prom ...... ...... .. 370
...

How to deal with this problem and remove the repeated rows?

Thank you in advance.

[Bark]

It appears like it's repeating the data item as many times as rows you are bringing back from the DB. Either group the list by Prom/Non-Prom on remove from your query all the detail elemens that might be making you bring more lines than needed.

What do you see if you run it for tabular data?

Regards,

Bark

[lookingforK]

Thank you Bark.

How to run it for tabular data?

One more thing, I removed the column "Last Week Sales". The result I expect to see is like:
Product Class Type
Prom
Non-Prom

But, I got:
Product Class Type
Prom
Non-Prom
Prom
Non-Prom
Prom
Non-Prom
Prom
Non-Prom
Prom
Non-Prom
Prom
Non-Prom

It repeated 6 times ...

[rperwin]

to run as tabular data you have to hover over the Query Explorer and then 'right click' on the corresponding query and select 'view tabular data'

[rperwin]

Also - is this based off of a relational model?  If so, is the Last Week Sales data item set as a measure?  You may have to change the aggregate function to 'Total' (which has to be done in the properties of the data item)

[lookingforK]

Thank you rperwin.

Still try to understand ...

[lookingforK]

Thank you rperwin.

Finally, I understand ...

This problem has been solved.