Business Objects tips, tricks, articles, blog, guides…

Comments

Posts

Designer

What is a Universe Overload?

What a cool and confusing name!

I have to admit that the name “Universe Overload” tends to send my imagination going to exciting science-fiction worlds and different dimensions. I think the person that coined the phrase must have chosen it for that reason, because for me it does not seem to represent. To calm my imagination and to help the phrase prompt more appropriate understanding I tend to replace the word “Overload” with “Override”. In general, “Universe Overloads” override the default restrictions placed on the universe objects.

Then what are “Access Restrictions”?

Prior to BO XI 3, “Universe Overrides” were called “Universe Overrides”. In Business Objects XI 3.1 “Universe Overrides” we renamed (like so many other things) “Access Restrictions”. The incremental boringness of this new name is off-set by an equal increase in the descriptiveness of the name. “Universe Overrides” or “Access Restrictions” are simply object restrictions, table mapping, and row restrictions which are directly added to a universe.

How can I add or modify a Universe Overload?

You can modify/view these “Access Restrictions” by opening the universe in Designer. Then select Tools > Manage Security… > Manage Access Restrictions. Nevertheless, if you did not know that already then you will need more than this brief article to get you safely going into the exciting and futuristic world of “Universe Overloads”.

How to Backup a BO Universe and Maintain its CUID

Backing Up and Restoring Universes While Maintaining CUIDs

Import Wizard BIAR File – The ultimate backup

The sections that follow this one will tell you how to create a backup of a universe on your machine from which you are running Designer. The steps in those sections are “tricks” that allow you to maintain the Business Objects XI ‘s universe’s CUID. With practice they can be executed quickly and on the fly to permit the Universe Designer/Developer to create a back-up more quickly than if they were to use Business Object XI’s Import Wizard’s BIAR file backup method.

With regards to maintenance of the Universe’s CUID and general backup/snapshot capabilities Import Wizard’s BIAR file backup has proven to be quite reliable. This is not to say that I have never seen issues, but these have been limited to large BIAR files and large numbers of objects. Creating a BIAR file backup of a single universe or just a few should 99% of the time be reliable. The drawback is that creating such a backup on the fly can be a rather significant workflow disruptor, you must chose just the right settings in Import Wizard, and because you must switch to Designer, you can only backup what has been loaded to the CMS. All of these attributes might steer you to look for another on the fly backup technique, if so, keep reading…

Make Back-up of a universe

Using Business Objects Designer import the universe. Then making no changes, close the universe. This will create a fresh, unaltered copy of the universe on your local computer.
Locate the universe file (*.unv) and the folder (has the same name as the universe file) in the following folder path on the computer on which you are running Designer:
C:Documents and SettingsApplication DataBusiness ObjectsBusiness Objects 11.5Universes@DevelopmentWorking Area
Select both the universe file and the folder (hold CTRL and use mouse). Then right-click either the selected file and chose “WinZip -> Add to Zip File…” Note: Any compression tool will do.
Enter a name that is meaningful to you; it may indicate the version of the universe, the date/time, or editor. Click “OK” and notice the new Zip file you created. This is your backup.

Restore a back-up of a universe

As has been stated many times on this website, you must be very careful with Universes and their CUIDs. Many logical work flows for handling universes can result in changing CUIDs and lost report bindings. Nevertheless, if properly back-up a universe can be restored to a prior state by following this work flow.

Open Designer, but do not open any universe
Locate the desired corresponding Universe folder and zip file. They should be located in:
C:Documents and SettingsApplication DataBusiness ObjectsBusiness Objects 11.5Universes@DevelopmentWorking Area
Back-up the existing universe file and folder to a new Zip file
Delete the existing universe file and folder
Unzip the backed-up universe file and folder to this same location. Note: They must be in the same local folder as the universe file and folder they are replacing.
Open the universe file that you just unzipped and export it to the folder in which you are working:
You will receive a prompt similar to the following. Click “Yes”.

Prompt reads, “A newer version of this universe exists in the repository. If you continue with the export you may overwrite existing changes. Do you want to continue?

