I have a report that needs to contain 14 columns. There are 2 columns that are winticket amount and adjustments. These two fields are numeric fields for each row returned for a location. However if no amount is available or applies for a particular week, there is a null value in the fields. Therefore when the report is generated for that week those two fields are then missing totally from the report. The end users of the report need those two fields to be in the report no matter what. Is there a way to do this in Business Object so that even though the universe tables may have the fields populated with a null value, when the report generates those two fields still appear on the report? Thanks
it depends a bit. If it’s not a join problem and the database actually returns those NULL values then there are several possibilities.
In the universe, you could add a function like NVL (oracle) or ISNULL() (sqlserver)
In the report you can use a formula like =if(isnull()) then() else ()
I thought that the suggestions would work. However after giving it a try the 2 columns with a null value still do not appear. I looked at the data returned and the transaction codes for those two fields do not even appear in the list of transaction values, even though they are include in the sql. When there is a value in one or both of the fields then data appears. The fields are “winticket” and “Adjust” It’s almost like it sees there is no data in those fields and totally ignores the transaction codes. Now if I do this report with just those two codes it returns the message “No data to fetch” It does not list any custmoner ID or Name. Is there something else that anyone can think of? Thanks
can you post the sql it generated ? It sounds like you have a join or where problem.
Does the query run over multiple tables and is it possible that when you say that the fields winticket/adjust are empty it means that there’s now row for them which would lead (when using inner join) to your ‘no data to fetch’
(BOB member since 2005-08-01)