FAQ: Designer

(@Select(Sales Geography\Country Group) IN @Prompt('Select Country Group or
enter * for all groups','a','Sales Geography\Country Group',multi,free) ) OR
('*' in @Prompt('Select Country Group or enter * for all groups','a','Sales
Geography\Country Group',multi,free))

In the sample, the asterisk is used as the wildcard. The first part of the formula…

(@Select(Sales Geography\Country) IN @Prompt('Select Country or
enter * for all countries','a','Sales Geography\Country',multi,free) )

works when a user picks one or more values from the list. The second part…

OR
('*' in @Prompt('Select Country or enter * for all countries','a','Sales
Geography\Country',multi,free))

works when the user selects the asterisk.

If a user selects ‘USA’, the following statement results…

COUNTRY IN ‘USA’ OR ‘USA’ IN ‘*’

If the user selects the asterisk, the following statement results…

COUNTRY IN ‘’ OR '’ IN ‘*’

The IN, combined with ‘multi’ are the keys to allowing the selection of multiple values from the list.

Here is another example which demonstrates how a prompt of this type can accept multiple wildcards and handle case sensitivity…

(PER_JOBS.ATTRIBUTE3 IN @Prompt('Enter Employee Survey Code(s) ("ALL" for
all)','A','Job Information\Add. Job Details Survey Code',multi,free) OR
'ALL' IN @Prompt('Enter Employee Survey Code(s) ("ALL" for all)','A','Job
Information\Add. Job Details Survey Code',multi,free) OR
'All' IN @Prompt('Enter Employee Survey Code(s) ("ALL" for all)','A','Job
Information\Add. Job Details Survey Code',multi,free) OR
'all' IN @Prompt('Enter Employee Survey Code(s) ("ALL" for all)','A','Job
Information\Add. Job Details Survey Code',multi,free) OR
'*' IN @Prompt('Enter Employee Survey Code(s) ("ALL" for all)','A','Job
Information\Add. Job Details Survey Code',multi,free) OR
'%' IN @Prompt('Enter Employee Survey Code(s) ("ALL" for all)','A','Job
Information\Add. Job Details Survey Code',multi,free))

Cindy Clayton (BOB member since 2002-06-11)

system · July 10, 2003, 5:27pm

What is an isolated join and how do I fix it?

An isolated join is one that has not been included in any context in your universe. Once you start using contexts in a universe, every single join must belong to at least one context. A join can belong to more than one context, but if it doesn’t belong to any it is considered by BusinessObjects to be isolated from the rest of the universe structure.

To understand this add a new table to an existing universe that uses contexts. Create a join between that table and an appropriate existing table. Now create a new object based upon your new table. Now create a simple query with one object from your new table and one object from the table you joined to. The result is two bits of synchronised SQL. Why? Because we left our join isolated, as we forgot to add it into an appropriate context. Go back to the universe and add the join to a suitable context. Now try your report again

Nick Daniels (BOB member since 2002-08-15)

system · July 15, 2003, 1:16am

What is the limit on the number of objects I can pick from a list of values and can I increase the limit?

BusinessObjects restricts the number of objects that can be picked from a list of values to ‘99’. It is possible to increase this limit by adding a MAX_INLIST_VALUES value to your database prm file. This parameter is not in any driveren.prm file by default. To use it, you must enter it in the [RDBMS] section under the (GENERAL) subsection. If you do not enter this parameter, the default value is 99.

Your database has a constraint on the number of values that can be present in an inlist condition. Oracle for example, allows thousands of values.

Picking values from a list is different than pasting or typing values into a prompt box. If values are pasted or typed in, the constraint does not apply.

One of our listserv users did some testing with regard to inlist values and had the following to report…

I have thoroughly tested this (“this” being max values when using the “in list” operator in a condition) and here is what I’ve found out:

If you select the values from an LOV, BusObj checks to see if you have set the MAX_INLIST_VALUES parameter value in your .prm file. If you have, it uses this number to limit how many values you can select. This number CAN be greater than 256 if your database supports it. Oracle 8 supports up to 1000 values, so I would assume you could set this value up to 1000 if you use Oracle. (I tried 280 and it worked!).

If you select values from an LOV, and you have NOT set the MAX_INLIST_VALUES parameter, BusObj will limit the number of values to 99.

…and this is what I didn’t see documented anywhere!.. If you type or cut and paste the values instead of selecting them from an LOV, the limit is whatever your database allows. It doesn’t matter if you have the parameter set or not.

Cindy Clayton (BOB member since 2002-06-11)

system · July 15, 2003, 1:28am

How can I calculate the number of hours between two dates?

The best way to do it (and the fastest) is to do it in the database. If you simply subtract the two days, you will get a number in the format D.T, where “D” is the number of days, and “T” is the fraction of days in decimal form. In other words, day 2 and noon minus day 1 at midnight will yield 1.5 as the result.

Take this number and multiply by 24, that gives you the number of hours. Take the decimal portion of that and divide by 60 and you get the number of minutes. So if you have 1.5 (earlier example) and multiply by 24 you get 36 hours and 0 minutes.

Cindy Clayton (BOB member since 2002-06-11)

system · July 15, 2003, 1:32am

How can I eliminate the time portion from my dates and my date lists of values?

Read this till I have time to summarize for the FAQ’s ;-)…

Cindy Clayton (BOB member since 2002-06-11)

system · July 15, 2003, 1:38am

Is there some automated way to populate descriptions for my universe objects?

See here…

Cindy Clayton (BOB member since 2002-06-11)

system · July 16, 2003, 4:21am

How do I create cascading or nested prompts?

Author: Robert Metzker

Let’s create a Series of Cascading Prompts for the Island Resort Marketing Universe. We’ll simply work our way through the Resort class for a quick example. All of the prompts will be fairly similar, and I am going under the assumption that everyone has some Designer Experience.

Let’s start at the Highest Level that we’d need a prompt for. That would be the Resort itself, since we need to know what Resorts we’re interested in.
[list]* Edit the List of Values for the Resort object.

Check the Automatic Refresh Before Use option.
Click Edit.
Bring Country into the Conditions and set it to: in list Prompt(‘What Country are you interested in?’)
Click Save and Close
Click Apply and edit the next LOV object.
[/list]
Edit the Service Line and set up the same Prompt on Resort as: in list Prompt(‘What Resorts are you interested in?’)
Again… Save and Close, ensuring that the Automatic Refresh Before Use checkbox is selected then Edit the next.

Service will have: Service Line in Prompt(‘What Service Lines are you interested in?’) with the Automatic Refresh…

Save this Universe and let’s generate a report.

Generate a report that pulls in the Country, Service Line and Revenue. Set a prompt on Service Line and apply a prompt as a condition asking: ‘What Service Lines are we tracking?’

Refresh this report.

For the Service Line, click on Values… A new Prompt should appear, asking about the Services. Again… click the Values button, and so on… and so forth.

Once you’ve answered the questions, you only need to hit the Values button when you need to backtrack to another level.
Optional Prompt in XIR2 for BOB w Screenshots.doc (449.0 KB)

MichaelWelter (BOB member since 2002-08-08)

system · September 27, 2003, 11:50pm

Now that my LOV lets me use ALL for all values, how do I get the ALL in the list?

Author: Dwayne Hoffpauir

There are two approaches - overriding the LOV SQL, and creating a universe object.

Overriding the LOV SQL

Press the SQL button in the query panel of the LOV
Add the phrase UNION SELECT ‘ALL’ FROM DUAL or similar dummy table for your particular database. For SQL Server (if you don’t care if the object parses or not) add SELECT xx From xx UNION SELECT ‘ALL’ (You don’t need a FROM table)
Click the “Do not generate SQL before running” checkbox.

Universe Object

Create a universe object defined as a constant ‘ALL’
Associate it with the SYS.DUAL or equivalent table so it will parse (optional)
Press the combined query button in the query panel of the LOV (defaults to UNION)
Include the new universe object.

Cindy Clayton (BOB member since 2002-06-11)

system · June 2, 2004, 1:44am

I’ve deleted a universe and I’m in a mess! What do I do now?

Keep away from sharp objects . Take a deep cleansing breath and don’t panic

First, ask yourself some questions:

Can you get a copy of the universe from the web server?
Can you get a copy from another user or Designer’s PC?
Do you have a backup copy in a source control system?
Do you have a repository backup that your DBA could restore to a temporary location? If so you could do the stuff in this post and import the universe.

Reports will complain if they are unable to find the universe you’ve deleted. A thorough explanation by digpen is located here.

Cindy Clayton (BOB member since 2002-06-11)

system · June 2, 2004, 2:08am

What is synchronization?

Synchronization happens when BO creates two queries for you but doesn’t really understand how to merge the result sets. Say you have a product table, an inventory table, and a sales table. You join products to inventory on product_code and place that join in the inventory context. You join products to sales and place that join in the sales context. Create the product code object from the product table, the inventory measure from the inventory table, and the sales measure from the sales table. When you create a report in the query panel, you’ll get two JOINED queries. BO understands that it can write a query with inventory and product and another with sales and product and put them back together by product_code.

If you’re getting synchronized queries, you’ve got messed up contexts, joins not in the proper contexts or objects in the query that aren’t from dimension tables joined to both facts. It is very rarely ever acceptable to have synchronized queries.

Cindy Clayton (BOB member since 2002-06-11)

system · June 24, 2004, 4:21am

How do I migrate universes between repositories?

Although it is not advisable to have multiple repositories, sometimes it is necessary. Migrating universes from one repository to another is not difficult, at least the first time.

Log into Designer, using the Key of the repository that currently contains the universe.
Import the universe.
Click on File - Save As, and, in the Save As dialogue box, check “Save for all users”.
Click Save, and then Yes, to overwrite the existing universe.
Log back into Designer, using the Key for the destination repository.
Open the universe, and give it a new connection.
Export the universe.

In subsequent migrations, you will be prompted, during export, if you want to overwrite the existing universe. Click yes.

MichaelWelter (BOB member since 2002-08-08)

system · July 24, 2004, 1:10am

I’m confused about connections! What’s the difference between secured, personal, shared, full-client, and ZABO connections?

Glad you asked.

There are three types of connections:[list]Secured (Sec): stored in the repository
Personal (Per): stored on the workstation, and usable only by the BusObj user that created it
Shared (Shr): stored on the workstation, but usable by any BusObj user[/list]Next to consider is your method of connecting to the repository:[list]Full-client (FC) means you are using middleware that is on your workstation
ZABO means the middleware is on the ZABO server[/list]Now lets see how these two sets of distinctions work together (Y=Yes, N=No):

         ----Type---
  Method Sec Per Shr
    FC    Y   Y   Y
   ZABO   Y   N   N

Remember that Designer, Supervisor, and Business Query only use the full-client method of connecting. Reporter can use full-client or ZABO methods. Since the ZABO method cannot use personal or shared connections, it explains why ZABO users can’t use Free-Hand SQL.

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · August 10, 2004, 3:11pm

What is a fan trap, why is it a problem, and how do I resolve it?

Dave Rathbun describes it well in his presentation, ZEN and the ART of Universe Design. Which is located here: Dave's Adventures in Business Intelligence » Conference Presentations
Here is a quick synopsis of Fan Trap from his presentation:

A Fan Trap is a one - many - many relationship. This would happen if you are using a Summary and a Detail table in one query. Fan Traps can cause numbers to multiply. Business Objects resolves this transparently by using 2 queries. It is resolved automatically as long as the measure objects have aggregrate functions (query will split in 2). You just need to make sure the Multiple SQL Statements for Each Measure box is checked in Designer. It is a default setting, so before you turn it off consider what it is doing.

Another way to solve a Fan Trap comes from the Business Objects web site: [obsolete link removed]

Create an alias for the table containing the initial aggregation, then create a context for the alias table and a context for the original table. You can then apply the aggregate awareness function to optimize the query. This is the most effective way to solve the Fan trap problem.

Create an alias for the table that is producing the multiplied aggregation.
Create a one to one join between the original table and the alias table.
Build the object that is causing the aggregation on the alias tables.
Define a context for the original table and a context for the alias.
Select File>Parameters and click the SQL tab to display the SQL page.
Select the Multiple SQL Statements for Each Measure checkbox.
Modify the select statement for the columns that are summed so that the columns in the alias table are summed and not the columns in the original table.

[Edit - updated link to new presentations page at Dagira.com]

Bob (BOB member since 2002-06-06)

system · August 10, 2004, 3:13pm

What is a chasm trap, why is it a problem, and how do I resolve it?

Business Objects describes it best at this web site: http://tips.businessobjects.com/tips/unvdes/tip/ut001/ut001.htm

The Chasm trap occurs when two “many to one” joins converge on a single table. It is a one - many - one relationship. You will get incorrect results when the following circumstances exist:

A “many to one to many relationship” exists among three tables in the universe structure. The query includes objects based on two tables both at the “many” end of their respective joins. There are multiple rows returned for a single dimension.

One way to resolve a Chasm Trap use Designer to define a context for each table at the “many” end of the joins. This creates two SQL statements and two separate tables in Business Objects.
Another way to resolve a Chasm Trap is in Designer select the option Multiple SQL Statements for Each Measure from the Universe Parameters dialog box. (This is a default setting). Only applies to measures. You force the SQL generation engine in Reporter to generate SQL queries for each measure that appears in the Query panel. You cannot use this solution to generate multiple SQL statements for dimensions.

Bob (BOB member since 2002-06-06)

system · August 12, 2004, 3:28am

How can I implement row level security?

How to Implement Row-Level Security

First – what is Row-Level Security? When you want to specify which rows of data each userid is permitted to retrieve from the database, based on some column of data, or combination of columns of data – that is row-level security.

Row-level security can be implemented in BusinessObjects in several possible ways. If you have a tech support login, you can look at some of the techniques and the pros and cons in Steve Krandel’s 2002 International User Conference presentation Using Designer to Implement Row-Level Security.

Here are some of the ways possible:

1.) By letting the database do it. This is usually the most secure, although for logistic reasons, many sites do not find it feasible. To make this work, individual logins have to be set up in the database for each user, and those logins need to correlate to the BusinessObjects login and password. A possible way is to have a security table that is joined to the fact table(s) – via a view – and use the view in BusinessObjects. For this to work, the universe connection will need to specify “Use BusinessObjects username and password”. (This has sometimes been called “Advanced Login Strategy” in the past.)

2.) In BusinessObjects Supervisor – for a given group or user, select the universe. Go to the Rows tab of Universe Properties. Click the Add button, and select the table to be protected. Enter the appropriate WHERE clause to be imposed. However, only 256 characters of a WHERE clause can be entered in this panel, which many folks find very restrictive.

3.) A workaround to this is to define the WHERE clause in a special object in Designer, and impose that object in Supervisor, using @WHERE(special_object) for the WHERE clause.

4.) You can impose the security restrictions in Designer, by using self-joins and a maintained lookup table. You might end up with self-joins along the lines of:

DataTable.Region in ( Select Region From SecurityLookup Where User = @BOUSER)

Using Designer to Implement Row-Level Security in BOB’s Downloads has more information.

Dave Rathbun (BOB member since 2002-06-06)

system · August 31, 2007, 3:00pm

How can I use the current system date in an object?

You use a different function for each database. For example, Oracle users would typically use sysdate while Sybase or SQL Server users would use getdate(). Teradata uses DATE, and DB2 uses CURRENT_DATE. Ask your DBA if you are unsure which method is appropriate for you.

Ok, so I have done that, but my object does not parse. How can I fix it?

These functions or pseudo-columns do not need to reference a table. Yet when Designer parses these and other objects it will create a full SQL statement as:

SELECT object FROM table

When you don’t reference a table in your object definition, the SQL is not valid:

SELECT object FROM

Don’t worry about it, the object will still work in a query as long as you include at least one “real” object with it. Do not be tempted to include the DUAL table (Oracle specific tip) as you will add more problems to the universe than you solve.

What about other databases?

See the Designer Database Specific FAQ entry here.

Dave Rathbun (BOB member since 2002-06-06)

system · May 23, 2008, 10:46pm

How should I impose a WHERE clause on my object(s) in the Universe?

This question comes up very often, and the answer is that you usually don’t want to do this if there’s any chance that more than one object will have a WHERE clause on the same database column – because the conflicting WHERE clauses will be ANDed together, causing you to get No Results.

From a recent post by Dwayne Hoffpauir, where a person wanted 10 objects on the same database column each with different WHERE clauses:

Dwayne Hoffpauir:

Use a CASE syntax on the Select portion instead. Something like this:
CASE Table1.Column2 = 'String1' THEN Table1.Column1 END
Note that NULL will be returned if the condition is not met. You can specify an ELSE if you like. Assuming Table1.Column1 is numeric:
CASE Table1.Column2 = 'String1' THEN Table1.Column1 ELSE 0 END

Of course, this specific syntax only works if your particular database’s SQL supports the CASE syntax. If it doesn’t, then you should work with your DBA or co-workers to identify which alternate syntax to use instead.

Anita Craig (BOB member since 2002-06-17)

system · July 16, 2008, 2:23am

How can I provide a default value for a prompt?

Using standard code that is portable for all products you cannot do this as of XI 3.0. The standard prompt syntax via the @Prompt() function does not provide for a default value option. If your universe will only be used in a web environment then you can opt to make use of the extended syntax which will allow you to specify a prompt order, whether the prompt is persistent or not, and even include a default value. Note that these objects will probably not parse successfully as Designer is not aware of the extended syntax.

Okay, so if I use the extended syntax, how can I make the prompt default to another object? or a formula, like today’s date?

You can’t. The extended syntax only supports having a constant value as a default. You cannot use a formula or a reference to another object.

Dave Rathbun (BOB member since 2002-06-06)