BusinessObjects Board

FAQ: Designer

Designer FAQ

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

Is there any way to set up a prompt that will allow a user to select one value, many values, or type ‘ALL’ for all values in a list?

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

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

How do I migrate universes between repositories?

How do I create cascading or nested prompts?

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

How do I increase the number of items I can pick from a list of values?

What is synchronization?

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

Why do all my date lists of values include time and can I get rid of the time portion?

What are stored procedures and how do I use them?

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

How do I link universes? Why would I link universes? What are the restrictions? What are the advantages and disadvantages?

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

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

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

How can I implement row level security?

What functions are included in the Functions list, and can I add more if I want to?

How can I select a table based on the response to a prompt?

Should I use Contexts or Aliases to resolve my loops?

How can I use the system date in a prompt or an object? Why won’t they parse?

How can I provide a default value for a prompt? How can I make the prompt default to another object, or a formula like today’s date?

I have a universe and it tells me it’s from a different CMS. How can I open it?


Bob (BOB member since 2002-06-06)

prompt function
help CASCADING prompts for WEBi in UNIVERSE
Excluding promt values in Report
newly working on SDK
Can a prompt filter be optional?
Can we crate Optional promts??
dependent prompts
BO XI
How to add ALL in LOV's for an object
Solving a FanTrap issue
LOV and prompt
Creating an object to display ALL in BO Universe
relating to cascading prompt
ALL IN @PROMPT
Alias & context: When to use which one
CR XI - On-report parameters with a Universe?
ALL values in Designer propmt
Prompted Query
Default value for a prompt & @Select / @Prompt
To Insert ' ALL' at top of lov selected in BO6.5
All in prompt LOV
How to replace 'ALL' value with all rows
Prompt for Wildcard
Prompt options and select ALL of them.
Synchronized Queries
Input Date Format
Problems with Prompts
Using 'Matches Pattern' and 'Inlist' in one prompt.
Adding ALL to a LOV List
Programatically select ALL values for PROMPT
Prompt question
Prompts and LOV's
Prompt that default to ALL
Select ALL in Prompt
Best Methodology for Implementing "All" parameters
Running a report that has a prompt but leaving prompt blank
Wish list for new release of BusObjects & Webi
Creating a prompt where user can select "all"
BO 5.1.6 Prompt for "In List = All/Null" wildcard?
Low Level Security
Cascading prompt
Optional Prompts - Require Help
ALL option in prompt
Select all LOV values in a prompt
Multiple Measures
What is Optional Parameter?
Prompt in a derived table
INCLUDING "ALL" KEYWORD IN LIST OF VALUES
Using "ALL" in WEBI XI R2 Prompt in DB2
Finding Fan & Chasm Traps
Reporting
Adding *ALL and *NONE values to a list of values
best practices for resolving chasm and fan traps
Prompt - Add ALL in the List of values
Hierarchical prompts
how to create all prompt at designer level?
Default all of the values for a dimension in a report prompt
Passing on the power of contexts / design question
empty/no user selection for condition objects
Can we add All/NA to the list of Values
prompt order
prompt issue
Weird actions when using '%' functionality in prompt
Sample Record Prompt in Designer and Webi XI
Can we add All/NA to the list of Values
Hai i want to select all values from aprompt instead
ALL lov
How to bY Pass Values
How does query uses context?
All or * for any prompt in universe XI R2
Using 4 prompts; need to use them seperately in a query
ALL PROMPT
Regarding Fan Traps and Chasm Traps
Prompts in WebI
Adding hardcoded value like 'ALL' for a Prompt
Selecting all values in the Prompt
Creating a prompt where user can select "all"
Object condition with @prompt
Many roads to get there ...
Cascaded prompts
Flexible Prompts
Cascading Prompts
Flexible prompting
How to include ALL as a paramter values with other LOV's
ReadMe: Answering posts - how to
designer
Cascading prompts
what is FANTRAP PROBLEM,CHASM TRAP PROBLEM?how to reslove it
what is FANTRAP PROBLEM,CHASM TRAP PROBLEM?how to reslove it
How to use case to select filters in where clause
Custom scheduling
how to add 'ALL' in prompt? and does it works?

