I have a report that I am trying to get information for. Sometimes the report returns data and sometimes it returns the message no data to fetch. We still need a report to show all zeros even if there is no data in the sales fields. There is customer ID’s and we need the report to at least return the customer ID and zeros for the sales field. I know updating the table with “0”'s would be ideal but that is not an option at this point. Is there a way to query the table and return the customer ID and transaction header for “Sales” & “Returns” if the field “Sales” and the field “Returns” are null so it shows a zero?
My understanding is you are looking for outer join solution.
Defining outer join at universe level, you can populate the customer id even if you dont have sales associated with him.
(Use of outer join is again debate topic)
Other solution at report level (BO versions which provides synchronous multiple data provider) is,
Create 1st data provider with customer id.
Create second data provider with customer id & sales measure object.
Link these data providers on customer id. Show customer id & sales measure in a table.
Format sales measure cell to show o for undefined value.
Sujit has given you a good approach for retrieving all customers. Just as an FYI, if you want to get rid of a “No data to fetch” message, here is one solution (courtesy of Debbie Kelly at UC Berkeley - thanks Debbie!):
(BOB member since 2002-06-20)