Posts Tagged ‘Web Intelligence’

What Are the Differences Between Breaks and Sections?

Meridianblues asked us all what the difference was between breaks and sections. I started to answer the question where it was asked, but then I thought the answer might be of value if exposed more broadly in its own article:

What are Web Intelligence Sections?

In Business Objects XI Web Intelligence sections are easily created and often even by mistake (drag a single object to the report and you will see what I mean). They can be made more complex and wider (taller) with greater scope. Sections apply to the entire horizontal area of page over which they are defined (best viewed/managed in Structure View). In other words, you can have multiple tables, graphs, cells in a section if you want. The section is also defined usually by a dimension so that each unique value of the dimension becomes an instance of the section in which all content placed in the section is repeated. All of the content in that instance is filtered to that unique dimension value.

So what are Breaks then?

A break is something that I rarely seem to get these days. No seriously, a Business Objects WebI report break has similar properties to those of a section. It is a sort of pivot around each distinct value of a dimension or formula. However, a break occurs only in a single table/block. Breaks cause the table to be broken up in instances of the break, sub-tables usually. These are easily created, usually by highlighting the desired dimension’s column and then right-clicking or section the top-menu icon.

A Distinction between the Two

Sections can be applied and formatted to function more or less like breaks; however, breaks can never function as sections do, they are limited to the scope of the block on which they are defined. I tend to prefer sections as they allow for greater flexibility and they seem to have more options. Nevertheless, breaks can be excellent for grouping relatively small amounts of data and then aggregating the measures at the bottom of the table.

Personally I would love to hear you all opinions or observations on this topic. Please share openly in the comments.

Web Intelligence Hangs, Allocate More Memory to Java

Myth: Business Objects XI Web Intelligence Requires No Software Install

Business Objects IX’s Web Intelligence is a light client. You may think that it does not require you to install anything on your PC, but then you would be wrong. Firstly, obviously WebI requires that your PC have a browser. Secondly, Web Intelligence Java Report Panel requires that your client PC has a Java Runtime Environment (JRE) installation (1.5 and 1.6 are good to go).

Third, your client will need to download the WebI Java Report Panel application (in the form of temporary Internet files stored usually in your JRE installation folder). If you ever dump these (for example, as discussed in the article “Clearing the Browser and Java Cache: Do This First!“) then you will notice that your subsequent visit to the Java Report Panel (create new or modify a document) will result in a longer load time while your web client downloads the BO WebI java application.

Java Runtime Environment Installs with a Wimpy Configuration

By default I believe that the default Sun JRE’s will almost always install themselves with a very conservative (small) heap/memory consumption size. These settings can be managed on your client PC by going to “Control Panel” and double-clicking on “Java”. Once there click on the “Java” tab and then click on the “View…” button:

Java Control Panel Parameters

Web Intelligence Power User? Put Your Memory Where Your Mouth Is

Once you know which one to tweak and you focus on the “Runtime Parameters” you will see that there is an “Xms” and an “Xmx” parameter here. “Xms” is the minimum starting size of the heap or memory allocated to the process. “Xmx” is the maximum or limit of the heap/memory allocation. A good starting point here is: -Xms128m -Xmx256m, but you can go higher if you feel you and your PC are up for it. This link points to a great article on the topic of mistakes to avoid when configuring these parameters. Most importantly, always include “m” or “g” at the end of the number, never exceed your machines physical memory, and I read some where else that the effective limit here is 1 gigabyte on Windows XP/Vista (-Xms1g -Xmx1g). Sun has a good guide on this topic too(link).

Making the Change

I would first back-up your current settings, email them to yourself or something like that. Make the change and follow the syntax. Change the setting(s), then click “OK”, then click “Apply” then click “View” to confirm your changes were saved. If you do not click “Apply” and you click “View” right after clicking OK your changes will be wiped out. Of course, you should probably not be using your JRE while making these changes, or at least close everything down and restart your WebI Java Report Panel after the changes are made. I don’t think rebooting your PC is necessary. Be sure to test your changes by launching Web Intelligence Java Report Panel and keep your eye on your system’s memory to see the impact there while testing your wonderful WebI skills to test the potential improvements you just made.

Why Would I Do This?

