[Solved] List with empty cells

[otsizh]

 Hello,
I have a problem here. I have a list with two colomns:

Proj IDÃ,  Ã,  Amount
2221Ã,  Ã,  Ã,  32
2223Ã,  Ã,  Ã, 
2225Ã,  Ã,  Ã,  45

when there is no data for Amount field I have list without displaing the that row:
Proj IDÃ,  Ã,  Amount
2221Ã,  Ã,  Ã,  32
2225Ã,  Ã,  Ã,  45

I tired to change the data format and put something for is missing, is null options;
I tried to theng expression for Amount colomn:
if ([Amount]is missing) then (0)
I tried conditional variables, but still don't have luck.

please help
Olha

[sir_jeroen]

Have you tried an outer join on that table... that should work.
If not... switch to a cross tab instead of list.

[otsizh]

thank you for reply,
could you please give me some stepts how to do outher join in cognos RN, or it should be done in data base?

[BIsrik]

try a filter saying amount is not null.

Srik

[otsizh]

I have to display all Projects IDs ether they have Amount or not:
Project ID
2221    45
2223   
2225    34


but what I have is:
2221    45
2225    34

[otsizh]

Have you tried an outer join on that table... that should work.
If not... switch to a cross tab instead of list.

when i tryied to do other join i have cross joins mistakes  :'(

[sir_jeroen]

You have to create a Left/right outer join in Framework manager

[jjones_stats]

Try this:

Go into the query explorer (multi colored cube icon on the verticle toolbar).
Click on the Tablular model to view it.
On the left side of the window, go to the toolbox tab and drag a new "data Item" into the Tabluar Model's "data items' pane.

The data item's properties window should pop open.
In the "Expression definition" use the "CASE" function:
example:

CASE

WHEN
[your package name].[Project ID].[Amount] >= 0.01 

THEN
[your package name].[Project ID].[Amount]

ELSE 0

END

click "ok"
name this data item "total amount" (in the lower left hand properties window under "data item > name")

delete or cut the original "amount" field from your list report.

Let me know if this works for you. If not hit me on email: jjones@smarthealth.com I'll be at my desk most of the day.

[sir_jeroen]

Sorry Jones... but blank is not the same as 0 thus this won't work.

It can only be solved by an outer join or a complex union statement... But if I could choose I would go from list to cross tab....

[JoeBass]

With regard to the cross join errors:  In FM, choose Project->Edit Governers and select the radio buttons that allow cross joins and outer joins.

I'm not sure how you are constructing you query in RS but, there is a bug that may concern you:
A query built by joining two child tabular models will return a null result set if one of the two child tabular models returns a null result set - No matter how you attempt to join them in Framework manager

[otsizh]

I solved that problem with tabular set

Thank you all for help!