:?: Is there any way to set up a prompt that will allow a user to select one value, many values, or type ‘ALL’ for all values in a list?

:idea: Yes. Below is a sample of the syntax required to allow a user to select one, many, or all values from a prompt.

(@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 :us: (BOB member since 2002-06-11)

"ALL" Dynamic prompt
ALL option in the LOVs
can we keep the prompts text empty?
InterviewQuestion:where does BO stores LOVs of "ALL val
Looking for help on using "ALL" in @prompt
Including '*' or 'ALL' for prompts
How to select all values
How to create prompt "ALL'
Is it possibly to define prompts on report using SDK?
Using 'All' in cascading prompts
Accepting default value '%' in prompt to fetch all values
designer filter
Prompt based Date filter in Universe
FAQ: Prompt allows to select one value, many values or %
Selecting Date Range Issue
@Prompt In Free Hand SQL Report
prompts in WebI
Condition in prompt
Multiple prompts-enter either one prompt and disable others
Multiple user inputs for prompts
Find Facility
Prompts to be set NULL
Multiple prompts
SDK insight
Complex Prompt
problem with prompts in a report
Prompting for ALL or ONE
Multiple Prompt Report Question
Prompts
Custom prompt
Optional Prompts
Like Pattern, ALL, and List of Values prompt
Linking Prompts - NOT Cascading
Prompts in FHS report
prompts
Optional Query Prompt
choice to user for selecting only one prompt
Default user prompt
can I run a query with prompt empty?
Prompt question
Using Constants in Designer for Union Queries
WebIntelligence Report SQL modification
Adding a Condition
Missing Right paranthesis error while parsing
Problem using Multi in Prompt in Freehand SQL
Use of InList and Matches Pattern in a single prompt condit
@Prompt
Prompts in WEBI Report
List of values in prompts
Multiple Query Limits
More About Dates and Prompts
Prompts issue
don't generate where clause if user enters *
Prompts with default value
@ Prompt Syntax
Code for Optional prompts in BO XI R2
Help using ALL in paramters
Optional Prompts with Extras
Creating an 'ALL' value prompt
Prompts that select all values
problem in linking reports with mutiple parameters
All Prompt
Ability to search on criteria in prompts
Prompt with Null value
Modify run of the report
@Prompt
Adding 'All' as value in LOVs
Custom Static Values with Optional prompt
To create dynamic date range with one date input
All in prompt LOV
Prompt with Case Statement and Default
using ALL in prompt
optional prompt
Conditional prompts - help
Prompt an universe level
prompt issue
Opendoc : How to select ALL values?
@prompt issue
Filtering on Y, N or Y and N
Getting All values in a variable
Values not retrieving for "ALL"
Adding ALL in object lov
Adding a 'Show All' Option to my LOV
ALL LOV selection
@PROMPT with ALL key word
Conditions with Prompts
@Prompt with no value selected
ALL values
prompts
prompts
ALL or individual values prompt in WebI
All LOV's
List of values
Choosing one or many or all objects
How to create a ALL object in the Universe
"ALL"
Select 'ALL' in LOV
[V6.5] prompt with optionnal filters
Modify the Lov query to add ALL in list of values of prompt
Optional Prompts - Universe level
Need prompt based on or
"All" option in prompt LIST of VAlues
default selection in prompt
'ALL' in LOV's
ALL object
"All" option in the LOV
LOV listing problem with 'ALL'
List of values in prompt condition
ALL in LOV when number field
Entering "ALL" in a user prompt
All functionality issue
Need Sample macro program
Entering All or specific values in prompt but not both..
@Prompt - How do I retrieve all records for the prompt
how to Include 'ALL' in the Promt in XI R3
LOV
Prompts Question
Query issue
I want One LOV where I can Select ALL values Or Multi
Using either or condition in WEBI
@Prompt in Loop Format
Passing ALL Parameter
Using prompts with ALL
show all object values in prompt without selecting LOV
Prompt: %
Adding "ALL" to a Prompt List of Values
VBA Forms in Infoview/CMC
Prompt with (ALL) Values
how to use * in prompts
Multiple prompts in a BO report
To retrieve all values when selected ALL
Using ALL key word
Nested Prompts
How to use @prompt()
ALL in LOV
problem in prompt
I want to create multiple combo's
Entering info for 1 of 2 prompts
canwe add *ALL*value in runtimeprompt in ZABOreportlevel?wi
Prompt problem (custom LOV? Nested prompt?)
how to display "ALL" in LOV
Add "All" value to prompt list
Allowing users to select "all" from lov in prompt
How to - optional prompts?
Including 'All' in prompt
LOVs
Problem facing in Prompt
Flexible Prompts
How to make prompt as Optional in Universe level
How to pass all values in a prompt
How to pass all values in a prompt
Prompt - All values
Select All in parameter list
Unable to make prompts optional
Running a report with LOV value 'ALL'
how to add 'ALL' in prompt? and does it works?

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

:idea: 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 :wink: :mrgreen:


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

:?: 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…


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

:?: 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 :us: (BOB member since 2002-06-11)

:?: 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 :us: (BOB member since 2002-06-11)

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

:!: See here


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

:?: 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 :vatican_city: (BOB member since 2002-08-08)

cascading prompts
How to dynamically refresh promp value by using other prompt
How to create a cascading prompt in designer or in reporter
prompt only sales persons related to 1 section
About Prompts
Steps for making cascading of prompts in BO XI
Prompts using VB for a report with source as excel
How To Create Cascaded Prompts
Dependent LOVs… It’s possible????
cascading prompts
list of values
Creating an universe which enables drop-down menus
building a hierachy (drill list) of values in a prompt
User response in cascading prompt
multiple queries - cascading prompts
more Company with 1 universe
Cascading Prompts
Multiple Prompts in Report
Cascading prompts in WebI
Prompt filter
Cascaded Prompts
Cascading prompts
Dynamic/Cascading Prompts in Infoview 6.5
BO6.5 - Prompting on data levels (location) PLEASE HELP
@ prompt
Prompt dependently to another prompt with differente LoV
Nested Prompts?
One prompt depends on second
Automatic Display the Values for Other Prompts
Case When with @prompt
cascading prompt
Relative Prompts.
Prompts
Is it possible to have a prompt based on another prompt!!!
Multiple Prompts
how to retrieve the record of selection
Cascading Prompts
linked LOV or Dynamic LOV
LOV question.
Applying restriction on LOV
Prompts Drop down lists
Cascading Prompt in 6.5 Illustrated
How to create cascading Prompts
Second Prompt values based on First Prompt
Filtering second prompt depending on first prompt
Cascading prompt in Webi ?
Prompt and Nested Prompt
Retrieving values from one prompt to another
LOV with "ALL" option in XIR2
restricting LOV
Cascading Prompt
Cascading LOVs
Working with Parameters and Resulting Datasets
Displaying Prompt based on value from another prompt.
Cascading Prompt Question
Cascading Prompts
2nd Prompt LOV based on 1st Prompt input
Cascading Prompt with "All" option
Problem with Cascading prompt
LOV based on other LOV
cascading prompts
report parameter is dependent on another parameter
My Problem in selecting 'All' value in Cascading Prompt.
Problem with cascading prompt
Cascading prompts
Cascading with custom LOvs
How to use Cascading LOV
How to Hide the prompts?
"ALL" in Cascading LOV / Cascading Prompt
How to set 2nd prompt value based on the first prompt value
Cascade prompts
cascaded dynamic prompts
Multi value cascading prompt
Prompt based on the selection of a previous prompt
How to create cascading prompts
Cascading Prompt
Condition's value based on a previouse condition's value
Tabular view of Prompt
Dynamic SQL in LOV
prompts
cascading prompts
restrict LOV with object where clause
cascade prompt
LOV + ALL
Cascade Prompt
Predefined Conditions and Prompts
Dynamically creating LOV (BO 6.5)
How to Creat Cascade(Nested) prompt?
Cascading prompt
Cascading Prompts - NOT the Robert Metzker Way
Cascading prompts/nested prompts
Prompts with conditional values
@Prompt command
Creating a join based on prompt value
Cascaded prompts
Prompts and LOVs
multiple prompts
Prompts.. WebI or FullClient
regarding prompt
Two Prompts
Cascading Prompts Problem in BO 6.5 version
Cascading Prompts in Webi 6.5.1-logical hierarchy
Using all Cascading Prompt Values in Where clause for 6.5

:?: 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

  1. Press the SQL button in the query panel of the LOV
  2. 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)
  3. Click the “Do not generate SQL before running” checkbox.