I have seen this change both improve performance and stability of the Business Objects XI WebI client machine. Truthfully, many support engineers recommend this change anytime a user reports that WebI is hanging or sluggish. I recommend increasing these settings. Now, I wonder if by increasing them I can reliably run to Java Report Panel sessions at the same time. Hmm… I wonder.

Newly Released Guide: Web Intelligence Quick Reference

It has been exactly one month since I published my last Business Objects XI article. I sincerely apologize for the delay, but I promise that I did not take a holiday. I spent the past month working on enabling a new “Subscribe to Comments” feature and lately on pulling together a brand new guide which I hope you will find useful and valuable. Yes, now we can honestly state that we offer guides, that is to say, instead of offering solely our popular Query Builder guide.

We have compiled our NEW Business Objects XI Web Intelligence Quick Reference Guide from the personal feedback and cheat-sheets of seasoned Web Intelligence developers. The result is a concentrated collection of the most popular and valuable functions, WebI operators, syntax details, tips, and more, pulled together with the goal to increase WebI developer efficiency and precision and to make reports more professional and complete. We want to enable Web Intelligence reports and their developers to realize their full potential… please read more about the guide and order one here

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.

1. 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!
2. 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.
3. 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!

4. 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.
5. 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.
6. 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.
7. 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.

Sizing Limits to Web Intelligence Report Server Maximum Simultaneous Connections

In Business Objects XI R2 newly created Web Intelligence Report Servers default to 50 “Maximum Simultaneous Connections” (editable in the Central Management Console, or CMC). In Business Objects XI 3.X the “Maximum Simultaneous Connections” setting is defaulted to 100! Can we read anything in to these changes in default settings? Do the default settings mean anything at all with regards to the settings you should have on your system?

Sizing Limits to Maximum Simultaneous Connections

General consensus of our sources tell us that XI 3.X is better coded and can handle more connections than XIR2, with regards to WebI Report Servers. Nevertheless, it would be quite a leap to say that all other things as equal as possible that XI 3.X WebI Report Servers can handle twice as much traffic. Seriously, don’t count on it.

I feel I should add personal experience and best practice here. I see the default setting for “Maximum Simultaneous Connections” on the WebI Report Server as a maximum setting. I have personally never exceeded it nor witness it exceeded by anyone. It is generally held that if you need more Simultaneous Connections then you ought to add another WebI Report Server to your environment. Of course, keep in mind that SAP Business Objects’ general guideline is that you have no more than 1 Web Intelligence Report Server per available CPU core (for example, a server with 4 quad-core CPUs has 16 CPU cores); so there is a limit there too.

Real-World Web Intelligence Report Server Sizing

A production system will generally run better having more Web Intelligence Report Servers with lower Maximum Simultaneous Connections. For example, a server with 4 dual-core CPUs would run better having 8 WebI Rpt Servers each set at 30 Maximum Simultaneous Connections than it would having 4 WebI Rpt Servers each set at 60 Maximum Simultaneous Connections in CMC. In the real-world tuning and balancing usually are based on observed performance within configuration guidelines.

To get your sizing in the right place you should know what you maximum concurrent users are (logged-in users plus concurrent schedule jobs). This number is essentially how many Maximum Simultaneous Connections you will need. So let’s say you never have more than 100 concurrent users, but you can at those peak times also have 25 scheduled jobs. Keep in mind that most users will only do one process at a time, but some like me will be refreshing one report while editing another simultaneously. A scheduled job will always only be one connection. So you can safely say that you require only 150 simultaneous connections (with some wiggle room).

Now your server had only 4 CPU cores in it. So this one is easy 150 divided by 4 will give you 37.5. Round that up, because I suggest you have at least 150 and that you keep the same number on each WebI Report Server. So you can set each one’s “Maximum Simultaneous Connections” to 38 using the CMC. In XIR2 this might be pushing the limits, but in Business Objects XI 3.X this should be a comfortable setting.

@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

Clearing the Browser and Java Cache: Do This First!

There are certain rules, tried and true practices, that you always try first before you call someone. In IT the first one is restarting an application and the second one is rebooting the computer. After those two, the order becomes debatable. Unfortunately, many of us know folks that call us before even trying one and two.

The Third One

