Please Help: Count Distinct with Conditions

[carollinho]

Hi All,
I have to create a summary report that shows different types of order count by Freight Carrier.  For example, No of Late Orders (where Ship Date > Planned Ship Date) , No of OnTime Orders(where Shio Date <= Planned Ship Date etc.

The expression I use for data item No Late Order is as follows:
if (ShipDate>PlannedShipDate) then (count(distinct [OrderNo]) else (0)

There are 4 orders that satisfy the above condition in the database but I am getting a 0 count.  I don't know how to get the count distinct to work properly. 

Any help is much appreciated.


Regards

[Sunchaser]

Hello,

What sort of database are you using ?
As soon as It's possible, I'm always using the "SQL native" syntax and try to solve anything and produce the fields used in reports directly from "SQL native" (i'm using Oracle DB); but It's a personnal opinion, and I don't mean it's the best way for anybody ...

Have you tried something with a "case" structure ?
Something like:
"
CASE
WHEN ShipDate <= Planned Ship Date THEN
     (count(distinct [OrderNo]))
WHEN ShipDate > Planned Ship Date THEN
     //something else ...
ELSE
     0
END
"

Hope it'll help a little bit.

[blom0344]

Remark1: Cognos does support the case construct as well.. Using SQL native syntax is almost never needed.

Remark2: Try using the expression without the count distinct, but set the aggregate property of the item to the proper value. That may seem to be another way of getting the same result, but for me it is the only way to force Cognos to return distinct counts

[carollinho]

Thanks Sunchaser and blom0344 for your replies. What aggregate property should I specify for this data item?  It is now default to 'automatic', should I change it to 'calculated' or 'total'?

[blom0344]

Nope, there is a special aggregate type available to perform distinct counts. Not sure how it called exactly (as I am using a Dutch version) but it is there somewhere in the list of aggregate types..