WARNING: if you receive any messages asking you to Move, Copy, or Overwrite a universe then you may not be restoring to the exact location that the universe previously resided. Prompts asking to overwrite the universe will be received if the universe you are attempting to restore does not have the same CUID as the backup universe. In this case, double-check your directories and analyze everything in Query Builder.
At the end of the export close the universe and import the universe you just exported.
Verify that this is the correct universe.
You have successfully restored the universe

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

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:

Deleting a Universe: OK, this one is not innocent, but it is the most obvious and it actually has a few variants
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.
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.

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.

Can I Connect/Use Oracle 11g Database with Business Objects XI?

The answer is “Yes”, but it depends on which version of BOXI you are running.

Business Objects Enterprise XI Release 2

Any version of BOXI R2 equal to or greater than Business Objects XI R2 Service Pack 5 includes an Oracle 11g driver. However, if you find your instance is still lurking below SP5 you are not without hope. According to Oracle, 10g drivers can successfully be used to connect to Oracle 11g databases. Business Objects does not support using R2 SP4, or lower, to connect to and Oracle 11g database, but that doesn’t mean that you can’t do it. It only means they won’t take any “Customer Messages” on any issues related to such connections, unless the issues can be recreated on database supported for your BO XI service pack level.

Be sure to check your service pack’s “Support Platforms” for the particular supported implementation of Oracle 11g middleware. For example, XI 3.1 SP2 states clearly for Linux/Unix machines running CMS you must install Oracle 10.2 middleware to connect to your Oracle 11.1 database.

Business Objects Enterprise XI 3.X

Starting with Business Objects XI Release 3.1 Business Objects has included an Oracle 11g driver. Now if you are on XI 3.0 and you can’t jump to 3.1 just yet, you might want to borrow a trick from Administrator’s of XIR2 systems lower than XI R2 SP5 and use an Oracle 10g driver to connect to that cutting edge 11g database. Just don’t expect BO to support you on that venture.

Selective Operators: Allowing Users to Decide Which Operator to Use

Everyone once in a while you come across something that makes you say, “that is great, now why didn’t I think of that?”. Recently I came across such a thing: an idea of how to make operators in the WHERE clause of a query definable by the user at the moment of report refresh through creative use of SQL and Designer. Honestly I think everyone could benefit from a greater understanding of SQL. This like piece of code is the proof of that:

Selective Operators in Business Objects XI Web Intelligence or Desktop Intelligence at Run-Time

( 'Lesser than or Equal' = @Prompt('Select Operator:','A',{'Lesser than or Equal','Equal','Greater than or Equal'}, MONO,CONSTRAINED) AND Emp.salary <= @Prompt('Enter Salary:','N',,MONO,FREE) ) OR ( 'Equal' = @Variable('Select Operator:') AND Emp.salary = @Variable('Enter Salary:') ) OR ( 'Greater than or Equal' = @Variable('Select Operator:') AND Emp.salary >= @Variable('Enter Salary:') )

The value in this case does not come from fancy Business Object prompt syntax, but rather from creative combinations of SQL and prompts. Note: Please remember this is just an example that you should use to learn the technique and then adapt it to your database’s SQL and to your business requirements.

How it Works

Although this example code has three clauses they can all be bound to each other through their placement in a single universe-level condition, or you could place them in multiple conditions for mixing and matching at the report level. The power of this logic is that it uses one dedicated prompt to collect the desired operator from the user: ‘Lesser than or Equal’,'Equal’, or ‘Greater than or Equal’. Then using hard-coded string values it creates a scenario where only one of the three statements can be true.

And Example with SQL Substitution

In each statement the hard-coded operator text is matched with the actual operator following “Emp.salary”. Therefore, if the user selects “Equal” for the @Prompt(‘Select Operator:’) and “1000″ for the @Prompt(‘Enter Salary:’) then the following substitutions will be made for the actual SQL run by Business Objects:
( 'Lesser than or Equal' = 'Equal' AND Emp.salary <= 1000 ) OR ( 'Equal' = 'Equal' AND Emp.salary = 1000 ) OR ( 'Greater than or Equal' = 'Equal' AND Emp.salary >= 1000 )
Because of the magic of SQL and logic only the clause ( 'Equal' = 'Equal' AND Emp.salary = 1000 ) can execute; the others are ignored because the first part of the SQL evaluates to FALSE. In the rules of logic a statement of “FALSE and TRUE” is always FALSE. In this case we don’t know if the second part could evaluate to true, because we don’t know the underlying data, but regardless the entire clause is FALSE when one of the components is FALSE and it is linked to the others using “AND”.