I present to the world of Business Objects and in fact, the world of most web based applications, a third practice that you will always want to try before calling someone (or have your users try before they call you): clearing the browser cache and the java cache.

How do I clear the browser cache?

In Mozilla FireFox follow these instructions:
In the drop-down top menu navigate as follows:
Tools > Options > Privacy (tab) > Clear Now (button)
or just
Tools > Clear Private Data
Make certain that "Cache" and "Authenticated Sessions" are selected, the other options are up to you if you want to include them. You may want to add "Offline Website Data" too.

Clear the Cache for Mozilla FireFox

Clear the Cache for Internet Explorer

Sometimes You Need to Dump the Java Cache Too

There are times when the Java Query Panel won’t load or it misbehaves on the client. Instead of wasting time debugging or wishing you had “Full Client” back, just dump the Java Cache. Here is how to do it in my current Java version:
Open the Java Control Panel by looking in your PC's control panel or by double clicking the Java icon in your system tray.
On the "General" tab click the "Settings" button. Click "Delete Files". You could also click the "View" button and then hand pick the BO related applications, resources, or deleted applications that you want to dump. Be aware that the next time you access java web components, such as the java query panel, expect the initiation to take some time.

Clear the Java Cache

Errors that Indicate Issues Resolved by Clearing the Cache

My favorite error is “Internal Error: the value of parameter lang is invalid”. It is perhaps one of the least helpful in a sea of un-descriptive errors. This error is a client-side error and can be quickly resolved with just a clearing of the browser cache in many cases.

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.

1. 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.
2. 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!

What is an Unbound Report and Why Should I Care?

You may have read something on this web site or in one of our guides that mentioned “unbound reports” in a less than favorable light. Hopefully this article explains what we mean by this.

What is an unbound report?

With the migration to the Business Objects XI platform many things changed. One of these changes was the method used to connect (or bind) documents to universes. I wrote an article on this binding and relationship, it is called “Business Objects XI – Changing BO Report / Universe Relationships“. So I won’t revisit that discussion beyond saying that in BOXI reports and universes are primarily and securely bound to each other through a set of unique identifiers of the objects: CUID and Object ID. If a report loses this relationship to its universe we call it and “unbound report”.
There may be other names for this phenomenon, but I believe this is the one the experts in SAP Business Objects use, and if they don’t I like it and I think it fits very well.

What causes a report to become unbound from its universe?

The basic event that occurs causing a report to become unbound from its universe is that the unique identifier of the universe is removed from the system. Some of the actions that remove this unique identifier may seem rather innocuous, but they can all be potentially deadly to your reports:

1. Deleting a Universe: OK, this one is not innocent, but it is the most obvious and it actually has a few variants
2. Replacing a Universe Using Designer: Many people use Designer to migrate universes from a development environment to a production environment. If during export you are prompted to “overwrite” a universe and you accept… kiss your report binding good-bye. If you quickly delete the original universe and then upload the new one, same story.
3. Improper Use of Import Wizard: The “Merge” option causes so much trouble because it is not understood properly by many. If you use this or even if you use the much preferred “Update” option and you see that a new universe named with a “(2)” suffix was created, you haven’t ruined your report binding yet, but you next actions might. You must manually bind each report to the new universe with the “(2)” using the Java Report Panel to transfer the binding and make it safe to remove the old version of the universe. Failure to do this will fill your day with regrets.
The truth is that there are many permutations of actions that can lead to this. Many of them start with improperly creating backups of the universe (using Save as) and generating new unique identifiers for the modified universe. Others start with bad migration or promotion techniques.

But It Worked Fine in Business Objects 6.5

You are correct, but that doesn’t change anything. Remember, the game changed with Business Objects XI. The fusion with Crystal Reports created all new rules that Business Objects has barely explained to its customers. In BO65 the report-universe relationship was name based. Thinking this is still the case is the biggest issue facing Business Objects XI administrators and developers. Now it is all about unique identifiers and you can only maintain them by using the properly workflows.

I Followed a “Bad” Workflow but My Report Still Works, Why?

