Case statement issues

[Skykit]

Hi all,
I'll try and explain the issue without too much blurb!

I have a report that has a few columns and some based on case statements.  The problem is, I'm losing a line of data and can't figure out where it's going wrong.

Col 1= [KPI] which is a case statement.  Simply put, when [days] <= 1 then KPI, WHEN [days] >1 then Not KPI
Col 2= [Department] (List of departments)
Col 3= Count([Invoice_Number]

This in itself works fine.

The issue is when Col 4  is introduced
I need Col 4 to also be count([Invoice_Number]) but without taking into account the split by KPI.
So essentially, Col 3 should be the count of invoice numbers for the department for that KPI
BUT col 4 should be a count of invoices for the department regardless of KPI.

Eg: Col 3 might have 5 for department HR for KPI and 2 for HR in Non KPI.  Col 4 should therefore be a 7.
This seems to work until a department exists in KPI but not in Non-KPI.
When this happens, a further Col 5 which is a percentage difference column does not add up because the total Col 4 amount for KPI is different to the total Col 4 amount for Non KPI.

It almost feels like the case statement is doing an incorrect join but clearly its not joining.

I've tried to explain this in the attachment I have tried to mock up in Excel.
The total figure for "Total Invoice Count for Dept'" is 16 for Outside KPI which is CORRECT
The total figure for "Total Invoice Count for Dept" is 14 for Within KPI which is INCORRECT.  This should be 16.

What has clearly happened is that because "Within KPI" has no figures for Department C, it's missing off the line despite the figure existing for the overall KPI.

What I would expect is a Department C line, Null for Invoice Count for Dept by KPI and 2 for Total Invoice Count for Dept.

I hope this makes sense!

Data is relational and in RS 10.1

AJ

[BigChris]

Why not just do a simple crosstab?

[Robl]

Your column 1 is forcing a grouping of the data.
Try filtering on a single department and you'll probably see it appearing under both KPI and Non-KPI.
Cognos is deliberately not letting you double count the invoices for the depts under the different KPI's.

You need to force an invoice aggregation outside the normal roll up rules.

Try creating a custom calculation for column 4.
Something like
Total ([invoice number] for [Department])
then set the aggregation rules to None.

[Skykit]

Chris/Rob
Thank you both for replying  - I must turn on notification on reply which is why I have replied back so late!
Unfortunately, the requirement had to be a list report (no idea why but they would not have it any other way).
In the end, the fix was crude - to add a fake line in the database itself.

I'll get back on it soon and try the recommendations on here