Understanding and Suppressing the Report Message “No data to retrieve”
Receiving the dreaded “No data to retrieve in …” or “There is no data corresponding to this query…” pop-up boxes can be confusing and disorienting for many end users. Heck, it even bewilders me sometimes when it pops up unexpectedly. The truth is that there are many occasions when this “error” is quite valid and even desirable. Like almost everything, it depends.
This is the error as seen from the InfoView Web Intelligence HTML Viewer
Causes for the “No data to retrieve in ” message
Firstly, let’s explain what causes this. Simply put one or more of the queries in your report is not returning any records. So what causes that to happen? Many things:
- The data is missing in the database. This one can be either expected or very scary for the the business intelligence administrator.
- The filters on the query are too restrictive or negating each other. Here the filters will also be a product of the values that you submit to any report-level prompts. Of course, if you for example select the value “Sacramento” for a city, the value “Chihuahua” for a state, and “India” for a country; then unless each prompt condition is joined to the other prompts with “Or” logic you should expect the “No data to retrieve” message.
- Row-level restrictions and other kinds of “universe overloads” can ultimately cause the final SQL submitted to the database to evaluate false for all records in the tables. This can be more subtle and can be revealed by studying the SQL.
- Universe connections can be changed and redirected to different database with similar structures but less or different data. No integrity or structure errors could be raised. This might also happen when moving a universe between environments and expecting the universe connections in all environments to be the same.
- Unbound reports can suffer from this. They can attach to different universes without anyone noticing on the front-end. This is especially true when more than one universe share the exact same objects. I have seen this with universes based on calendar years, for example.
This is the error as seen from the WebI Java Report Panel
How do I suppress the “No data to retrieve” message?
There are times when you may expect that certain users will not receive data for any or all queries. In many cases, you have designed the report to handle this case, but you may not want the user to be bothered by the “No data to retrieve” message. This is a common requirement. There is a hard, non-standard way of suppressing the message using a VB macro or some other SDK integration, but I don’t recommend this route; too much overhead and too hard to maintain.
The easier way is to handle this at the query level. If you want to suppress the error, make the cause of the error go away. What? Yes! Create a union in your query such that always one of the queries returns a value. In some cases you can use queries you already have and just splice them together, but in most cases, the simplest way to create this is to add a second SQL UNION query with the same number of objects and object types in the “Result Objects” and make it always return one value. Then you may need to filter this value out of your reports by adding a simple report level filter in order to keep everything clean. You may also use this value in a variable or alerter to present the report user with your own custom message regarding the absence of data returned by the report queries.
Additional Notes
This error is seen in all forms of Web Intelligence (viewers and Java Report Panels) and Desktop Intelligence. The suppression method of creating a “UNION” in the “problem” query will work for all of the different flavors of reports. That is what also makes this a superior work around. When possible, I ALWAYS recommend avoiding the use of macros and other hacks that are not part of a standard BO solution. Of course, if you do use this 100% BO union hack then you may want to keep track of it in a document or in the report comments for future generations of report jockeys who may have to support it, migrate it, or reverse engineer it.
Business Objects Tips and Tricks for Web Intelligence Reports – October 2009
Happy Halloween to all. I am going to try to start publishing short to medium length lists of my favorite tips. These have helped me a lot over the years and I think if I just try to jot them down as I use them I can end up with a fairly decent list of Business Objects tips and tricks that I can post with some kind of regularity.
- Use the “View my documents…” preferences setting of “fullscreen browser window”: There are actually two options and I believing that I am a power user, prefer to use the “in multiple fullscreen browser windows, one window for each document” option. If you don’t use one of these “fullscreen” options you are probably working less efficiently than you would otherwise. Do yourself a favor and try this tip out!
- Save New Versions Often: For many years, in full client (a.k.a. Desktop Intelligence) and in Web Intelligence Report creation and modification I have regularly used the “Save As” option every time I make a change that is just a bit more complex than a few formatting changes. I use the technique of starting with a base report name and then appending the date numerically to it. I don’t get down into a numerical representation of the time as it would be tedious I just use letters, starting with “a”. For example, the first version saved off today would be saved as the name “My Awesome Report 20091029a”. Doing the date and the letters in this format allows for simple alphabetical sorting within the InfoView or CMC object list. Later if the report becomes corrupt, or my great idea causes a huge mess I just start stepping back in version until I find the last known good one.
- Relative Positioning of Tables, Charts, and Cells: If you haven’t yet used this feature then your report design must be rather limited or just simple. I relatively position all of my tables, charts and cells. Always. Even if there is just one table on the report table I use relative positioning to get teh table exactly where I want it in relation to the page margins. Many times I am mixing tables, cells, and charts on the same report tab/page and I use relative positioning to make sure they never bleed together and that they are always spaced the same no matter how much data I pull in. My favorite trick is to create multiple tables and relatively position them to look like a single table. This is great when trying to make BO’s reports look and feel like the business user thinks they should.
How do you do this Business Objects magic trick. Easy, just click on the table, chart, or cell until you see a border around the object with a very small checkered pattern (with tables you need to click the border, with the other just click the object anywhere). Then right-click (with table, right-click the border) and select “Position”. Then your choices are flexible: horizontal, right and left, and vertical, top and bottom. Each one allows the space to be defined in pixels (px). Experimentation will pay off in allowing to to create some very professional looking reports and possibly even satisfy some otherwise impossible business requirements!
- What you see is not always what you get: For 98% of the components of a report what you see in the Java Report Panel is what the users will see in the regular HTML viewer they will use to open, refresh, and view the report. But there is that 2% that is not really quite the same (I just picked a low number, don’t quote me on the number). Most notably the prompts; they do not work exactly the same. Date prompts have different controls and selected dates in the HTML viewer will append the time to the date.
- Where is my data cube? What exactly was returned by the query?: Open the WebI report in the HTML viewer and click on Document > Save to my computer as… > CSV. The output will include all of the data from each query. The first query’s data followed immediately by the second query’s data ans so on. This is a good way to check things out without any concern about whether a BO table with all of the query objects is actually aggregating the query’s data when you don’t want it to.
- Cross-tab tables with totals in the first column: OK, this is nitpicking, but I keep finding business requirements that specify the totals should be in the first column and then the cross-tabbed data. Try to do this and you will find yourself in cross-tab hell. That is unless you click on the last dimension on the left and then add a column after. You just added a column in the “dimension” zone and now you can throw a measure variable in it. However, be prepared to format the column quite a bit to get it to look like a measure column; by default it will inherit the formatting given to measures.
- Alerts don’t work on dimensions: Yeah, this one sucks. I try about once a year to get an alerter to allow me to manipulate a measure in the header, for example create borders around quarters. It won’t do anything. Alerters are intended to bring attention to or apply condition formatting to measures. The alternative is to come up with some compromise involving semi-complex variables using the dimension objects.
TRUTH: Business Objects Does Not Create an Excel File, Help!
A couple weeks ago we received the best email question so far. Not only did it ask for more details with regards to one of the articles, but it provided insights worthy of making it its very own standalone article. Truthfully, I would like to publish it here to share the knowledge contained inside (this is always the best way to answer questions sent to us), but also to hopefully bring visibility to a very worthy question that you may be able to answer. Please read this one, it is good, and provide any comments or questions you might had in the comments below.
Our Guest Author wrote the following [minimal edits for context clarification from Julian are in brackets]:
Subject: [Excel] Format issues when Schedule a DESKI report in BOXIR2 SP3
We are trying to schedule a DESKI Report 3 times a week in Excel format to our clients. DESKI report has 1 data provider and 1 report tab only. We are on BOXIR2 SP3.
[A client] has an automated process, which reads the files we send and save it somewhere. They have a problem in reading Excel file which we send it through our BO scheduler. I tried to schedule DESKI document through InfoView using both SMTP and FTP [delivery] options.
From the InfoView -> scheduler
1. Excel format is generating Excel readable file — but they want exact Excel file.
2. Text file format is generating ‘Tab’ delimiter file
3. There is no option for CSV from scheduler [link to related article]
I am really kind of stuck in the middle (between clients, business users, and my manager). Clients want either an Exact Excel file or CSV file on a schedule basis. As there is no other way to schedule a report in CSV Format [link to related article], we have to write macros/scripts to do this. Which I am ready to do, [but] our management and team don’t want to do this workaround. [They want to] avoid macros as it will increase maintenance and users have to depend on IT for this.
New Knowledge Alert – Business Objects Cannot Create True Excel Files, only Excel Readable Files
I have opened a ticket with BO support and this is the answer I got from them.
Business Objects uses its own dll files for exporting a report to an excel format. The file created is not a native excel file and is simply excel readable.
Any file converted to excel using a tool other than excel will always create excel readable file which would be different (but not too much) from the native one created in Microsoft Excel.
Business Objects does not require Microsoft to be installed for this conversion to take place as it relies on its internal files for the conversion. BusinessObjects is designed only for creating the reports, though it can export the reports to the other formats.
But since all the exported files contain similar information such that Microsoft Excel can interpret and render the files, your module should be able to access it. The files exported from Business Objects would not differ greatly from an excel file created in Microsoft. I am not sure what these internal differences would be.
[The following response captures the client's requirement/comments]:
I think we are stuck in this issue now because [BO] was intended to create an “Excel-readable” file, not create an Excel file. My Java module [is] trying to read an Excel file, not an Excel readable file. There [is] nothing wrong at both ends.
Unfortunately, the data file just cannot serve the purpose that I am looking for. We would need to find some other options now.
I am not sure if [BO] is able to create a “CSV” file? This is a general purpose type of file format and would not have any format issue in reading. Also, you did provide a TEXT file for my testing, but it’s not a fixed length TEXT file. Seems that tab was used as a delimiter in that text file. I wonder if you are able to create a text file without delimiter?
I know that we can save a report in CSV format also in a fixed length text format – through DESKTOP Intelligence
but is there a way to schedule this.
Is a schedule CSV option available in BOXIR3.1???
Please let me know if there is a way to schedule a DESKI report in CSV format.
Responses from BusinessObjectsTips.com’s Julian
The more I think about this I have to say that macros are the only solution that will meet the needs client, unless they can somehow do SDK integration of some sort and pull the data right off the report.
Another solution might be to create an Excel “converter” application. This could be a standalone Excel file with a macro in it that loads BO’s native Excel readable format and outputs a true Excel format file. Perhaps a standalone executable would be better because it would allow for scheduling (perhaps using a BO Program Object). I don’t really know how to create such an application, but this actually would not require any BO skills.
To the author, please share your progress with us all. To the rest of our audience please share your ideas and comments. Thanks!
WebIntelligence Report’s Save As CSV Has a Problem?
Like a good obedient BO disciple I switched from using BO 6.5′s “full client” (now called Desktop Intelligence) to using BOXI’s Web Intelligence tool (also called WebI). To Business Objects’ credit, they have put much of Desktop Intelligence’s functionality in to WebI. But there is one disorienting difference.
BOXI Web Intelligence “Save to CSV” Is Different
My recollection tells me that if I exported a BO 6.5 Full client report to CSV that I received a CSV file with the same content that I would get through a Microsoft Excel export. Sure formatting, such as colors, column width, font, etc. were lost, but the order of the columns and even the report level variables in the table were exported to CSV from full client. Maybe, I a wrong about this (please point this out if I am), but that is the way I and some colleagues remember it.
BOXI Web Intelligence (and perhaps BO 6.x Web Intelligence) also has the ability to export to a CSV file, but this export behaves very differently. This export behaves much like the BO 6.x “View Data” or the “Data Cube” functionality. The BOXI WebI CSV export is a dump of the exact results of the first query of the report just as they were received from the database.
WebI CSV is a Query Result Export, NOTHING MORE
When you export a WebI report to CSV you may not get what you expect. The results should be identical to those you would get if you took the query and ran it directly against the database. This may be disappointing to some because it does not include any report-level aggregation, calculations, sorting, breaks, character formatting, or column ordering. HOWEVER, if a report developer is debugging a query or a universe object this output can be invaluable. For example, it can quickly uncover issues (such as Cartesian products) that dimensions can hide in a table. There are many uses for this output.
Why Does WebI’s CSV Have This Limitation?
I don’t claim to have inside information, but I can guess at two reasons for this limitation.
- Report Development Debugging: I know I already mentioned this, but I really want people to use the CSV export to understand more about their query and report. This previously perceived limit is actually advantageous functionality in the right hands.
- Discouraging ETL Use of Business Objects: If CSV were to output the aggregation and calculated columns of a report it would be readily used by end users as an ETL tool (Extract, Transform, Load). Some users still use it this way, but large scale use would explode if CSV exports were more than query dumps.
What Can I Do If I Want a CSV Export of My WebI Report Table’s Data?
There is almost always a workaround. If you must have your table’s data in CSV format then the solution is simple: export to Excel, open Excel output, save to CSV. You’re welcome!
