show null values in a graph

[mikiap]

Hi all

I have in an FM model, table1 & table2 with a left outer join.

1table: every day in the calendar:
day1, day2, day3, day4.

2table: day, record_id, group_id:
day1, record_id1, gr_id1
day1, record_id2, gr_id2
day2, record_id3, gr_id1
day3, record_id4, gr_id2

I want to be able in Cognos 8.2 Report Studio, to show all the days from table1 with the correspondent records in table2, like below:

table1.day1, day1, record_id1, gr_id1
table1.day1, day1, record_id2, gr_id2
table1.day2, day2, record_id3, gr_id1
table1.day3, day3, record_id4, gr_id2
table1.day4

I am able to do that as long as I show all values with no filters on table2.

When I put a filter on group_id=gr_id1, I want to see:

table1.day1, day1, record_id1, gr_id1
table1.day2, day2, record_id3, gr_id1
table1.day3
table1.day4

but instead it makes from left outer join, a direct join:

table1.day1, day1, record_id1, gr_id1
table1.day2, day2, record_id3, gr_id1
table1.day4

instead of:

I want to see all records in table1 and only the records in table2 for group_id=id1

Does anyone have a solution for this?

Thank you for an answer!
Miki

 

[drlex]

Miki,
When you put a filter on data that comes from a table that is left outer joined to the first, you make the join an inner one.
You'll need to either add the filter criteria to the join, or add a NULL as an alternative on the filter: group_id=gr_id1 OR group_id = NULL

soi là, soi carré

 

[mikiap]

Hi drlex,

Unfortunately I tried with NULL values already and it doesnt work.
If I add this condition, it takes from the table2 the records with group_id is empty (which exist) and it still doesnt take any day from table1 which has no record in table2 (in my example above: table1.day4).

Thanks
Anca

 

[blom0344]

You can very easily solve this by defining 2 seperate queries and adding the join in the report. This will effectively perform the filter BEFORE the join is executed which yields all rows from table1 and only the filtered ones from table2.

Ties Blom

 

[loysqc]

Blom is the best!!

 

[mikiap]

Thanks. I solved the problem with creating different queries and outer join.

Cheers
Anca