How can I supress the ‘No Data To Fetch’ message?
There are multiple methods of supressing the ‘No Data To Fetch’ message.
Programmatically…
If you copy this code into the ThisDocument module, it should work. To implement the code, you will need to have your Report which gives you the No Data to fetch message open. Click on Alt + F11, and the Visual Basic Editor will open. On the left hand side you should see a list of all the documents you have open. Under the name of the document which gives you the message you will see a icon called ThisDocument. Double click on it and on the main window, a blank sheet should appear. It not only suppresses the “No Data to Fetch…” message, but any prompts and so forth are also suppressed. It even turns off the hourglass cursor, so it can be confusing to the user.
Private Sub Document_BeforeRefresh(Cancel As Boolean)
Application.Interactive = False
End Sub
Private Sub Document_AfterRefresh()
Application.Interactive = True
End SubNote that this code is executed only when a full refresh is performed. If you run queries individually (from the query panel or Data Manager) the above events are not triggered.
By creating a union query…
One trick used to get around this problem is to create a class of “report objects” in the universe consisting of blanks and zeroes not attached to any table. Also create at least one of these objects that references a dummy table such as SYSDUMMY1 in DB2 (v5 and higher) or DUAL in Oracle. The objects that don’t reference a table will not parse in Designer, but they work fine as long as at least one object in the query does reference a table.
You can then take a report where you don’t want to see the “No data to fetch” message and create a union query using the blanks, zeroes and the object referencing the dummy table. This will guarantee that at least one row will be returned by the query. The zeroes added to numeric columns will not influence the result. Create a report filter to hide the union row. This technique can be used for long-running reports that contain multiple data providers where processing should not stop if one of the queries does not return data.
Using a dummy table and contexts…
The way to get the No Data to Fetch message to disappear is to put a dummy table in your database with a single row with a single column with a value of 1 or use a system table such as Oracle’s dual table. In your universe, add your dummy table as a class with a single object. Put a self-join on the dummy table. In the object definition put in the where clause that the column equals 1, so that the value will always be found by the sql. Put the self join in a separate context. It should be the only join in the context. Do not include the join in any of your other contexts.
Include the dummy object in your result set in your dataprovider when you don’t want the No Data to Fetch message to appear. The dummy object in its own context will cause B/O to create two datacubes for the query because two different contexts are used. Because of the self-join always being true you will always get some result. Just hide or delete the dummy column in your report.
Displaying a friendly user message if no rows are returned…
If you simply wish to display a friendly message on the report when no rows are retrieved, check here for one method.
Cindy Clayton 
(BOB member since 2002-06-11)