Display of "nil" fields

[m3gd04]

I work at hospital and write simple reports in BO. Hope this is an easy one and you can assist me.

To simplify, Lets say I have the following 4 fields:

Case Date, Case #, Procedure, Free Comments

For some reason, certain days will not have "Free Comments" populated, therefore I will get a "no data to fetch " error message.

I tried to write Boolean variable but couldn't get it to work.

I'm just trying to get the report to display data in the other three fields despite the "Free Comments" field being nil. Yet, i'm having trouble getting this to work.

Any suggestions would be greatly appreciated.

Thanks

 

[blom0344]

The problem is in the dataprovider (as it returns no rows) and not in the report.

There is a elegant solution which means you have to adjust the definition of the universe object 'Free Comments':

CASE WHEN tableX.Free_Comments IS NULL THEN 'No comments available' ELSE tableX.Free_Comments END

The same can be achieved with the COALESCE (DB2 / SQL server) or NVL function (Oracle)

If you cannot modify the universe then you have to adjust your query.

Best practice is to make sure (generally speaking) that fields that are used for conditions never contain null values..


Ties Blom

 

[skrandel]

But, this also means that you need to have your universe designer make a change so that the there is an outer join to the table that holds the free-text. The problem is not one of calculation and display; it's one of retrieval.

What you've described, by definition is an outer join. I bet your universe doesn't have it.

Steve Krandel
Symantec

 

[blom0344]

Steve, you're right, that is the primary cause..

Ties Blom

 

[m3gd04]

Steve & Ties,

A million thanks for all you do.... You're the best!!!