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.

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.

Possible Recovery from Business Objects Error WIJ77778 and WIJ20003

Business Objects WebI report development is challenging enough already, session timeouts in BO XI R2 are mostly annoyances to the report developer, often causing the developer to lose work and time (and money of course). I have found a few ways to recover from a couple errors some of the time. These tips have saved me from losing hours of work (cumulative) in the past.

The eternal busy/hourglass in Java Report Panel?

Have you ever been working on some tedious report-level variables or alerters and found that suddenly your WebI Java Report Panel session cannot validate your variables? I mean it just sits there with a seemingly eternal busy or hourglass icon. When this happens to me the first thing I do is to stop the validator by pressing the escape button, “esc”, on my keyboard. This usually returns me to my unvalidated formula. Then I proceed to “Step 2″ (see below).

Your Web Intelligence session has timed out. Log out and log in again to InfoView. (Error: WIJ 77778)

The Variable Editor won’t do anything with my variable!

Or depending on if you are working in the variable editor the screen might just flash teh screen and “ding” at you without doing anything more when you try to validate the formula or save changes to it. If this occurs to you STOP and immediately proceed to “Step 2″.

The Formerly Dreaded WIJ 20003 Error

I used to beat on the things around my desk or my own forehead every time I receive the WIJ 20003 error. In the past, most efforts to overcome this led to me closing my Java Report Panel window and losing me work. Lately, I have not run into one of these errors form which I could not safely recover. Much of the instances of this error that I receive seem to be related to a timer that can easily be reset at the InfoView level with a… wait, proceed to “Step 2″.

Unable to retrieve the first page of the current report. Check the report for errors or contact your Database administrator. The report panel will switch to Structure View. (Error: WIJ 20003)

Have you ever thought your save was successful only to find nothing was saved?

This is one of the worst consequences of a partially timed-out session. This happened about once every few weeks for a while. Now, I think I have been able to avoid it by carefully checking my session by trying to validate a variable or cell contents and then if any issue is detected proceeding straight to “Step 2″.

IMPORTANT NOTE: A knowledgeable SAP-BO engineer once told me that clicking the “Save” button is not the first thing I should do if I suspect that my session is timed out. He suggested trying to validate a variable or the contents of a cell first, or modify the format of the report and display the results even. Then click the “Save” button. From this and some observations it seems that the “Save” button in many version of Business Objects XI Web Intelligence does not reset all session timeouts and can therefore appear to work without any error. I always keep my eye on the lower left-hand corner of my Java Report Panel window and look for the flashing “Saving document” message. I suggest you do too.

Step 2: Resetting Non-Java Report Panel Timeouts

As we have discussed in previous articles there are many many different timeout timers working against the Business Objects report developer. Not all of these timeouts are reset by most or any of the actions taken in the Web Intelligence Java Report Panel. Even regular saving may not spare you from timing out in other ways that have nothing to do with your refined report editing work practices.

Due to these often inevitable timeouts experienced during long report editing sessions, a very good practice is to do the following:

• Always use the “View my documents…” setting of “fullscreen browser window”, it is under preferences. There are actually two options and I believe that I am a power user and as such I 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 then you will not be able to try this trick and you are probably working less efficiently than you would otherwise.
• Go back to the original InfoView browser window. Click on something here. I usually click on the folder that I am already logged in to. This will get all of your other session timers reset.
• Now return to your Java Report Panel session and re-attempt the action that you did not previously complete with any high level of confidence, such as variable validation. After this completes then save your report, or save a version with “Save As”.

Preventing BO WIJ77778 and WIJ20003

We all know by now that an ounce of prevention is worth a pound of cure. This remains true in Business Objects land too. So how can you prevent these errors:

• Follow “Step 2″ as detailed above. Set a timer to remind you to do this occasionally.
• If your connection to the web server is spotty/iffy/dodgy/unreliable (such as through remote network access) then always execute “Step 2″ as quickly as possible following any confirmed or suspected connection interruption once the connection is confirmed to be restored. This will in many cases allow you to return to your Java Report Panel session and save your work.
• Save often and version your saves, use “Save As”.

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:

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

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

In Business Objects how can I get a list of reports using a specific universe?

I have been asked the following related questions a few times in the past months and I thought it would be a good idea to post a public answer to them. First the questions:

• Is there any way to get a list of reports that are using a specific universe?
• If I change this universe which reports will be affected?

The Answer – Yes, Definitely, Using Query Builder

The short answer to these questions is “Yes, you can”. The slightly longer answer is, “Yes, this can be done through Query Builder, the BO Auditing data*, or through the Business Objects SDK.” For the purposes of this article, I will confine my further comments to the Query Builder and BO Auditing data solutions; the BO SDK solution requires a set of skills and even licenses that many BO Developers and Administrators do not possess.

I haven’t been keeping track, but I expect that those who have emailed me this question have not studied The Best Query Builder Guide Ever Written. I just wanted to point out that our Query Builder Guide answers these questions, and provides so much more.

The Resolution using Query Builder