A Few of the Other Details

OK, I feel I got a little technical on the logic, but I think the examples speak clearly enough just in case I lost someone. Again, the whole point that I am making with this code is that it allows users at run time (a.k.a. report refresh) to select the operator for they want to use. If a user wants they can also use the Java Query Panel and change the operators all they want, but they would need access and training to accomplish this.

Some notes: In order to keep it simple I only used the @Prompt statement once and I use @Variable for all of the other instances of the prompt’s use. This helps to avoid problems if I accidentally set the @Prompt differently and it makes it easy to modify Prompt syntax later. The parenthesis around each clause is necessary. Please let us know what you think and share your modifications or spin-offs or alternate uses if you have any.

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

Extended @Prompt Syntax: Default Value, Purge Values

During my report development efforts the other day I knew that I needed a business objects universe-level prompt, but I also knew that it was mandatory to “purge” the prompt values. Like a good little Business Objects developer I always purge all of the data from my reports and reports’ prompts before I publish them for User Testing and promotion to production; however, in Business Objects XI R2 I have found that WebI reports have a strangle-hold on prompt values and they don’t want to give them up without a fight.

So I started looking around to see if I was just missing something obvious. What I found on my search was some completely unsupported extended @prompt syntax. The funny thing was that I did not have to install anything to enable it. The syntax worked immediately, just by passing in the additional parameters. Note: this is unsupported officially by BO, but BusinessObjects obviously developed it for some larger client and slipped it into the XI R2 code. Therefore, there is no promise that this will be supported in the future, but there is hope.

Anyway, on to the syntax:

@Prompt(’1) Enter Product Family’,'A’,'Product Hierarchy\Product Family’,multi,free,[Not_Persistent],[<{default_value(s)_or_leave_blank}>],[User:#])

With this syntax you can now add 3 additional/optional parameters after you universe-level prompt’s “free/constrained” option.

Prompt Persistence: Default value is “Persistent”; however, the value “Not_Persistent” is the reason most Business Objects WebI report writers want to use this syntax. Using “Not_Persistent” removes the last prompt used with the report; it purges the value. This can be very important in many settings because it can be the difference between an end-user accidentally running the report for some unwanted parameter value.
Default Value(s): If left blank no default value used. If a value or values is desired then the set should be surrounded by { } characters and separated by commas (much like hard-coded LOV values earlier int he @Prompt parameters). This feature might seem to counter the “Not_Persistent” setting; however, it may be possible that you always want your Web Intelligence report prompt to include the default “*” value even while you are testing or running it for other values. Therefore, some might find that using this option with the “Not_Persistent” option provides value and extended functionality to their WebI reports.
User:#: This particular value has been ignored by many, but it also provides some interesting value. It allows the universe business objects developer to define the relative order of the prompt. Yes, no more not-so-tricky “1.”, “1)”, or “1-” prefixes. In fact, I believe this option provides better value because it defines order of universe-level prompts and it two prompts have the same “User:#” value then they are sorted alphabetically.

The following are some examples of potentially usage of this extended @Prompt Syntax:
@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,{'Kiwi','Guava'},User:0) @Prompt('Enter Product Family','A','Product Hierarchy\Product Family',MULTI,FREE,Not_Persistent,{'*'},User:9) @Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,,User:1

I have found this syntax to be invaluable in my BusinessObjects Designer and Web Intelligence report development efforts. As good little programmers we know that universe-level prompts are a “best practice”; however, without this syntax they can retain last-run prompt values and cause confusion and even misleading and blatantly wrong business intelligence. This syntax finally brings universe-level prompts into the 21 century and makes our jobs all a little easier and maybe even more secure.

Tip: Including “All” or “*” In Your LOV

Try hard coding “All” or “*” in the object’s LOV, in the universe. You could do this by editing the object’s LOV and adding a query with UNION that will only return the word “All”. You may have to make sure that the LOV for the object is returning a character data type for this to work properly.