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.
Advanced Web Intelligence Reporting Questions, Randomly Selected
I appreciate the questions that are emailed directly to me; many of them are good ideas for articles that fill a topic gap on the site. Others deserve equal attention, but they don’t exactly require their own article. This article is intended to capture and answer such questions related to Web Intelligence reporting.
My report has multiple queries (data providers) that use the same prompts how can I present the user with only one instance of the prompt?
This is part of the magic of Business Objects. Since BO 5.0, and probably before it, BO has supported
@prompt consolidation. This is to say that if you have two prompts that are exactly identical (same characters, and same options) then the user will only be prompted one time, but their submitted value(s) will be sent to all @Prompt and @Variable occurrences in report’s query(s).
Note: this question has piqued my interest because it raises two three questions:
- What happens if the @Prompts or @Variables vary in case (upper or lower case) only?
ANSWER: One prompt will be displayed to the user for each variation in case. - What happens if the @Prompts or @Variables vary in options only (such as one has “constrained” and the other has “free”)?
ANSWER: The first prompt (from top to bottom) will take the lead and drive the properties of the prompt displayed to the user. Interestingly, if the prompts vary in “mono” and “multi” settings then this will cause an error in most cases. WebI will not permit the prompt to be displayed and raise an error (see below) and Desktop Intelligence (DeskI) will allow it to pass, but if the result violates the SQL statement (multiple values sent to an equals operator, not an IN operator) than the user will get an error.
Error received in WebI when using two prompts that vary only in mono/multi settings
- What happens if the two Business Objects @Prompt vary in LOV (List of Values) only?
ANSWER: This is peculiar. If the LOVs are custom, then the first one (from top to bottom) will drive the user displayed LOV; the LOVs will not be combined. If one of the LOVs is based on an object’s LOV then this will override all LOVs and only the objects LOVs will be displayed to the user.
IMPORTANT NOTE: I experimented with both WebI and DeskI. I found WebI to be quite robust at handling quick changes to the conditions/prompts of a query. On the other hand, DeskI produced strange results because it was constantly caching the previous behavior of the previous test. So each time I tested a new option I needed to create a new report. This is interesting because it points to possible problems when creating a DeskI report. Certain changes to the DeskI reports I was creating were not properly reflected without starting a new report or completely exiting DeskI and returning to the report.
SHARE YOUR FEEDBACK: on this topic and all others I do, of course, welcome anyone’s experimentation or prior knowledge if they can send it in form of a comment or email.
Where can I find truly advanced WebI or DeskI report building resource?
Most advanced training courses of which I am aware are really only “advanced” if you have just started using BO. They probably contain a few really good snippets, but most of the content is geared towards getting beginners to the next level.
Honestly, I have found much of the best content contained in online BO reporting forums (Web 2.0 at its best) and much of it comes from the very generous contributions Business Objects experts such as Dave Rathbun and Steve Krandel (both have personally helped me out and inspired me to give back through this web site). Uncovering this great stuff has been to result of thorough researching of specific topics (both in search engines and using the search functions of the respective online sites. Please see our list of useful “links” for a good start.
If you know of any comprehensive advanced training courses or books (that don’t mostly rehash the simple stuff) please share the wealth through comments here or email me directly if you prefer.
@Prompt Functions: The Next Step – Optional Prompts
For most of us the @Prompt function syntax can be a bit confusing at first. Sure we catch on quickly, however, we often need to review the business objects @Prompt syntax available online or refer to a previously created @Prompt. After we get comfortable with the @Prompt and the available options such as custom list of values, constrained/free, mono/multi, and default/purge values (link to article), many of us are ready to take our @Prompts to the next level.
Prompt Conditions – Fancy Lines in a SQL WHERE Clause
Of course, if we are discussing @Prompt then we are discussing work done in Business Objects’ Designer. I suppose you could hard-code one in the SQL of a WebI report, but if you are doing that then I don’t think you are ready for the next level. Anyway, back to Designer, @Prompts are placed in condition objects (the little filters). Conditions become part of the WHERE clause of a report’s SQL. Therefore, much of what you can do with the WHERE clause in SQL can be replicated in Designer’s condition objects. This means that you can include AND and OR logic, for starters. This is where the magic starts, because “OR” logic is the drive behind optional prompt conditions. Anyway yo get the technical discussion started here is an example of basic business objects @prompt syntax:
Products.product_num IN @Prompt('Enter Product Number(s)','A',,MULTI,FREE)
Multiple @Prompt Applications Using @Variable
Let me take one step back and discuss the BO syntax briefly. @Prompts are translated into SQL by BO with the values submitted to them. Sometimes this translation is frustrating (dates date types for example). The interesting thing is that this translation can occur for each instance of the @Prompt in the report’s SQL. So you could place it in various parts of your report’s WHERE clause and each would be executed and evaluated. HOWEVER, the prompt values are all consolidated under a single @Prompt text string. This because the unique identifier of the @Prompt is the text displayed to the end user, you know the first string following the “@Prompt(“. Another way to reuse a prompt in the SQL of a report is to use the @Variable function with the exact same prompt text string. This allows you to use the input from the user without replicating all of the rest of the syntax in the @Prompt. Using @Variable is preferable where possible because it will avoid having to make changes to an @Prompt in too many locations (when changes are inevitably needed).
Creating a True Condition
SQL WHERE clauses function much like most logical programming; they evaluate to “True” or “False”. I don’t really want to get into a tutorial on logic, but it is important that you understand a bit about it. In short, this is what you need to know:
true and true = true
true and false = false
true or false = true
false or false = false
In order to create an optional prompt you will need to create a clause that has the ability to evaluate true based on a valid parameter value or a submitted keyword that you have configured for the prompt. Therefore you want an OR clause. Here is the key to the whole thing and it looks like this:
Employee.employee_id = @Prompt('Enter a value (* to bypass filter)','A','',MONO,FREE)
OR
'*' = @Variable('Enter a value (* to bypass filter)')
The keyword that is pre-configured in this prompt is: * (the asterisk character). So do you see it? If I enter a valid value for Employee.employee_id then my optional prompt clause will return those employees. If I enter a “*” character (minus the quote characters) the prompt clause will evaluate “TRUE” for all records and this will make as though the enter Prompt clause were not even present. It has been bypassed.
Standard Practices with Business Objects Optional Prompts
I have seen various keywords used over the years. The “*” (asterisk) is the most common. The word “All” is used by some, but when using letters you have to be prepared to handle or prevent mixed case responses. I have also seen the keyword added to a List of Values and the Business Objects prompt set to constrained to prevent upper/lower-case issues. This would for MONO or MULTI prompts, but please know that putting multiple values together with the keyword will still result in a complete TRUE clause/statement (or bypassing of it). Often the keyword is included in the prompt text or at least in any auxiliary training documentation. Remember, the keyword doesn’t help anyone who is not aware of it or how to use it. Finally, the keyword should not be similar, close to, or the same as any possible valid value for the prompt condition (nothing close to Employee.employee_id in this case); this would REALLY confuse the end-users of the BO prompt.
Optional Prompts in Business Objects XI 3
BO finally caught up with its competitors with the release of BO XI 3.0. In this release it began to include the ability to make report-level prompts optional through a check box. The unfortunate part is that this requires the user to create his or her prompts in the report and not follow the best practice of creating them in the universe. I haven’t yet seen any way to make a universe-level @Prompt optional based on configuration initiated at the report level. Perhaps this is by design; it allows the universe author to perfectly control both the composition and the optional nature of the prompt.
If you haven’t yet seen how to make a report level prompt option, please take a look at the image below. To get to this point you need (1) open the report or create a new one, (2) click on “Edit Query”, (3) locate a report-level prompt or create one, (4) click on the prompt properties button, it looks like a question mark in a blue circle over-lapping a small window with an “X”, (5) click on the “Optional prompt” check box, (6) click “OK”, (7) switch to the “Edit Report”, (8) test the prompt and save the report.
Modify Report > Edit Query > Prompt Properties > Optional Prompt
Selective Operators: Allowing Users to Decide Which Operator to Use
Everyone once in a while you come across something that makes you say, “that is great, now why didn’t I think of that?”. Recently I came across such a thing: an idea of how to make operators in the WHERE clause of a query definable by the user at the moment of report refresh through creative use of SQL and Designer. Honestly I think everyone could benefit from a greater understanding of SQL. This like piece of code is the proof of that:
Selective Operators in Business Objects XI Web Intelligence or Desktop Intelligence at Run-Time
( 'Lesser than or Equal' = @Prompt('Select Operator:','A',{'Lesser than or Equal','Equal','Greater than or Equal'}, MONO,CONSTRAINED) AND Emp.salary <= @Prompt('Enter Salary:','N',,MONO,FREE) )
OR
( 'Equal' = @Variable('Select Operator:') AND Emp.salary = @Variable('Enter Salary:') )
OR
( 'Greater than or Equal' = @Variable('Select Operator:') AND Emp.salary >= @Variable('Enter Salary:') )
The value in this case does not come from fancy Business Object prompt syntax, but rather from creative combinations of SQL and prompts. Note: Please remember this is just an example that you should use to learn the technique and then adapt it to your database’s SQL and to your business requirements.
How it Works
Although this example code has three clauses they can all be bound to each other through their placement in a single universe-level condition, or you could place them in multiple conditions for mixing and matching at the report level. The power of this logic is that it uses one dedicated prompt to collect the desired operator from the user: ‘Lesser than or Equal’,'Equal’, or ‘Greater than or Equal’. Then using hard-coded string values it creates a scenario where only one of the three statements can be true.
And Example with SQL Substitution
In each statement the hard-coded operator text is matched with the actual operator following “Emp.salary”. Therefore, if the user selects “Equal” for the @Prompt(‘Select Operator:’) and “1000″ for the @Prompt(‘Enter Salary:’) then the following substitutions will be made for the actual SQL run by Business Objects:
( 'Lesser than or Equal' = 'Equal' AND Emp.salary <= 1000 )
OR
( 'Equal' = 'Equal' AND Emp.salary = 1000 )
OR
( 'Greater than or Equal' = 'Equal' AND Emp.salary >= 1000 )
Because of the magic of SQL and logic only the clause ( 'Equal' = 'Equal' AND Emp.salary = 1000 ) can execute; the others are ignored because the first part of the SQL evaluates to FALSE. In the rules of logic a statement of “FALSE and TRUE” is always FALSE. In this case we don’t know if the second part could evaluate to true, because we don’t know the underlying data, but regardless the entire clause is FALSE when one of the components is FALSE and it is linked to the others using “AND”.
A Few of the Other Details
OK, I feel I got a little technical on the logic, but I think the examples speak clearly enough just in case I lost someone. Again, the whole point that I am making with this code is that it allows users at run time (a.k.a. report refresh) to select the operator for they want to use. If a user wants they can also use the Java Query Panel and change the operators all they want, but they would need access and training to accomplish this.
Some notes: In order to keep it simple I only used the @Prompt statement once and I use @Variable for all of the other instances of the prompt’s use. This helps to avoid problems if I accidentally set the @Prompt differently and it makes it easy to modify Prompt syntax later. The parenthesis around each clause is necessary. Please let us know what you think and share your modifications or spin-offs or alternate uses if you have any.
Extended @Prompt Syntax: Default Value, Purge Values
During my report development efforts the other day I knew that I needed a business objects universe-level prompt, but I also knew that it was mandatory to “purge” the prompt values. Like a good little Business Objects developer I always purge all of the data from my reports and reports’ prompts before I publish them for User Testing and promotion to production; however, in Business Objects XI R2 I have found that WebI reports have a strangle-hold on prompt values and they don’t want to give them up without a fight.
So I started looking around to see if I was just missing something obvious. What I found on my search was some completely unsupported extended @prompt syntax. The funny thing was that I did not have to install anything to enable it. The syntax worked immediately, just by passing in the additional parameters. Note: this is unsupported officially by BO, but BusinessObjects obviously developed it for some larger client and slipped it into the XI R2 code. Therefore, there is no promise that this will be supported in the future, but there is hope.
Anyway, on to the syntax:
@Prompt(’1) Enter Product Family’,'A’,'Product Hierarchy\Product Family’,multi,free,[Not_Persistent],[<{default_value(s)_or_leave_blank}>],[User:#])
With this syntax you can now add 3 additional/optional parameters after you universe-level prompt’s “free/constrained” option.
- Prompt Persistence: Default value is “Persistent”; however, the value “Not_Persistent” is the reason most Business Objects WebI report writers want to use this syntax. Using “Not_Persistent” removes the last prompt used with the report; it purges the value. This can be very important in many settings because it can be the difference between an end-user accidentally running the report for some unwanted parameter value.
- Default Value(s): If left blank no default value used. If a value or values is desired then the set should be surrounded by { } characters and separated by commas (much like hard-coded LOV values earlier int he @Prompt parameters). This feature might seem to counter the “Not_Persistent” setting; however, it may be possible that you always want your Web Intelligence report prompt to include the default “*” value even while you are testing or running it for other values. Therefore, some might find that using this option with the “Not_Persistent” option provides value and extended functionality to their WebI reports.
- User:#: This particular value has been ignored by many, but it also provides some interesting value. It allows the universe business objects developer to define the relative order of the prompt. Yes, no more not-so-tricky “1.”, “1)”, or “1-” prefixes. In fact, I believe this option provides better value because it defines order of universe-level prompts and it two prompts have the same “User:#” value then they are sorted alphabetically.
The following are some examples of potentially usage of this extended @Prompt Syntax:
@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,{'Kiwi','Guava'},User:0)
@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',MULTI,FREE,Not_Persistent,{'*'},User:9)
@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,,User:1
I have found this syntax to be invaluable in my BusinessObjects Designer and Web Intelligence report development efforts. As good little programmers we know that universe-level prompts are a “best practice”; however, without this syntax they can retain last-run prompt values and cause confusion and even misleading and blatantly wrong business intelligence. This syntax finally brings universe-level prompts into the 21 century and makes our jobs all a little easier and maybe even more secure. 
Tip: Including “All” or “*” In Your LOV
Try hard coding “All” or “*” in the object’s LOV, in the universe. You could do this by editing the object’s LOV and adding a query with UNION that will only return the word “All”. You may have to make sure that the LOV for the object is returning a character data type for this to work properly.