For those of you familiar with SQL or just the concept of tables you might think that all you have to do is join up the record of the specific universe with all of the records of the reports where it is used. Well, this is partially true. Firstly, if you know something about query builder you know that reports and universes are stored in separate logical tables and you know that you can’t really join two tables together. The solution here is two part: (1) query the record for the specific universe retrieving all associated report IDs and then (2) query for for each of those report IDs to get the reports’ identifying information.

Query the Universe’s Reports

This is a straightforward query, but be careful if you do not have the universe’s object ID, because it is possible to have two universes with the same name and you wouldn’t want to get wrong data from the start. Here is the SQL statement you need to put in Query Builder:

SELECT
si_id,
si_name,
si_webi,
si_cuid
FROM
CI_AppObjects
WHERE
si_name = 'UNIVERSE_NAME' AND
si_kind = 'Universe'

From this you will get some additional universe info and a list of all reports that are bound to this universe. Copy that list of report object IDs to a text editor and proceed.

Query the Report’s Identifying Information

Take that list of report object IDs from the previous step and parse the list so that each ID is separated by a comma. Then substitute that list for the string “111111,222222,33333 below in the SQL:

SELECT
si_id,
si_name,
si_universe,
si_cuid
FROM
CI_InfoObjects
WHERE
si_id IN (111111,222222,33333) AND
si_kind = 'WebI' AND
si_instance = 0

The output should be your desired list of reports. Now that you see the “si_universe" property you might ask yourself why I don’t query the report table for my universe’s object ID. Good idea, but Business Objects’ Query Builder query language doesn’t yet support filtering on “property bags”, and si_universe, just like “si_webi” are property bags. They hold multiple values in a single property.

The Resolution using BusinessObjects Auditing Data

If you have enabled auditing on your environment’s report servers then you can look to your Auditing data for an answer to this question. However, auditing data is not meta data; it’s records are created as the objects are used. Therefore if a report is never used then it will not be present in this data set. If this is not a concern, or if you would like to know which reports have been used belong to a certain universe, then querying the auditing data is an option for you.

Querying the Auditing Records