Many times unbound reports will continue to perform just fine, for a while. The first question is how long with that while be. The second question is, are you sure the report is using the right universe??? The CMS record of the report has meta data that lists a universe short name which many times will allow a report to find a universe to use. The problem with this is that it is unreliable and universe short names can change and be duplicated. In such cases a well-behaved unbound report might start to use a universe other than the one you intend or it might just fail. If it uses an unintended universe this could be a HUGE problem as it may not raise any errors to the end-user and the end-user may be making business decisions on wrong, bogus, or out-dated information!!!

The Nearly Unrecoverable Error – Error: WIS 00501

If your report cannot locate a universe, even with using the hidden short name stored in the report’s meta data then this is the error you will see:

Refreshing Data
Universe not found. See your Business
Objects Administrator. (Error: WIS 00501)
(Error: INF )


Once you see this error, you should expect the worst. In some rare and mostly undocumented cases some lucky BO developers have been able to recover from this issue by replacing the original universe. However, please don’t count on this; it is only worth an attempt.

So What are the Proper Development and Object Promotion Workflows?

I have hinted at a few and skimmed over others. To be honest there is a lot to discuss on this topic that exceeds the scope of a simple article. I am working on a new guide that details these workflows and how to properly design or retrofit a Business Objects XI system to best avoid these pitfalls and provide the maximum stability. As the guide materializes I will post some of the content here. I also hope to receive comments that will help shape part of the guide as well.

What do the values of AUDIT_EVENT.ERROR_CODE mean?

If you have spent some time with your Business Objects XI auditing data then you have probably asked yourself what significance is of the the error code values for the AUDIT_EVENT table’s ERROR_CODE property. If you haven’t yet pondered this then I suspect you haven’t looked closely at your data, OR you don’t have enough data to have produced a suspicious error code.

What are those weird values for AUDIT_EVENT.ERROR_CODE?

Here are some of the values that I have seen over the years in my BO XI R2 auditing database. The list below is a distinct list of all error codes with their corresponding EVENT_TYPE_DESCRIPTION:

ERROR_CODE | EVENT_TYPE_DESCRIPTION
1 | List of values
5 | Get page
86 | Get page
87 | Get page
303985 | Generate SQL
2147500037 | Apply format
2147500037 | Document refresh
2147500037 | Get page
2147500037 | List of values
2147500037 | Select prompt
2147760471 | List of values
2147760472 | Document refresh
2147760472 | List of values
2147760642 | Document refresh
2147760642 | List of values
2147760644 | Document refresh
2147760644 | List of values
2147760646 | Document refresh
2147760646 | List of values
2147760675 | List of values
2147760677 | List of values
2147775010 | List of values
2147776034 | List of values
2147776072 | List of values
2147776088 | List of values
2147776301 | Get page
2147776326 | Get page
2147776341 | Apply format
2147777058 | Get page

I must state that this list is in no way comprehensive, but I think it is a good sample. It might have helped the search engine help you to reach this page. Anyway, the point here is that it doesn’t matter if we build a comprehensive list because it is near impossible/pointless to build an error code translation for each ERROR_CODE value.

What do all of those ERROR_CODE values mean?

Some of you may have that handy-dandy Business Objects error code cross-reference document (I should see about sharing it next time I find it) and you are thinking these error codes can be found there. Sorry, nice try, wrong, thanks for playing. Failing that look-up I asked around and this is what I found out:
Very Short Answer: nothing
Short Answer: they can only be used in deep debugging analysis
Long Answer: Those error codes are internal Web Intelligence error codes which depend on the workflow followed and on the specific API which failed. The error codes should be able to be referenced in the trace logs produced through active “-trace” logging on the corresponding WebI Report Server (more on this topic in our article “Business Objects Classic Logging and Standard Tracing“).

So can I find any meaning in the ERROR_CODE values?

In my experience the number of errors perceived by the users of the system exceed the number of non-zero/non-null ERROR_CODE values stored in the Business Objects XI auditing data. Truthfully, some of this makes sense. If a BOXI user gets an error immediately when they click on a report in InfoView then the CMS may never get a chance to capture the error to be able to write an auditing record. More truthfully, I don’t really know.

