Grouping isn't working in Congo's

[Bago]

I have 2 Items is duplicated in the report this items is shared across the Organization (4 org IDs) and shows is it zero stock or not

also, I create a data item to detect if its zero in this (A) ORG or not and did this to all organization (B) and (C)

Note: (ORG C) is a TWO Stock have different ORG ID location is deferent but they are one


the issue I'm having is:

there is Two item when I run the report xls. give me duplicate result


what I did is I but each Organization in SQL Query then I join them with 0n = 0n relation

1- (ORG A)JOINED WITH (ORG B) CREATING query Named MED

then some of the data items I need to not duplicated data and avoided null values I did this condition and give it name Planner code
 
case
when(    [PLANNER_CODE_ORG_A] is null)then([PLANNER_CODE_ORG_B])
else ([PLANNER_CODE_ORG_A])
end




Then I joined the query (MED) with (ORG C) and did the same as above to include all the Organization Planner code

Note: all the columns in the queries are the same



Report Duplication Issue:

I am encountering a duplication issue in my report concerning two items that are shared across four organizations (ORG A, ORG B, ORG C, and ORG D

*ORG C &D are the same but different location have different ORG ID *).

These items indicate whether stock levels are zero or not.



Summary of the Approach:


SQL Query Structure:

I structured my SQL queries to avoid duplication:

Joined ORG A with ORG B, creating a query named MED. with relation 0n = 0n

To handle potential null values and prevent blank in the report, I implemented a condition for the required columns example planner code:

CASE
  WHEN [PLANNER_CODE_ORG_A] IS NULL THEN [PLANNER_CODE_ORG_B]
  ELSE [PLANNER_CODE_ORG_A]
END AS Planner_Code

then Final Join:
The MED query was then joined with ORG C, applying the same logic to include the planner codes from all organizations.


Current Challenge:
Despite these measures, the report still generates duplicate results for two items when exporting to XLS.

here is attached photo




 



 

[Bago]

any help guys?

[adam_mc]

Just my thoughts...

Unless I'm missing something, in looking at your output, it would appear that your rows are not unique.
Both Items appear to have different Y/N flags for "Zero in B" and "Zero in C&D" - Cognos will auto-aggregate to the lowest level of detail (grouping) shown on the output.
Assuming this to be correct, you could remove those columns from your output and the report should aggregate to a single row for each item.
However, if you need to show those columns, it will never consolidate unless you can conditionally display only one row.

Thanks,
Adam.