There are many ways to mine this data source for the data you need. For most folks the best, easiest method is to use the “Activity” universe. Here you will find the objects you need. A tutorial on this universe and the auditing data is out of scope here, but I’d like to put together a guide on the topic if I hear of any interest. Another way to mine your data is directly against the database tables (these tables are not encrypted like the Business Objects XI CMS Infostore. This certainly allows for better query tuning, but it will increase the complexity for you. You could even use the SQL of a report created against the “Activity” universe as a starting point, but be careful as that universe is full of derived tables.

Final Thoughts

Both of these steps might seem difficult at first, but with a little practice you will find that you can whip out answers to these kinds of questions rather quickly. In fact, now you have the tools to answer the reverse as well: “How can I get a list of universe using a specific set of reports?”. Now you know, and knowing is half the battle.

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.

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.

How Do I Run a Report Against a Specific Job or Report Server? Easy!

There will come a time when for some unforeseeable reason you will want to run a particular report against a particular report server or job server, or both. When that time comes I hope you know how to make it happen or that you can easily find this article.

Why Would You Want to Run a Report Against a Specific Job or Report Server?

Usually for debugging purposes this topic comes up. For example you have logging configured on a certain WebI Report Server and now you want to force reports to run on it. You could also want to do this because that Report Server has a particular configuration you want to test. Perhaps you want to direct certain reports to certain report and job servers in order to do your own load balancing in scheduling some intense reports. One of these reasons might be part of the reason, but I would love to hear your comments on other good reasons.

How do you force a report to run of a Business Objects report server or job server?

This is rather easy once you know where to configure it and you have some Server Groups set up.

1. Log in to the Central Management Console (CMC): this is where many things start and in this case InfoView will not help you to set any part of this up, you need CMC access.
2. Create a Server Group: Go to the Server Groups section of CMC and create a new SG. After you create the SG you need to assign your targeted servers to it by clicking the “Servers” tab and clicking the “Add/Remove Servers…” button


Assign BO Servers to the New Server Groups

3. Locate the Desired Report:: The easiest way is to use the “Folders” section to navigate to the report in CMC, or use the “Objects” section to search for it. Once located click on the report.

4. Click on the “Process” tab: yeah, do that and then you will see where the power of this often ignored tab comes from. This is where the magic happens. Remember we are still using the Central Management Console.

5. Make Your Selections: You will want to almost always use the “Only use servers belonging to the selected group” option, for obvious reasons. In the corresponding drop-down box, locate the server group that you want to use. Only one server group is permitted here, but a server group can have pretty much any number of servers in it. The top setting is for scheduling only; I suggest you set this even if you don’t plan on scheduling, but your requirements may dictate otherwise. The bottom setting is for both interactive refresh and report editing (such as the JAva Report Panel for WebI Reports).


Set your server groups here

Now to Test it Out!

Ok, now you are cooking with gas! Give it a try. If you get errors, go back and check the Server Group or the assignments to the server group. If the SG is empty you WILL get errors. Sometimes errors tell you that the something is wrong with the server, such as it is not started. If a schedule stays in “Pending” status for very long this may mean that you have not added both a Job Server and a Report Server to the server group. It all depends. Experiment, observe, and learn.

Business Objects XI – Changing BO Report / Universe Relationships

Common Ground – Terminology

Firstly, let me lay down some terminology in order to make this concept easier to discuss. “Classic BO” refers to any version of Business Objects between 5.X and 6.X. “BOXI” or “BO XI” refers to the Business Objects XI Release 1, 2, or 3 (R1/R2/R3), the injection of BO to the Crystal platform.

BO Classic Universe-Report Binding

In Classic BO if you wanted to replace a universe that was deleted from the repository or swap out one version of a universe for another version all that you had to do was place a universe in the repository which had the same name as the universe upon which the report was initially developed. The binding between report and universe was on name. For this reason universe name, for the most part, was the unique identifier for the universe. It made sense and it was a relationship everyone could understand regardless of technical background. When a report was selected from the repository its universe was also found based on the name of the universe listed in the report’s properties.

BO XI Universe-Report Binding

In BOXI, reports are bound to their universes not by the universe name, but by the unique identifier of the universe, the Cluster Unique Identifier (CUID). While this may seem a small change at first glance, one begins to see the full scope when one thinks beyond the simple workflow of universe creation and report creation. For example, what happens when you want to move the universe and report to another BOXI environment? What about if someone deletes the universe and reloads it from a back-up copy on their PC? What if you copy the universe to a different universe folder? Do you know how to answer these questions? Will your answers always result in preservation of the universe’s CUID?

Appearances are Deceiving

At first glance BusinessObjects has brought Full-client, WebI Intelligence, and Universes almost as they were in BO 6.5 (with a few improvements) into the the new Crystal platform with BOXI. The fundamental problem here is the phrase “as they were”. To the end user, it will appear that, despite the InfoView improvements and addition of multiple data providers to WebI, everything else is “as it was”. While familiarity is good, here it also creates the problem. Most end users do not realize that the binding mechanism between report and universe has changed, because for the most part everything else has not, and many training courses do not alert users either to this binding change. Therefore most users will follow development work flows based on the misconception that reports are bound to their universes based on universe name.

How to Maintain Business Objects XI Universe-Report Binding

Getting Started – Creating BOXI Universes and Reports

First of all you do not need to do anything special to create the proper CUID-level binding between the universe and the report. Creating a new report and selecting the universe will create the proper binding. In fact, if you copy a report, that is properly bound to a universe, you will end up with a duplicate report that is properly bound to it’s universe. Like I said before, if this is all that you are doing then you need not think differently about Universe and Report Binding. The problem is that most of us do more with reports and universes then just create them once, save them, and leave them alone.

Making Changes – Proper Universe Editing

Editing a report and a universe requires no special instructions as long as you obey some guidelines. Universes require the most delicate and precise handling. A universe that is imported from the repository, edited, and exported back to the repository will maintain it’s CUID and relationship to its reports. If you create a copy of the universe, using either CMC or Designer, the copy will receive its own CUID and it will not be associated with the original’s reports. Moving a universe between universe folders will maintain the CUID; HOWEVER if a universe of the same name exists in the destination DO NOT overwrite the universe. This will definitely cause the moved universe to receive a new CUID (this is true at least in Business Objects XI Release 2). NEVER USE THE OVERWRITE FUNCTIONALITY OF DESIGNER, terrible, unreliable results are nearly guaranteed. By the way, moving a universe cannot be done in CMC, BO Designer is required.

NOTE: A CUID is a unique identifier. It is impossible for two objects in the world (or same BO XI platform for that matter) to possess the same CUID, unless IMPORT WIZARD (or some other such tool) is used to “clone” the object from one environment to another.

Making Changes – Proper Report Editing

With regards to retention of the universe-report binding, editing a report does not require much delicacy at all, beyond that one would normally employ. However, if your goal is to retain the Business Objects report’s CUID (for reasons of cross-environment synchrony, for example) then there are some rules of engagement. A new CUID will only be created for a report if the report is duplicated, for example: the report is copied, the report is saved using “Save As”, the Import Wizard is used (various options could result in a duplicate with a different CUID).

NOTE: Once you understand the basics you will begin to ask yourself more profound questions such as… How can I tell if a report is not bound to its universe? Many unbound reports continue to work fine, why is that and if this is true why should I worry? How can a revert back to an old version of a universe without losing my universe’s CUID? Why do unbound reports spontaneously go berserk? These are all excellent questions that I will answer if you confirm to me (through your comments) that you are interested in the answers.

Closing Thoughts

In order to maintain CUID parity/synchrony within your Business Objects XI environment and across multiple BOXI environments you will need to learn more than can be covered in the scope of a single article. If this article has piqued your interest or assisted you in understanding the basics, and you would like to learn more then please leave your comments. I could present a couple more focused articles on this topic. Nevertheless, I would also like to know if there is interest in a more comprehensive guide that we could make available for sale at a reasonable price. If so, I would invest more time in this (many hours).

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!

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.