All I can say is that the best use of AUDIT_EVENT.ERROR_CODE values is to count them as Boolean values. Did an error occur, or did no error occur. Moreover, if you find that the ratio of errors to non-error events seems to disagree with other sources of error tracking information (or user perception) then the data still has value. It has relative value. You can track it to measure trends. If you see the ratio of errors to non-error events increase/decrease over time then you know something, and knowing is half the battle.

Mystery Solved?

My sources tell me that this is fixed in BO XI R3 SP1 (XI 3.1). In fact, if you want to dive deeper take a look at ADAPT01092225. The “fix” is to limit all AUDIT_EVENT.ERROR_CODE values to 0 or 1. So, if it were previously possible tie auditing data to Web Intelligence Report Server trace logging through the error code value, that ability is revoked in R3 SP1. Therefore, the only purpose remaining for this property is the relative trend value I detailed above. Good luck.

Using BO Query Builder to Detect Report-Universe Binding Status

The loss of binding between reports and universes is a common problem experienced by BO XI users. Often the issue can go unnoticed for quite a while, but like a dormant disease it can spontaneously begin to demonstrate severe symptoms that can result in the loss of your report. Sound serious enough? It is! The following article is intended to help you detect report-universe unbinding proactively, before they cause you serious trouble.

The best tool for detecting of Report-Universe Binding Status is Query Builder. As far as I know there is no third part tool or BO utility for this other than Query Builder, but honestly, Business Objects’ Query Builder works quite well and it is available to you for free. Here and the queries that you will need:

Business Objects Query Builder Query: Universe Binding Status

This query brings back a limited set of properties for the desired universe. I lifted it from our Query Builder Guide. Like most problems there are multiple ways to attack and starting by looking to the universe is one way.

SELECT
si_id,
si_name,
si_webi,
si_cuid
FROM
CI_AppObjects
WHERE
( si_name = '' OR si_id = ) AND
si_kind = 'Universe'

