Hi,
Does anybody know how to prevent the “No data to fetch” message when a document is refreshed and a dataprovider doesn’t produce any data?
Regards,
Johan de Waardt
ABN AMRO Bank
Listserv Archives (BOB member since 2002-06-25)
Johan,
If you want to get rid of this message when you’re refreshing queries through a script, add the following to your script, and the window will go away:
dim appint as BOApplication
set appint = Application
appint.Interactive = FALSE
But be careful. This will make any and all pop-up windows go away. Once set for a script, this setting is true for the whole BusinessObjects session. So it may be a good idea to set appint.Interactive = TRUE at the end of your script to avoid hiding other windows during the session.
Regards,
Luis Gonzalez
Does anybody know how to prevent the “No data to fetch” message when a document is refreshed and a dataprovider doesn’t produce any data?
Listserv Archives (BOB member since 2002-06-25)
Are you using freehand SQL or stored procedures? If so, you can set up and if
exists clause and create default values. If you are using these, let me know and I
will walk you through the process.
Pilosio Christophe wrote:
I have a Business Objects document with 7 queries, is it possible not to
have the message “No Data To Fetch” during the refresh of all the queries.
Because the refresh is stopped until I press Ok.
Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (24 hrs. a day now!): listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’
====================================
Listserv Archives (BOB member since 2002-06-25)
I have recently found a “problem” with the way that I need to generate reports off my datamart using B.O. 4.1.5 and SQL 7 using ODBC.
If I am creating a report that uses 3 dimension tables and 2 or more aggregated fact tables combined with multiple queries then I run into a problem. If I use an object that is in the conditions but not in the results object the query does not return any data. I run the exact same query against the database inside SQL and I do get data returned. The only way for Business Objects to return data is to put any object that is the conditions section in the results objects section as well.
If I play around with the query too much then the report generates an ODBC error and I have to shut Business Objects down completely.
Anyone encounter this?
Simon
Listserv Archives (BOB member since 2002-06-25)
Simon,
You mentioned that you are using “multiple queries”. Are you linking the data providers? You also mentioned that “If I use an object that is in the conditions but not in the results object the query does not return any data.” How can you use an object in the condition to appear on the final report? From what I know, you can only use an object if it is in the result section. Please supply more info.
Rob
Listserv Archives (BOB member since 2002-06-25)
Hi Robert,
Yes, I am linking the data providers. I was trying to use an object in a condition where I did not need it in the final report. I was trying to use the object to narrow down the amount of data to be returned. You can use an object in the condition without putting it into the results section. Problem is that when I am using objects from 3 or more dimensions then Business Objects can’t always bring back data from a perfectly valid query unless whatever is in the conditions is also in the results object. Simon
Listserv Archives (BOB member since 2002-06-25)
Herjan,
I disabled no data to fetch using the dual table in Oracle.
Try the following:
- Import a table into the universe in question. 2. Rename the table dual.
- Create dummy objects in quotes eg ‘holder’. Your number of dummy objects should match the number from objects in the first query. 4. Union your first query. select your dummy objects
the SQL syntax (forgive the reference to Island resort marketing) should look like this:
Select resort, salesperson, service
from
Island_Resort_marketing
Union
SELECT ‘Character Place holder’,
‘Character place holder’,
‘Character place holder’
FROM
dual
)
This worked for me. Not sure about a non-Oracle enviroment, but you should be able to do it. You will now return one row of data. Create variable to remove the unwanted data.
Good luck,
Steve Bickerton
BO consultant, Chase Manhattan Bank
______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
Listserv Archives (BOB member since 2002-06-25)
Hi All;
I have another issue with “No data to fetch”. If there is no data, instead of displaying the default table headings, could we display a message on the report saying there was no data to fetch. Any suggestions?
Thanks
Listserv Archives (BOB member since 2002-06-25)
We have run into the same problem with the “No data to fetch” message. When you have a report that has five queries in it and the third query has “No data to fetch” it stops on the third query an does not continue. We would love to find a way to prevent this from happening.
Thank You,
Brian C. Mellinger
Walt Disney World
Security Loss Prevention Administration
Listserv Archives (BOB member since 2002-06-25)
It would be a nice enhancement if Business Objects provided a simpler way to do this such as a checkbox in the data provider or report, but this is how I’ve handled the problem. I posted this last month…
Re: Suppressing message box in BO report during refresh
A trick I have used to get around this problem is to create a class of “report objects” in my universe consisting of blanks and zeroes not attached to any table. I also create at least one of these objects that refences 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.
I can then take a report where I 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, and I can create a report filter to not display the union row. I have used this technique for long-running reports that contain multiple data providers where I do not want them to stop processing if one of the queries does not return data.
Listserv Archives (BOB member since 2002-06-25)
Sudhindra,
BusinessObjects 5 has the ability to superimpose blocks on top of each other and then display them based on various conditions.
-
Right mouse click on your existing block and goto Format Table. On the Appearance tab check the box that says “Hide Block”. In the conditions area put a formula like “=NumberOfRows(“Query 1 with Beach”) = 0”. You are just telling BusinessObjects to hide the block if a particular data provider returns zero rows.
-
Now just insert a cell right on top of the existing block. Put whatever text you want in the cell. Right mouse click on the the cell and goto Format Cell. On the Appearance tab check the box that says “Hide Block”. In the conditions area put a formula like “=NumberOfRows(“Query 1 with Beach”) > 0”. Here you are doing the same thing as above but you are telling BusinessObjects to hide the block if a data provider returns atleast 1 row.
It will help if you do this in structure mode.
This doesn’t eliminate the dialog box that says “No data to fetch” but it makes your report look a little nicer after you hit ok.
Hope it helps.
Kumar
I have another issue with “No data to fetch”. If there is no data, instead of displaying the default table headings, could we display a message on the report saying there was no data to fetch. Any suggestions?
Listserv Archives (BOB member since 2002-06-25)
Kumar;
Thanks a lot. This is what I was looking for. I will try it when I get a chance.
Sudhindra
Listserv Archives (BOB member since 2002-06-25)
Sandy;
Thanks a lot. You have done a lot of research on this. I will try it when I get a chance.
Sudhindra
Listserv Archives (BOB member since 2002-06-25)
Sandy;
This works great! Thank you.
The only issue I had with this was that a user would see this dummy class when he/she does a data/edit. Other than naming it as “Dummy - DO NOT USE” is there any other way around this ?
Thanks
Sudhindra
Listserv Archives (BOB member since 2002-06-25)
you can hide the class in the designer. but this will hide the class from all the users.
or u can put some security on this class so that only few users (who need to see) can see this class.
Sandy;
This works great! Thank you.
The only issue I had with this was that a user would see this dummy class when he/she does a data/edit. Other than naming it as “Dummy - DO NOT USE” is there any other way around this ?
Thanks
Sudhindra
******************************************************************************* Note: The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Ernst & Young LLP
Listserv Archives (BOB member since 2002-06-25)
Hello all,
We also wanted to get rid of the message ‘no data to fetch’. The solution of Sandy below seems to work fine, but I think we found an easier and more transparent solution.
We have made one dummy object. The where-statement of the dummy object only contains: ‘’ . It references an alias of a small table in our database. We put as self join on a column of this table (in our case: dummy_table.nummer = 1). Now only one row will be retrieved, whenever this table is queried. And now instead of using the union option Sandy mentioned do the following.
Create a context in your universe that contains only the self-join on the dummy table. If you didn’t use any contexts at this time, than you will have to put all the other joins in another context.
Now in the querypanel of BO make your query including the dummy object. If everyting is Ok than BO will generate two SQL statements, where one is the query of your choice and one is the sql to query the dummy table. This last query always returns one row, so no data the fetch will never pop up!
Another advantage is that the dummy object will not be linked to any other object in your query, so it doesn’t bother you at all!
This solution is tested on an oracle db and BO 5.01
Regards,
Robert Duindam
RAAPLEX
e-mail: robert.duindam@raaplex.nl
From: Sandy Smyth[SMTP:ssmyth@HORIZONCON.COM]
Re: Suppressing message box in BO report during refresh
A trick I have used to get around this problem is to create a class of “report objects” in my universe consisting of blanks and zeroes not attached to any table. I also create at least one of these objects that refences 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.
I can then take a report where I 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, and I can create a report filter to not display the union row. I have used this technique for long-running reports that contain
multiple data providers where I do not want them to stop processing if one of the queries does not return data.
Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (24 hrs. a day now!): listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’
DISCLAIMER******** Deze e-mail is uitsluitend bestemd voor de geadresseerde(n). Verstrekking aan en gebruik door anderen is niet toegestaan. Fortis sluit iedere aansprakelijkheid uit die voortvloeit uit electronische verzending.
This e-mail is intended exclusively for the addressee(s), and may not be passed on to, or made available for use by any person other than the addressee(s).
Fortis rules out any and every liability resulting from any electronic transmission.
Listserv Archives (BOB member since 2002-06-25)
Great solution!
If you are using Oracle, you could use DUAL rather than maintaining a dummy table in your database.
Listserv Archives (BOB member since 2002-06-25)
Thanks Robert for your clear instructions on how to get the no data to fetch message to disappear. I have a user that’s been requesting this for a while and your directions solved the problem really easily. I have a very complex report that was putting up 5 or 6 of these messages per document refresh and they have all now disappeared.
Thanks again.
Debbie Kelly
Museum Informatics Project
University of California, Berkeley
Listserv Archives (BOB member since 2002-06-25)
Hi Sandy and other listers,
I would have used dual, but unfortunately I can’t use it, because my dba won’t let me!
Sandy Wrote:
If you are using Oracle, you could use DUAL rather than maintaining a dummy
table in your database.
DISCLAIMER******** Deze e-mail is uitsluitend bestemd voor de geadresseerde(n). Verstrekking aan en gebruik door anderen is niet toegestaan. Fortis sluit iedere aansprakelijkheid uit die voortvloeit uit electronische verzending.
This e-mail is intended exclusively for the addressee(s), and may not be passed on to, or made available for use by any person other than the addressee(s).
Fortis rules out any and every liability resulting from any electronic transmission.
Listserv Archives (BOB member since 2002-06-25)
Hi,
I found this thread when searching for an answer to two similar problems.
a) If there is no data from a given data provider, is it possible to suppress the printing/display of that report all together?
The second one is a different situation but the suggestion on how to hide data conditionally seems relevant:
b) We are using ranking to obtain the top 1 but if there is a tie, we need to show however many rank as 1. That is working OK but we want to suppress the literal/label that we had set up to show in the first column – so that we see “Most Active Hour of the day:” 9am-10am 400 hits
11am-12pm 400 hits
I can’t figure out how to write a condition relevant to a tied ranking…
Thanks!
Listserv Archives (BOB member since 2002-06-25)