I have a report which looks like this :
username1[section]
status 0-15 days 15 -30 days
Approved 2 3
Denied 1 0
In review 5 7
username2[section]
status 0-15 days 15 -30 days
Approved 2 3
In review 5 7
As there is no data for "denied" for user2 , it's not getting displayed.
But i want all these 3 (Approved,denied,inreview) to be displayed irrespective of data in the DB.
I can make them as 0 if there is no data. But i want the three to be displayed.
how to do this ......... :(
i know i'm asking something very basic , even i did it before , but its not working for me now ..... my version of cognos is 8.1.3
Thanks in Advance
You could do the following:
Have you tried using tabular model in combination with joins (outer joins) in RS?
e.g.
SQL 1: fetch all statusses
SQL 2: fetch all statuses for each user
SQL 3: Join SQL 1 & 2 based on 0..n
Maybe this can get you on the way....
I tried it . The problem is I'm applying a filter on date in sql2 . date > 12/31/2006. It actually returns 1 record for approved from sql 2. but it has to show other records from sql 1. i don't know where the problem is ... It's not displaying those records.
I tried even creating a left outer join between the status table and the other table. i created a filter on the other table on date and tested the data for the relationship. it's showing correctly like
Approved 2
Denied 0
Inreview 0
But when i apply the same filter at the report level it's just showing
Approved 2
I'm using Cognos 8.1.2
Is there any way to do this ....
Thanks in Advance
The problem is that I have a hunge... But I'm not able to write it down :'( Sorry... maybe it comes to me later....
Desperado
You are on the right track with the left outer join.
You can modify your report filter to something like this..
'date > 12/31/2006 or date is null'
The second clause will let the blank row appear in the report.
Try this and let us know if it works
HTH
NKT