You need to put in the universe name (upper or lower case is not important) or the Object ID of the universe (most people are more familiar with name, but object ID (si_id) provides more precise results. From this query we will see only the reports to which this universe is bound. It won’t list the reports to which it should be bound. The report name nor the report’s query name are given here, but the reports si_id or object ID is given here. And so you my need to do some additional queries to identify the reports listed here. Which leads us to the other way to start looking at this issue.

Business Objects Query Builder Query: Report Binding Status

For many people this is the query they will lead with in QueryBuilder. Usu

SELECT
si_id,
si_name,
si_universe,
si_cuid
FROM
CI_InfoObjects
WHERE
( si_name = '' OR si_id = OR si_parentid = ) AND
si_kind = 'WebI' AND
si_instance = 0

Using the query will require you to provide at least one of the following: report name (si_name), report object ID (si_id), or parent folder object ID (si_parentid). This query’s result will list all of the universes to which the report(s) is/are bound. How can a report be bound to more than one universe? Multiple queries in the report (also called the classic name of “data providers”). This fact makes this query the most important query in my opinion. If it fails to list an expected universe then you have identified and unbound report. My sympathies and congratulations!

What does an Unbound Report Look Like in Query Builder?

Words are often not as valuable as pictures. Now pictures of words, well their value is questionable, but not in this matter. The following image show what the report Query Builder query looks like when a report is showing that it is bound to its universe (just a single universe in this example):

Query Builder output of a report properly bound to its universe

Query Builder output for a report not bound to ANY universe

A Note About the Query Builder “si_universe” Property

Many properties in Query Builder are compound in nature, meaning they have multiple values for a single object. For example and report can have multiple universe and in fact its si_universe property has multiple sub-properties (si_total and the universes’ object IDs). We call properties like si_universe property bags. The unfortunate fact here is that in Query Builder you cannot filter on a property bag. Therefore, you cannot create a query that only returns all of the unbound reports.

What about the CUIDs?

If you have read my other articles such as “Business Objects XI – Changing BO Report / Universe Relationships” then you know that the relationship between reports and universes is really at the CUID level and not the Object ID level. Well, the truth is that Object IDs are specific to an environment, but CUIDs are portable between environments (with the write methods). Anyway, Query Builder and the CMS InfoStore will show the binding at the Object ID level, but this binding will look different in another environment to which you have correctly and successfully migrated the report and universe because the Object IDs will be different (but the CUIDs will be the same). Anyway, for now, just know that the CUID is very important with regards to report-universe binding; however, when detecting the status of that binding they are not important.

Important Note about Business Objects’ Import Wizard

I said earlier that there was no other tool for identifying unbound reports, but I fibbed a bit. It is possible to use Business Object’s Import Wizard to detect unbound reports. If you select a report and the option to automatically select its universes then if Import Wizard fails to select all of the expected universes you know that the report is in an unbound state. This is a tedious way to detect report-universe unbinding, BUT if you find yourself using Import Wizard and you are experiencing an issue with Import Wizard selecting reports’ universes then you have a good indicator that something is probably wrong with the report-universe binding.

Knowing is Half the Battle

The next logical question is, “Now that I have identified an unbound report, how can I correct it?”. That my friends is a topic for another article. My family and hobbies are calling to me now; please remind me if I forget to write that article soon.

Want to Know More About Query Builder?

I recommend that you take a look at our “Business Objects Query Builder Guide“, it is most likely “The Most Complete Business Objects XI Query Builder Guide Ever Written” and it will help you to discover and master the secrets of Business Object’s Query Builder, such as the one discussed in this article.

OpenDocument – An Introduction to a Powerful Tool

If you are not yet using the OpenDocument function in your reports you might want learn a bit more about it and see if it could not meet some of your current business requirements, or better yet allow you to amaze your business partners by giving them something they didn’t yet know that they needed!

What is OpenDocument

OpenDocument is simply the ability to open a “document” (a.k.a. report, usually) directly; without having to locate the report and even without having to submit any parameters to the report (optional). A properly constructed OpenDocument URL can be placed on an page that can serve up a URL/link and then users can click on it and be taken directly to the report that the URL specifies.

Major Capabilities of OpenDocument

The OpenDocument function provides a few excellent capabilities:

• Abstraction: users don’t even need to knwo the report name. They could be given a URL on a web page, email, Word Document, etc. which says “Click Me to Access Your Important Report“
• Simplicity/Efficiency: users are spared navigating to a report and selecting predictable parameters each time (static or dynamic, your web coding skills are your only limits)
• Linking Reports Together: create summary and detail reports that allow for in-depth drilling on specific data or create other interesting and useful relationships between reports

Additional Information about Business Objects’ Open Document Function

BO XI introduced OpenDocument, replacing the “viewrpt.cwr” command in previous versions of the Business Objects Enterprise. OpenDoc accepts many optional parameters allowing various customizations, such as display format, parameter values, and refresh flags.

A Simple Example of an OpenDocument URL

The following is an example of a very simple OpenDocument URL using a .NET web server:

http://myserver/businessobjects/enterprise115/infoview/scripts/
opendocument.aspx?sDoc=My%20Favorite%20Report

Cautionary Note #1: Please note that OpenDocument calls use URLs and therefore they are bound to the special character limitations of all URLs. That is to say that spaces and certain other characters are not permitted. Spaces can be replaced by “%20“; however, Business Objects Web Intelligence also has a native function called “URLEncode()” that can resolve any necessary special character formatting.

Cautionary Note #2: In Business Objects XIR2 it is entirely possible to have two documents with the same name and therefore this simple example should be received in this context. The OpenDocument function supports many parameters that will allow you to pin-point your desired document and in our next article on this topic we will show you how to do this gracefully and With maximum portability across any environment.

Use Web Intelligence (WebI) Java Report Panel Structure View

Many new BO WebI users, especially former “full-client” users, complain about how slow and inefficient it is to develop reports on WebI. Most of the time when I hear these complaints I come to discover that the complainer is missing some basic knowledge about WebI report editor.

First the Basics of BO WebI Client Server Relationship

Business Objects XI WebI is a web application that depends on the web server and CMS repository for quite a few things. This is not a stateless web application. Initially the server provides the editor, GUI, parser… to the client. Once the editor application (a.k.a. Web Intelligence Java Report Panel) loads locally the server maintains a constant communication channel with the editor. The WebI Java Report Panel starts by default in the “Results Viewer” mode.

What is the “Results Viewer” / “View Results” mode?

The results viewer mode of WebI is the complete WYSIWYG (What You See Is What You Get) mode of Web Intelligence. It present the report almost exactly as it will appear to end-users (there are a few differences). Complete communication with the BO server is required in order to generate the WYSIWYG view as it applies all formatting, filtering, aggregation, and calculating on the report’s variables and the query’s results. Some may argue that this should not require server communication beyond the first iteration; however it certainly does in the present “online” WebI versions (“off-line WebI” should change this drastically). It seems that the standard “online WebI” is in many ways a “light client” application. All of this means that EVERY change made to the report while in “View Results” or “Results Viewer” mode will require communication to and from the server and this will make editing the report VERY slow.

WebI Java Report Panel’s Structure View to the Rescue

What is surprising to many is that certain mundane actions that normally would not even register with the server do require server processing when executed in the default “Results View”, but not in the “Structure View”! To switch to the “Structure View” click on the button labeled “View Structure”. This will switch you to a template of the structure of the report you are building. Now changing fonts, cell shading, column order, header titles, cell contents and much much more become instantaneous. Most of the changes you make to the report are cached on the local client; this explains the new found speed.

Additional Advantages of the Structure View

The structure view also allows you to view and easily manipulate certain elements of the report better than the “results view”. For example, table footers can be quickly accessed and section within the report are represented with gray lines that mark their beginning and end. A cell’s object(s) or variable(s) can also be immediately visually identified in the structure view as well.

A Disadvantage of the Structure View

All of the speed of the Java Report Panel Structure View is because server communication is reduced to the minimum. This minimal level of communication means that most report formatting actions and several common report editing actions are not communicated to the server at all. Big deal? Yes, this means that the server is not being reminded that you are working hard on your report and it is certainly not saving those wonderful changes you are diligently working on. In other words, when working in the “structure view” save often!

BO InfoView WebI Session Timeout = Developer’s Worst Enemy

Business Objects has been encouraging report writers/developers to stop using the “full-client” and start using WebI. There are quite a few obstacles to overcome to be able to make the switch to WebI (training, functionality adjustment, server capacity planning, etc.), but perhaps the most difficult adjustment is learning to development under a timed session.

For many reasons Business Objects administrators configure their systems to timeout a web session and an InfoView session after a certain period of inactivity. With finite resources, which we all definitely have, this is always a good idea. However, this limiting concept has two problems. The first issue is that “full-client”, ZABO, or Desktop Intelligence developers are used to working in a virtually limitless development environment: their own desktop PC. The second problem is that system administrators need to find just the right balance between flexible the valid and acceptable dormant session limits and the obvious session abandonment.

Business Objects Administrator Settings

When Business Objects system admins find that magical median, which will be different for every user community, they need to set it at a few different places:

1. The web server “connection timeout” setting should be increased first. This one is usually stored in seconds, not minutes.
2. The InfoView application within the web server should also have its “Session Timeout” value increased.
3. The web.config file also needs an update to the “” section, setting “” (using the number of seconds in place of the “#” character.

BusinessObjects Report Developer Adjustments

Report developers or writers, including ad-hoc report writers, will need to adjust their methods and habits. Initially, business objects report developers will assume that as long as they save before any periods of inactivity they are safe. However, they will face the harsh facts of this assumption if they are working on the query the whole time.

In BO XIR2 editing the query of a Web Intelligence report does not reset the session timeout timer. Therefore it is entirely possible for a developer to be actively developing a report’s query and find that their session is lost, along with all of their work. In fact, in the WebI java editor panel there are many actions that are registered only on the client and they are not communicated to the server until a logging action takes place such as saving a document. Among these actions you may find general formatting, query editing, and even adding fields to a report.

The safest bet is to require all of your report developers to develop a habit of saving their report every 5 minutes. This will certainly reset the timeout timer and it will ensure that no report development work is lost. From my own personal experience I would say that I have probably lost about 10 to 20 hours of work because of lost session.

Enhancement Request for Business Objects XI R2

To be honest, I would like to see BO add some functionality to the report editor and even InfoView that would help avoid this issue. They could implement an auto-save function. Or perhaps a pop-up box prompt which warns the report developer that they are about to lose their session. Either one would be a wonderful improvement and would probably save BO users thousands of lost hours of work. Until then save every 5 minutes!

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.

1. 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.
2. 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.
3. 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.