If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Problem displaying data

Started by Desperado, 25 Jun 2007 02:45:22 PM

Previous topic - Next topic

Desperado

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

sir_jeroen

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....

Desperado

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

sir_jeroen

The problem is that I have a hunge... But I'm not able to write it down :'( Sorry... maybe it comes to me later....

cognosfreelancer

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