Universe Object

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

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

How to add All in LOV list
How to create prompt "ALL'
ALL option in the LOVs
ALL option in the LOVs
all option in LOVs?
Setting 'ALL' in the prompt
ALL LOV Prompts
A question of filtering...
@prompts
Prompt based Date filter in Universe
Report where all can be selected
default values in prompts
Prompts
Including ALL in prompt
Custom prompt
Using Constants in Designer for Union Queries
How to include ALL as a paramter values with other LOV's
Union with derived table
-All- in LOV for SQL Server
Show 'ALL' in LOV in a prompt
lovs
Webi Prompts
Adding 'All' as value in LOVs
Custom Static Values with Optional prompt
To create dynamic date range with one date input
Prompt with Case Statement and Default
URGENT HELP NEEDED - PROMPT PASSING VALUES!
All in LOV
ALL in cascading prompts
optional prompt
using ALL in prompt
ALL value not displaying in LOVs
How to include ALL for an object of type number...
Prompt an universe level
how can i get All in my prompt
Opendoc : How to select ALL values?
Optional prompt in XIR2
Need help with Prompt to run for all the companies.
Parameter passing
Best approach ALL prompt values problem: Universe or report?
Getting All values in a variable
Adding ALL in object lov
Adding a 'Show All' Option to my LOV
All in the prompt
@PROMPT with ALL key word
Conditions with Prompts
LOV with ALL and None
@Prompt with no value selected
ALL values
prompts
ALL option for editing the LOV's
ALL or individual values prompt in WebI
Display * in @prompt
List of values
How to create a ALL object in the Universe
All option disappears when exporting universe
Code for Optional prompts in BO XI R2
Prompts that select all values
All Prompt
Ability to search on criteria in prompts
Prompt with Null value
Problem with my prompt
"ALL"
[V6.5] prompt with optionnal filters
Modify the Lov query to add ALL in list of values of prompt
Modify the Lov query to add ALL in list of values of prompt
"All" option in prompt LIST of VAlues
"All" option in prompt LIST of VAlues
Modifying a List of Values
default selection in prompt
Getting all values using Prompt IN THE DATA PROVIDER
NVL & List Of Values
Problem in @prompt using
Problem in @prompt using
providing ALL option in Prompt
How to get 'ALL' to show up in list of values?
How to include a selection value 'ALL' in Prompt Values
"All" option in the LOV
Adding an All option in prompt value
"ALL" in LOV
OLAP Universe PROMPT - want to include ALL
Entering "ALL" in a user prompt
ALL in LOV
Grouping prompt question better explained. I hope
All values in prompt
All functionality issue
Using List of Values with an 'All' Option
Need Sample macro program
@Prompt - How do I retrieve all records for the prompt
default value to all on date prompt
how to Include 'ALL' in the Promt in XI R3
LOV
Prompts Question
Getting 'ALL' or '*' into LOV problem
Query issue
Dual table in Sql Server 2005
Passing ALL Parameter
Using prompts with ALL
Condition Object Trick
Adding "ALL" to a Prompt List of Values
VBA Forms in Infoview/CMC
Prompt with (ALL) Values
"ALL" in Cascading LOV / Cascading Prompt
Using ALL key word
how to use * in prompts
How to use ALL in the where clause in prompts
Using ALL key word
problem in prompt
How to run a report by entering 2 prompts out of 3 prompts
report prompt with ALL option
* in lov
providing "ALL" at prompt level
LOV + ALL
canwe add *ALL*value in runtimeprompt in ZABOreportlevel?wi
Prompt problem (custom LOV? Nested prompt?)
Simple Prompt
"ALL" as Default Value in Prompt created in Univer
Add '*' value to prompt list
Problem to Include 'ALL' in the List of Values
Allowing users to select "all" from lov in prompt
Including 'All' in prompt
N/A in list of values(LOV)
Prompt with 'ALL'
Problem facing in Prompt
Flexible Prompts
how to add 'all' in lov
Want to display seperately for each user..
Displaying all LOVs using 'ALL' Prompt
Displaying all LOVs using 'ALL' Prompt
'ALL' Lov in Date Object

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

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

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 :us: (BOB member since 2002-06-11)

