BusinessObjects Board

Suppress "No Data To retrieve"

building reports webintelligence
#1

Hi all,

I know this has been asked umpteen number of times.But i couldn’t find the answer to it.
How can we supress the “No Data to Retrieve” message when there is no data?
Iam on WebI XI R2.

Thanks!
Sarma

pvsbandi (BOB member since 2005-05-13)

Data fetch message
#2

Hi
In faq you can find several solutions

And search No Data To Fetch’ message
Good luck!

Jaimepe :es: (BOB member since 2006-12-10)

#3

Thanks, but i don’t see a solution nor can i get anything back when searching on No data to fetch.

pvsbandi (BOB member since 2005-05-13)

#4

Check if this topic helps.

Jansi :india: (BOB member since 2008-05-12)

#5

Thanks Jansi! This won’t work in my case because users are playing with the report and so, the SQL changes every now and then.

pvsbandi (BOB member since 2005-05-13)

#6

I dont know why you dont find this in faq’s:
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.

Code:
Private Sub Document_BeforeRefresh(Cancel As Boolean)
Application.Interactive = False
End Sub

Private Sub Document_AfterRefresh()
Application.Interactive = True
End Sub

Note 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.

But i copy for you.
Good luck!

Jaimepe :es: (BOB member since 2006-12-10)

#7

Thanks Jaimepe :stuck_out_tongue: .

This topic saved my time and work. I could add the ‘select null from dual’ in oracle and it suppressed the mesasge.

bobuser9 (BOB member since 2007-04-20)

#8

If we don’t suppress, would this affect the schedule.
I am on Webi XIR2.
Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

#9

No.

Nick Daniels :uk: (BOB member since 2002-08-15)

#10

…or you can just use the scheduler instead of refreshing manually?

rdougherty :us: (BOB member since 2008-10-06)

#11

NICE solution, it works like a charm…thanks for the advice and the examples.

deacjbt :us: (BOB member since 2009-09-24)

#12

Hey it was helpfful and Using a dummy table and contexts… i was acutlally was able to suppress “no data” message…

but the raw in webi report is showing

#incompatible Error

i want to put NONE instead of that error

Pls let me know if its possible

Thanks in advance

broccolimustdie (BOB member since 2010-06-17)

#13

I FOUND WHY THAT # INCOMPATIBLE ERROR WAS COMING…

WHEN YOU PUT SELF JOIN IN SEPRATE CONTEXT MAKE SURE YOU HAVE ALL OTHER JOINS IN SOME CONTEXT AND IF THERE IS NO OTHER CONTEXT MAKE A CONTEXT WHICH CONTAINS ALL JOINS EXCEPT THE SELF JOIN AND YOUR PROBLEM SOLVED.

THANKS :+1:

broccolimustdie (BOB member since 2010-06-17)

Disable message when query is empty
#14

Yes, once you start using contexts every join must appear in at least once context - something to always remember.
Glad you sorted it, please remember capital letters make you sound LIKE YOU ARE SHOUTING so please don’t write entire posts with them :wink:

Nick Daniels :uk: (BOB member since 2002-08-15)

#15

Oh my bad

will keep that in mind… :slight_smile:

broccolimustdie (BOB member since 2010-06-17)