:?: 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 :us: (BOB member since 2002-06-11)

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.

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

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


MichaelWelter :vatican_city: (BOB member since 2002-08-08)

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

:idea: 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 :us: (BOB member since 2002-09-19)

:?: 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.

  1. Create an alias for the table that is producing the multiplied aggregation.

  2. Create a one to one join between the original table and the alias table.

  3. Build the object that is causing the aggregation on the alias tables.

  4. Define a context for the original table and a context for the alias.

  5. Select File>Parameters and click the SQL tab to display the SQL page.

  6. Select the Multiple SQL Statements for Each Measure checkbox.

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

Fan Trap, Chasm Trap
Fan Trap, Chasm Trap
Loops
Displaying Master/Detail information w/out duplicating info
Help in adding new class in the universe?
Inflated figures
Finding Fan & Chasm Traps
Best Approach to Resolve the Fan Trap
Chasm Trap / Fan Trap
Issue with SUM
Summing a measure
Fan Trap & resolving it using aliases
Universe
Fan Trap / Chasm Trap
Measures, and One-to-Many Table Relationships
Setting an incompatible object
universes
1:n relationship and Multiple sql statements for each measur
Not getting accurate results
Hierarchy double count
Report requires data from 2 fact tables
Example to Fan trap
Is this a Fan trap? Any better way to resolve it?
duplicating sales
Help to Solve on to many relationship involving measure
Context and Trap
Count Issue in Universe
Budget / Goal vs Actual Universe Design
Fan trap
revenue getting multiplied.please help
Measure issue
Traps
Fan trap, chams trap
superfluous aggregations
Contexts issue
Fan Trap
Issue with Fan Trap
Question on Aggregate awareness
Universe Loops Detection
Fan Trap? Chasm Trap?
using measures from different contexts: chasm Trap
Data Issue
Loops
How does query uses context?
Fan Trap with Dimension
Read Me: Best Way to Resolve Loops Context? Alias?
Measures Getting Inflated (Not sure is it a Fan Trap)
How to resolve the cubes in the DataManager
Context help
Multiple returns of measure objects
Information regarding traps
Multiple joins cartesian product issue
How do I prevent double counting in many-to-many relation
what is FANTRAP PROBLEM,CHASM TRAP PROBLEM?how to reslove it
Wrong Measures inspite of No LOOPS!!
Traps
fan trap problem

:?: 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.

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

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

:?: How can I implement row level security?

:idea: 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 :us: (BOB member since 2002-06-06)

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

:idea: 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?

:idea: 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?

:idea: See the Designer Database Specific FAQ entry here.


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

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:

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 :us: (BOB member since 2002-06-17)

:?: How can I provide a default value for a prompt?

:idea: 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?

:idea: You can’t. :slight_smile: 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 :us: (BOB member since 2002-06-06)