BusinessQuery for Excel User’s Guide
BusinessQuery 6.5 for Excel
Windows
2 BusinessQuery for Excel User’s Guide
Copyright
Trademarks
Use restrictions
Patents
Part Number
Copyright © 2004 Business Objects. All rights reserved.
If you find any problems with this documentation, please report them to Business Objects in
writing at documentation@businessobjects.com.
Business Objects, the Business Objects logo, Crystal Reports, and Crystal Enterprise are
trademarks or registered trademarks of Business Objects SA or its affiliated companies in the
United States and other countries. All other names mentioned herein may be trademarks of
their respective owners.
Contains IBM Runtime Environment for AIX(R), Java(TM) 2 Technology Edition Runtime
Modules (c) Copyright IBM Corporation 1999, 2000. All Rights Reserved.
This product includes code licensed from RSA Security, Inc. Some portions licensed from IBM
are available at http://oss.software.ibm.com/icu4j.
This software and documentation is commercial computer software under Federal Acquisition
regulations, and is provided only under the Restricted Rights of the Federal Acquisition
Regulations applicable to commercial computer software provided at private expense. The use,
duplication, or disclosure by the U.S. Government is subject to restrictions set forth in
subdivision (c)(1)(ii) of the Rights in Technical Data and Computer Software clause at 252.227-
7013.
Business Objects owns the following U.S. patents, which may cover products that are offered
and sold by Business Objects: 5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and
6,289,352.
305-10-650-01
BusinessQuery for Excel User’s Guide 3
Contents
Preface Maximizing Your Information Resources 5
Information resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Useful addresses at a glance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
About this guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Chapter 1 Introduction 13
Welcome to BusinessQuery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
What’s new in BusinessQuery? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Chapter 2 Getting Up and Running 21
Upgrading to BusinessQuery 6.5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Launching BusinessQuery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Chapter 3 Building Queries 31
Building a basic query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Building a more powerful query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Working with universes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Chapter 4 Managing Queries in the Workbook 47
Inserting an existing query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Manipulating query files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Editing queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Refreshing queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Working with the QueryDirector . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Contents
4 BusinessQuery for Excel User’s Guide
Chapter 5 Sending and Retrieving Queries and Other Documents 65
Using Corporate Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Sending and publishing queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Retrieving queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Packing and unpacking workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Chapter 6 Customizing BusinessQuery 87
General options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Options for file locations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Using SmartSpace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Contents
Maximizing Your Information Resources
preface
6 BusinessQuery for Excel User’s Guide
Overview
Information, services, and solutions
The Business Objects business intelligence solution is supported by thousands
of pages of documentation, available from the products, on the Internet, on CD,
and by extensive online help systems and multimedia.
Packed with in-depth technical information, business examples, and advice on
troubleshooting and best practices, this comprehensive documentation set
provides concrete solutions to your business problems.
Business Objects also offers a complete range of support and services to help
maximize the return on your business intelligence investment. See in the
following sections how Business Objects can help you plan for and successfully
meet your specific technical support, education, and consulting requirements.
Maximizing Your Information Resources
Information resources
Whatever your Business Objects profile, we can help you quickly access the
documentation and other information you need.
Where do I start?
Below are a few suggested starting points; there is a summary of useful web
addresses on page 10.
Documentation Roadmap
The Documentation Roadmap references all Business Objects guides and
multimedia, and lets you see at a glance what information is available, from
where, and in what format.
View or download the Business Objects Documentation Roadmap at
www.businessobjects.com/services/documentation.htm
Documentation from the products
You can access electronic documentation at any time from the product you are
using. Online help, multimedia, and guides in Adobe PDF format are available
from the product Help menus.
BusinessQuery for Excel User’s Guide 7
Documentation on the web
The full electronic documentation set is available to customers with a valid
maintenance agreement on the Online Customer Support (OCS) website at
www.businessobjects.com/services/support.htm
Buy printed documentation
You can order printed documentation through your local sales office, or from the
online Business Objects Documentation Supply Store at
www.businessobjects.com/services/documentation.htm
Search the Documentation CD
Search across the entire documentation set on the Business Objects
Documentation CD shipped with our products. This CD brings together the full set
of documentation, plus tips, tricks, multimedia tutorials, and demo materials.
Order the Documentation CD online, from the Business Objects Documentation
Supply Store, or from your local sales office.
Information resources
8 BusinessQuery for Excel User’s Guide
Multimedia
Are you new to Business Objects? Are you upgrading from a previous release or
expanding, for example, from our desktop to our web solution? Try one of our
multimedia quick tours or Getting Started tutorials. All are available via the Online
Customer Support (OCS) website or on the Documentation CD.
How can I get the most recent documentation?
You can get our most up-to-date documentation via the web. Regularly check the
sites listed below for the latest documentation, samples, and tips.
Tips & Tricks
Open to everyone, this is a regularly updated source of creative solutions to any
number of business questions. You can even contribute by sending us your own
tips.
www.businessobjects.com/forms/tipsandtricks_login.asp
Product documentation
We regularly update and expand our documentation and multimedia offerings.
With a valid maintenance agreement, you can get the latest documentation – in
seven languages – on the Online Customer Support (OCS) website.
Developer Suite Online
Developer Suite Online provides documentation, samples, and tips to those
customers with a valid maintenance agreement and a D
via the Online Customer Support (OCS) website.
Send us your feedback
Do you have a suggestion on how we can improve our documentation? Is there
something you particularly like or have found useful? Drop us a line, and we will
do our best to ensure that your suggestion is included in the next release of our
documentation: documentation@businessobjects.com
NOTE
If your issue concerns a Business Objects product and not the documentation,
please contact our Customer Support experts. For information about Customer
Support visit: www.businessobjects.com/services/support.htm
Maximizing Your Information Resources
EVELOPER SUITE license
Services
A global network of Business Objects technology experts provides customer
support, education, and consulting to ensure maximum business intelligence
benefit to your business.
How we can support you?
Business Objects offers customer support plans to best suit the size and
requirements of your deployment. We operate three global customer support
centers:
• Americas: San Jose, California and Atlanta, Georgia
• Europe: Maidenhead, United Kingdom
• Asia: Tokyo, Japan and Sydney, Australia
Online Customer Support
Our Customer Support website is open to all direct customers with a current
maintenance agreement, and provides the most up-to-date Business Objects
product and technical information. You can log, update, and track cases from this
site using the Business Objects Knowledge Base.
Having an issue with the product?
Have you exhausted the troubleshooting resources at your disposal and still not
found a solution to a specific issue?
For support in deploying Business Objects products, contact Worldwide
Customer Support at: www.businessobjects.com/services/support.htm
BusinessQuery for Excel User’s Guide 9
Looking for the best deployment solution for your company?
Business Objects consultants can accompany you from the initial analysis stage
to the delivery of your deployment project. Expertise is available in relational and
multidimensional databases, in connectivities, database design tools,
customized embedding technology, and more.
For more information, contact your local sales office, or contact us at:
www. businessobjects.com/services/consulting.htm
Looking for training options?
From traditional classroom learning to targeted e-learning seminars, we can offer
a training package to suit your learning needs and preferred learning style. Find
more information on the Business Objects Education website:
www.businessobjects.com/services/education.htm
Services
10 BusinessQuery for Excel User’s Guide
Useful addresses at a glance
Address Content
Business Objects Documentation
www.businessobjects.com/services/
documentation.htm
Business Objects Documentation
mailbox
documentation@businessobjects.com
Product documentation
www.businessobjects.com/services/
support.htm
Business Objects product information
Overview of Business Objects documentation. Links
to Online Customer Support, Documentation Supply
Store, Documentation Roadmap, Tips & Tricks,
Documentation mailbox.
Feedback or questions about documentation.
The latest Business Objects product
documentation, to download or view online.
Information about the full range of Business
Objects products.
www.businessobjects.com
Developer Suite Online
www.techsupport.businessobjects.com
Knowledge Base (KB)
www.techsupport.businessobjects.com
Tips & Tricks
www.businessobjects.com/forms/
tipsandtricks_login.asp
Maximizing Your Information Resources
Available to customers with a valid maintenance
agreement and a Developer Suite license via the
Online Customer Support (OCS) website. Provides
all the documentation, latest samples, kits and tips.
Technical articles, documents, case resolutions.
Also, use the Knowledge Exchange to learn what
challenges other users – both customers and
employees – face and what strategies they find to
address complex issues. From the Knowledge
Base, click the Knowledge Exchange link.
Practical business-focused examples.
Address Content
Online Customer Support
BusinessQuery for Excel User’s Guide 11
www.techsupport.businessobjects.com
www.businessobjects.com/services
Business Objects Education Services
www.businessobjects.com/services/
education.htm
Business Objects Consulting Services
www.businessobjects.com/services/
consulting.htm
Starting point for answering questions, resolving
issues.
Information about registering with Worldwide
Customer Support.
The range of Business Objects training options and
modules.
Information on how Business Objects can help
maximize your business intelligence investment.
Useful addresses at a glance
12 BusinessQuery for Excel User’s Guide
About this guide
This guide is about BUSINESSQUERY for Excel, the add-in to Microsoft Excel that
enables users to query relational databases using the B
technique. It explains the basic tasks that allow users to build and maintain
queries.
Audience
This guide is intended for the end user who is responsible for running and
maintaining queries with B
technical background, nor have knowledge of the structure of the database at the
USINESSQUERY shields users from these issues, thanks to a semantic layer
site. B
that presents data in everyday business terms.
Conventions used in this guide
The conventions used in this guide are described in the table below.
Convention Indicates
This font Code, SQL syntax, computer programs. For
Some code
more code
$DIRECTORYPATHNAME The path to a directory in the Business Objects
USINESSOBJECTS query
USINESSQUERY. The user need not come from a
example: @Select(Country\Country Id).
This font is also used for all paths, directories,
scripts, commands and files for UNIX.
Placed at the end of a line of code, the symbol ()
indicates that the next line should be entered
continuously with no carriage return.
installation/configuration directory structure. For
example:
• $INSTALLDIR refers to the Business Objects
installation directory.
• $LOCDATADIR refers to a subdirectory of the
BusinessObjects installation directory called
locData.
Maximizing Your Information Resources
Introduction
chapter
14 BusinessQuery for Excel User’s Guide
Welcome to BusinessQuery
BusinessQuery for Excel is an add-in tool that provides Microsoft Excel with fully
functional database access. With BusinessQuery, you can access your corporate
databases from Excel using familiar business terms. All BusinessQuery
commands are available through the BusinessQuery menu and toolbar that
appear in Excel. The result is easy and intuitive information access with
guaranteed, reliable results.
When you run a query, BusinessQuery automatically places the results into cells
of an Excel worksheet. There is no need to copy or export the results to Excel.
Your results are not a static embedded object. Instead, they can be used with the
full range of Excel functions, including calculations, charts, and pivot tables.
Thanks to SmartSpace, BusinessQuery also lets you choose the most logical
way to place data in a spreadsheet. Thus, you avoid overwriting existing data,
formulas and formats.
BusinessQuery makes it easy to access data
In BusinessQuery, you work with data in business terms that are familiar to you.
What’s more, you do not need any knowledge of the database structure, or
database technology, to retrieve data that is relevant to your work.
Introduction
Universes
Universes provide the business-intelligent, semantic layer that isolates you from
the technical issues of the database. A universe is the environment that holds
your data in a semantic layer that can then be exploited by BusinessQuery or
other Business Objects applications.
Universes are made up of classes and objects. For example, the objects in a
human resources universe can be Names, Addresses, Salaries, and so on.
Classes are logical groupings of objects. Each class has a meaningful name,
such as Vacation (for objects pertaining to employees’ vacations). Each object
maps to data in the database, and enables you to retrieve data that appears in
Excel.
In your company or organization, universes are created by a universe designer,
using Designer. The designer then makes universes available to you and other
users at your site, so that you can access the data you want from the database.
A demo universe that maps to a demo database are delivered with
BusinessQuery. A full description of these is provided in “The demonstration
database and universe” below.
Queries
Queries enable you to retrieve data in a database, via a universe. You build a
query to bring data to Microsoft Excel.
The Query Panel
The Query Panel is the one-step graphical interface that you use to build and run
queries in BusinessQuery. The objects of the universe you are working with
appear as icons. Thus, you specify the objects you want to include in a query by
dragging and dropping icons with your mouse. When you run a query, the data
mapped by the objects you specified is retrieved from the database, and appears
in Excel.
A detailed illustration of the Query Panel is provided on page 35.
Query files
A BusinessQuery file (extension .bqy) stores the definition of a query and the
data it returns. By default, query files are located in the MyBQY folder under the
Business Objects Documents folder in MyDocuments. You can exchange query
files with other users that have BusinessQuery.
The demonstration database and universe
A demonstration database and universe are installed with BusinessQuery, and
used in the examples in this guide and in the online help. The database runs on
Microsoft Access.
The universe, which accesses the data in the database, is called Island Resorts
Marketing. It is designed for an imaginary tour operator that runs beach clubs in
different resorts around the world. You use it to retrieve data on sales and
reservations for resorts and customers, over time. The illustration on page 17
shows the universe’s classes and objects as they appear in BusinessQuery.
Because universes provide a business-intelligent semantic layer between you
and the database, the names of the classes and objects in the demonstration
universe are self-explanatory. For example, the
map to data on resorts:
• The
• The
• The
Resort object retrieves the names of the company’s resorts.
Service object retrieves data for the types of services in each resort:
accommodation, food and drinks, and recreation.
Service Line object retrieves data for the types of service in each resort,
such as, family suite (for accommodation), restaurant (for food and drinks),
and so on.
BusinessQuery for Excel’s User Guide 15
Resort class contains objects that
Welcome to BusinessQuery
16 BusinessQuery for Excel User’s Guide
For more information on classes and the different types of objects you find in
BusinessQuery, refer to “Classes and subclasses” and “Dimensions, details, and
measures” below.
Classes and subclasses
The demonstration universe contains five classes: Region, Customer, Sales,
Reservations and Measures. The purpose of classes is to provide logical
groupings of objects. For example, the
to data on customers in the database.
The
Customer class contains a subclass, which is entitled Sponsor. A subclass
is to a class what a subfolder is to a folder.
Dimensions, details, and measures
When creating universes, universe designers define objects. An object can be
qualified as a dimension, a detail, or a measure. Each type of object serves a
different purpose:
Customer class contains objects that map
Dimension
object
Dimension objects typically retrieve
character-type data (customer names,
product names), or dates (years,
quarters, reservation dates)
Detail object A detail object is always associated
with one dimension object, on which it
provides additional information. For
example, Address is a detail object that
is associated with Customer. Address
provides additional information on
customers, such as, their addresses.
Measure
object
Measure objects retrieve numeric data
that is the result of calculations on data
in the database. In the demo universe,
Revenue is the calculation of number
of items sold multiplied by item price.
Measure objects are usually located in
the Measures class.
Measure objects are semantically dynamic: the values they return depend on
the objects they are used with. For example, if you include
Revenue in a query, revenue per resort is calculated. If you include Customer
Revenue, revenue per customer is calculated, and so on.
and
Resort and
Introduction
BusinessQuery for Excel’s User Guide 17
The following illustration shows the classes, subclasses and objects of the
demonstration universe as they appear in the Query Panel.
A folder represents a
class.
Each icon within a class
represents an object.
The demonstration universe, Island Resorts Marketing
Welcome to BusinessQuery
18 BusinessQuery for Excel User’s Guide
BusinessQuery integrates with Microsoft Excel
Integration with Excel is key to BusinessQuery. Query results appear in Excel
worksheet cells. You can then use the full range of Excel commands on your
data.
The following paragraphs introduce other ways in which BusinessQuery provides
seamless integration with Excel.
The Query Panel and the worksheet
When building or editing a query, you work in the Query Panel. This graphical,
drag-and-drop interface appears in the main Excel window, and remains active
until you run the query. Because the Query Panel remains on the screen, you can
reference cells in the active worksheet when defining conditions. For example, to
exclude data in a range of cells from the query results, you need access to the
query panel and the active worksheet.
NOTE
The online help provides examples of using cell references in conditions. To view
an example, type “complex conditions” in the Help Topics dialog box, doubleclick “examples using cell references”, then double-click a topic in the list that
appears.
Introduction
BusinessQuery and Visual Basic
Information generated during a BusinessQuery work session is written to a
reference worksheet in the active workbook. For example, the names of queries
and the users who created them are recorded.
The functions and the information in the reference worksheet can be included in
Visual Basic macros and formulas. Thus, Excel users who are familiar with Visual
Basic, manipulate BusinessQuery information and perform scheduled or batch
query processing.
NOTE
For more information on BusinessQuery and Visual Basic, refer to “Integrating
BusinessQuery with Visual Basic” in the online help.
What’s new in BusinessQuery?
BusinessQuery offers the following new features.
Corporate Categories
When retrieving a document or selecting categories, the list of documents and
categories appears in a hierachical category tree.
Finding text or objects in a query
BusinessQuery allows you to find text or objects in the Query Panel so you don’t
have to search the entire list manually. The find in query function searches all
objects and their detail objects.
BusinessQuery for Excel’s User Guide 19
What’s new in BusinessQuery?
20 BusinessQuery for Excel User’s Guide
Introduction
Getting Up and Running
chapter
22 BusinessQuery for Excel User’s Guide
Overview
This chapter covers the installation, launch and management of the
BusinessQuery add-in for Microsoft Excel. You will find information on:
• Working with universes and queries that you used in BusinessQuery 5.1.
• Launching BusinessQuery in online and offline mode.
• The BusinessQuery toolbar which appears when you launch the application.
• Managing work sessions, for example, by logging in as a different user.
• Disabling BusinessQuery, and relaunching Excel.
As with all BusinessObjects products, BusinessQuery is installed by the
BusinessObjects installer program. A separate guide, the Installation and
Configuration for Windows Guide, provides detailed information on:
• The hardware and software requirements BusinessObjects products.
• The different types of installation, such as Master Setup and Stand-alone
Setup.
• Running the installer program.
Getting Up and Running
Upgrading to BusinessQuery 6.5
This section describes how to upgrade from version 5.1 of BusinessQuery.
Upgrading from BusinessQuery 5.1
No specific procedure is required for upgrading to version 6.5. All you have to do
is install the new software, then launch a BusinessQuery session in the normal
way.
For a summary of the new features in version 6, refer to What’s new in
BusinessQuery? on page 19.
NOTE
Because no upgrade procedure is required when you move from version 5.1 to
version 6.5, the Upgrade Workbook command no longer exists in version 6.5.
BusinessQuery for Excel’s User Guide 23
Upgrading to BusinessQuery 6.5
24 BusinessQuery for Excel User’s Guide
Launching BusinessQuery
Once BusinessQuery is installed at your site, you must activate the
BusinessQuery add-in. To do this:
1. Launch Microsoft Excel.
2. Click the Add-Ins command on the Tools menu.
The Add-Ins dialog box appears.
3. If BusinessQuery 6.0 is displayed in the dialog box, go to step 6.
4. If the option is not displayed, click Browse, and, in the dialog box that
appears, move to the bin folder, which is under $INSTALLDIR\bin.
5. Click Bqapi.xll, then click OK.
BusinessQuery 6.0 appears in the Add-Ins Available box.
6. In the Add-Ins dialog box, click OK.
The Add-Ins dialog box closes, and the BusinessQuery menu appears
between the Data menu and the Window menu.
7. Click the Load command.
This command loads the BusinessQuery application. It may cause the User
Identification dialog box to appear if the BusinessObjects supervisor has set
up a repository for user administration.
8. If the User Identification dialog box appears, enter the user name and
9. If necessary, select a security domain, then click OK.
Getting Up and Running
password that your BusinessObjects supervisor provided.
For information on security domains, refer to Using multiple security domains
on page 30.
The BusinessQuery toolbar now appears in the Excel application window. Refer
to page 26 for an illustration and description of the toolbar.
Online/Offline modes
Online and offline modes are options that apply when you are working in an
environment with a repository, which is set up by a BusinessObjects supervisor.
A repository enables users to share BusinessObjects resources, such as
universes and query files.
When you launch a BusinessQuery session, the Use in Offline Mode check box
in the User Identification dialog box (illustrated on page 24) lets you indicate the
mode you wish to work in:
• Online mode is appropriate for a networked environment in which the general
supervisor has set up a repository. In online mode, you can run and refresh
queries on universes which are stored on the repository and which access
remote databases.
Online mode also enables you to send query files to other users, and to
retrieve query files that other users have sent you. For more information on
sending and retrieving query files, refer to .
• Offline mode is appropriate for working away from your site, such as with a
laptop computer, or whenever your network goes down.
In offline mode, you cannot run or refresh queries on universes which are
stored on the repository, nor send or retrieve query files.
BusinessQuery for Excel’s User Guide 25
TIP
In offline mode, you can build queries and generate their SQL without retrieving data.
Later, when working in online mode, you can run such queries and thereby retrieve
data. For more information, refer to “Setting options and running a query” on page 43.
Launching BusinessQuery
26 BusinessQuery for Excel User’s Guide
The BusinessQuery toolbar
When you launch BusinessQuery, the BusinessQuery toolbar appears in the
Excel application window. The buttons on the toolbar enable you to carry out
tasks with simple mouse clicks, as illustrated and described below:
ab cde fg h i
a) New Query
Lets you build a new query. Displays the New Query dialog box, in which you
select the universe you want to use. You then build the query in the Query
Panel. To use this button, you must first click an empty cell.
b) Insert Query
Inserts the data retrieved by an existing query. To use this button, you must
first click an empty cell.
c) Edit Query
Opens the Query Panel, where you edit and run an existing query. To use this
button, you must first click a cell containing data returned by the query.
d) Refresh Query
Refreshes one query. The fresh data set appears in the worksheet. To use
this button, you must first click a cell containing data returned by the query.
e) Refresh All Queries
Refreshes all the queries in the active workbook. The fresh data sets appear
in the workbook.
f) QueryDirector
Opens the QueryDirector, whose three tabs enable you to manage the
queries inserted in the current workbook.
g) Update Workbook
The queries in the workbook are processed (for example, refreshed) in the
order in which they appear in the Update tab of the QueryDirector.
h) Properties
Displays the Query Properties dialog box, which provides information on the
query that returned the data currently selected in the worksheet.
i) Help
Displays the BusinessQuery online help.
Properties is the only button that does not have an equivalent menu command.
Getting Up and Running
BusinessQuery for Excel’s User Guide 27
Viewing tooltips
The BusinessQuery menu and toolbar are built to Microsoft Office 95 standards.
Thus, when you rest the cursor over a toolbar button, its tooltip appears:
Moving the toolbar
As all toolbars in Excel, the BusinessQuery toolbar is fully dockable. In other
words, you can place it at the top, bottom or on either side of the application
window. You can also let it “float” on top of a worksheet.
1. Click a blank area in the toolbar and hold down your left-mouse button.
Note that if the toolbar is floating, as in the illustration above, you can click its
title bar and hold down your mouse button.
2. Drag the mouse until the toolbar reaches the desired location:
For the top of the window, drag until the toolbar is on top of the Formula bar.
For the bottom of the window, drag until the toolbar is on top of the worksheet
tabs.
For either side of the window, drag as far as you can to the left or right.
To float the toolbar, drag it until is on top of the worksheet itself.
3. Release the mouse button.
TIP
To resize a floating toolbar, click any of its borders, hold down the left-mouse button
and drag to the left or right, up or down.
Hiding and displaying the toolbar
You can hide and display the BusinessQuery toolbar as you can any Excel
toolbar:
1. Click any toolbar with your right-mouse button.
A pop-up menu appears. A check next to a toolbar name indicates that it is
displayed.
2. Click BusinessQuery on the pop-up menu.
Launching BusinessQuery
28 BusinessQuery for Excel User’s Guide
Managing BusinessQuery work sessions
This section provides information on managing BusinessQuery work sessions
once you have launched the application and selected the Load command. For
information on launching the application, refer to page 24.
Logging in as a different user and/or in a different mode
If you want to log in as a different user, for example, with a different user name
and corresponding password:
1. Click the Login As command on the BusinessQuery menu.
The User Identification dialog box appears.
2. Enter the user name and password of the different user.
3. If you wish to switch from offline mode to online mode or vice versa, click Use
in Offline Mode. Click OK.
The rights BusinessQuery users have are managed by the BusinessObjects
supervisor. The supervisor can, for example, allow some users to change their
password, others not. If the rights of the user you have just logged in as are
different from the initial user’s rights, some commands on the BusinessQuery
menu may now be unavailable.
If the BusinessObjects supervisor allowed it, you can change your password. To
do this:
1. Click the Change Password command on the BusinessQuery menu.
2. In the dialog box that appears, type your current password in the Current
3. Type the new password in the New Password box, then type it again in the
You can use two options in the General tab of the Options dialog box to start work
sessions automatically:
• The AutoLoad BusinessQuery option causes BusinessQuery to load when
• The Use Automatic Login option starts the work session without prompting
Getting Up and Running
Changing your password
Password box.
Confirm Password box. Click OK.
Automatically launching BusinessQuery when you launch Excel
you launch Excel.
you to enter your user name and password.
BusinessQuery for Excel’s User Guide 29
Thus, if you activate both options, you launch BusinessQuery on launching
Excel. Note that the BusinessObjects supervisor can make these options
unavailable, for security reasons.
1. Click the Options command on the BusinessQuery menu.
2. In the General tab, activate AutoLoad BusinessQuery.
3. Optionally, activate Use Automatic Login.
Unloading BusinessQuery
You can free up some active memory in Excel by selecting the Unload command
on the BusinessQuery menu. This command disables all commands other than
Send To, Retrieve From, Load and Help.
Quitting and relaunching Microsoft Excel
When you quit Microsoft Excel at the end of a work session, the BusinessQuery
add-in remains installed. Thus, the next time you launch Excel, the
BusinessQuery menu is still available. All you have to do is click the Load
command.
Disabling the BusinessQuery add-in
If you want to remove the BusinessQuery menu and toolbar from Excel:
1. Click the Add-Ins command on the Tools menu.
2. In the Add-Ins dialog box, click the BusinessQuery 6.0 check box, then click
OK.
Launching BusinessQuery
30 BusinessQuery for Excel User’s Guide
Using multiple security domains
When logging on to BusinessObjects applications, you may have the choice
between security domains which your supervisor has set up.
Multiple security domains enable supervisors to store security information about
end users on different repositories in different locations. You can select the
security domain of the repository that is closest to your office, so that you reduce
the time it takes to log on. The User Identification dialog box is as illustrated here:
Note that if you have access to one security domain only, the User Identification
dialog box is as illustrated here:
Getting Up and Running
Building Queries
chapter
32 BusinessQuery for Excel User’s Guide
Overview
Building a query enables you to retrieve data from a database, and to display this
data in an Excel worksheet. You build a query by selecting objects from a
universe. The objects map to the database in familiar business terms.
This chapter begins with the basics of building a query. Then, you will find
information on ways of building a more powerful query; for example, by applying
a condition that limits the data that the query retrieves.
NOTE
For more advanced users, the chapter entitled “Building More Advanced
Queries” in the online help contains information on more complex query tasks.
References to specific help topics are indicated by the Help icon, as shown in the
margin.
Building Queries
Building a basic query
Building a basic query involves the following steps:
• Selecting the universe on which you want to build the query.
• Including objects in the query.
To build a basic query:
1. Launch BusinessQuery.
Launching BusinessQuery is described on page 24.
2. In the worksheet, click inside a blank cell in which you want the data from the
query to appear.
3. Click the New Query button on the toolbar, or select the New Query
New Query
command on the BusinessQuery menu.
The New Query dialog box appears:
BusinessQuery for Excel’s User Guide 33
4. Click the universe you want to use.
5. Type a name for the query in the Query Name box.
You cannot use @ or = as the first character of a query name. Excel uses
these characters to call macros or user functions.
Building a basic query
34 BusinessQuery for Excel User’s Guide
6. Type a description for the query in the Query Description box, then click OK.
The Query Panel appears. The Query Panel is the graphical interface that
displays the universe’s classes and objects. You drag and drop the objects
you want to include in the query. The Query Panel is illustrated on page 35.
TIP
In the General tab of the Options dialog box (Options command), you can specify a
default universe that will be preselected in the New Query dialog box. For more
information, refer to Specifying data access on page 92.
Viewing the objects that you can include in the query
In the Query Panel, the Classes and Objects box presents the classes,
subclasses and objects of the universe that you are using. Objects represent the
data that you can retrieve via the universe. Classes are logical groupings of
objects. Classes can also contain subclasses, as folders can contain subfolders.
When the Query Panel appears, only the universe’s classes are visible. Click the
plus sign (+) sign to the left of a class folder to view the class’s objects and
subclasses.
Including objects in the query
When you include objects in the query, you instruct BusinessQuery to retrieve the
data for those objects from the database. For example, to display revenue by
resort in your worksheet, you include the
query.
You include an object in a query by placing it in the Result Objects box. There are
three ways of doing this. You can:
• Click an object icon in the Classes and Objects list, and drag it to the Result
Objects box.
• Double-click an object in the Classes and Objects list.
• Click a class icon and drag it to the Result Objects box. All the objects in the
class appear in the Result Objects box.
Once you have included objects in the Result Objects box, you have built a basic
query. You can click Run to have the query retrieve the data from the database.
Revenue and Resort objects in the
Building Queries
The Query Panel
BusinessQuery for Excel’s User Guide 35
a
b
c
i
j
e
d
f
k
g
h
a. Classes are represented by folders.
b. Objects are represented by cubes, spheres or pyramids (here, by a cube).
For information on the different types of objects in a universe, refer to
“Dimensions, details, and measures” on page 16.
c. This button, selected by default, displays the universe’s classes and objects.
d. This button enables you to set options before running the query, for example
to specify a maximum number of rows.
e. This button displays the universe’s predefined conditions. For more
information, refer to page 40.
f. The Find feature enables you to search for the names of objects in the
hierarchy.
g. The Result Objects box displays the objects that are included in the query.
h. The Conditions box displays conditions on the query. For more information,
refer to page 39.
i. Save and Close lets you save the query you have defined without running it.
You can run it later on by using the Refresh Query command.
Building a basic query
36 BusinessQuery for Excel User’s Guide
j. The View button is unavailable in BusinessQuery.
k. When you click Run, the query connects to the database, the Query Panel
closes and the data appears in the worksheet.
Removing objects from a query
If you decide you want to remove an object from the query you are building, click
its icon in the Result Objects box then press the Delete key. Alternatively:
• Drag the icon to the Classes and Objects list.
• Click the icon with your right-mouse button, then select the Remove
command on the pop-up menu that appears.
Repeat to remove other objects from the query.
Changing the order of the objects in a query
The order in which the objects appear in the Result Objects box determines the
order in which the data will appear in the worksheet. To move an object, click its
icon. You can now:
• Drag the icon to the left or the right, then release the mouse button.
• Swap the icon with another object icon in the Result Objects box, by holding
down the Shift key, dragging it until it is above the object you want to swap,
then releasing your mouse button.
Saving the definition of a query
You can now build a query without having to run it right away. This feature lets
you:
• Save a query so that you can continue defining it at a later stage.
• Save a query that you have finished defining, but that you do not want to run
right away, for example because you know that network traffic is heavy so
you’d rather wait.
To benefit from this feature, use the Save and Close button in the Query Panel.
The result objects from the query appear as column headings. You then refresh
the query in order to view the data.
Building Queries
Running the query
Once you have selected the objects to be included in the query, click Run.
BusinessQuery displays the results in a range of cells in the Excel worksheet.
The worksheet contains the results of the query made up of the objects Year, Country, and
Revenue.
Choosing the formats of your query results
Your query results appear with default BusinessQuery formatting: blue column
headers and yellow cell backgrounds for the actual data. If you prefer the results
to appear with plain column headers and cell backgrounds, switch off the Use
Default Formatting option. To do this:
1. Select the Options command on the BusinessQuery menu.
The Options dialog box appears.
2. Click the General tab.
3. Deactivate Use Default Formatting, then click OK.
The universe designer can preformat objects in the universe. However, formats
applied in the universe are not supported by BusinessQuery. They are valid in
BusinessObjects only.
BusinessQuery for Excel’s User Guide 37
Building a basic query
38 BusinessQuery for Excel User’s Guide
Queries and query files
When you build a query, BusinessQuery stores its definition and the data it
retrieves in a query file. The file takes the name you entered in the Query Name
field of the New Query dialog box; its extension is .bqy.
You use query files when:
• Inserting queries in worksheets. Refer to Inserting an existing query on
page 49 for more information.
• Exchanging queries with other users. Thanks to query files, you can send
queries to other users via the repository, which is a centralized database set
up by the BusinessObjects supervisor.
Refer to Chapter 5 for more information on exchanging queries.
By default, query files are stored in the MyBQY folder in the BusinessObjects
Documents folder under MyDocuments. For information on changing this default
folder, refer to Options for file locations on page 94.
Building Queries
Building a more powerful query
The most basic queries contain objects only. However, the tasks described in the
following sections enable you to control the data that your queries retrieve. You
can:
• Limit the query results to data that satisfies conditions.
• Sort data, for example alphabetically.
• Specify the number of rows of data you want the query to return.
• Eliminate duplicate rows of data from the query result.
All the above tasks are easy to perform for non-technical end users. In the online
help, you can find information on more complex query tasks that are more likely
to be performed by advanced users. For more information, refer to the following
topics in the help:
“What you can do with the Sorts dialog box”
“Ways of going further with conditions”
“What you can do with your queries’ SQL scripts”
“What are combined queries?”
BusinessQuery for Excel’s User Guide 39
Applying conditions on a query
A condition is a way of limiting the data that a query returns. For example, the
Resort object in the Island Resorts Marketing universe retrieves five values:
Australian Reef, Bahamas Beach, French Riviera, Hawaiian Club and Royal
Caribbean. You can apply a condition on the
only one, two, three or four of the resorts.
In this guide, you learn how to apply two kinds of conditions, which are:
• Predefined conditions that universe designers create when they build
universes.
• Your own simple conditions, which you create by selecting the values that you
want an object to retrieve.
For information on applying two or more conditions on a query, refer to “What is
involved in working with groups of conditions?” in the online help. For information
on more advanced types of conditions, refer to “Ways of going further with
conditions” in the online help.
Resort object to restrict the data to
Building a more powerful query
40 BusinessQuery for Excel User’s Guide
To apply a predefined condition
When creating a universe, the universe designer can create conditions for you to
use. These predefined conditions belong to a class in the same way that an
object does. You can apply one or more of these conditions when you build a
query. However, you cannot edit predefined conditions, nor can you delete them
from the universe.
To apply a predefined condition:
1. Click the Predefined Conditions radio button, which is located below the
Classes and Objects box in the Query Panel.
Predefined
Conditions
The Predefined Conditions box replaces the Classes and Objects box. The
predefined conditions in the demo universe are illustrated here:
Building Queries
2. Double-click the predefined condition you want to apply.
The condition appears in the Conditions box.
To remove a predefined condition
Click the condition’s icon in the Conditions box, then press the Delete key.
Alternatively, click the condition with the right mouse button, and from the pop-up
menu, click Delete.
Simple Condition
BusinessQuery for Excel’s User Guide 41
To apply your own simple condition
Before you can apply a simple condition on an object, you must include the object
in the query. Then, you click the object’s icon in the Result Objects box and click
the Simple Condition button on the toolbar.
When you click the Simple Condition button, the list of values for the object is
retrieved from the database, and appears in the List of Values of Object Name
dialog box:
Hold down the Ctrl key on your keyboard, click the values you want the object to
retrieve, then click OK.
When you run the query, only the data corresponding to the values you selected
will appear in the worksheet.
To select different values for a simple condition
Once you have applied a simple condition on an object in a query, you can edit it
by selecting different values for the object to return. To do this:
1. In the Conditions box of the Query Panel, click the value(s) that appear(s) on
the right-hand side of the condition.
The Classes and Objects box becomes the Operands box.
2. Double-click the "Show list of values" operand.
The object's list of values appears in the List of Values dialog box.
Building a more powerful query
42 BusinessQuery for Excel User’s Guide
3. If you want to select more values for the condition, hold down the Ctrl key and
then, in the List of Values dialog box, click each value that you want the object
to retrieve.
4. Click any selected values that you do not want the object to retrieve, and click
OK.
To delete a simple condition
Click the condition in the Conditions box, then press the Delete key.
Applying sorts
Sorts control the order in which data appears: ascending or descending. For
example, you can apply a sort on a measure object so that its data appears in
ascending order, for example, from lowest to highest values.
The following table shows the order in which different types of data appear:
If you display Text will appear Numbers will
appear
Ascending order A-Z lowest to highest past to present
Descending order Z-A highest to lowest present to past
To apply a sort on an object
1. Click an object in the Result Objects box.
Click the Sort button on the toolbar.
A sort icon appears beside the object icon in the Result Objects box.
To remove a sort
There are two ways of doing this:
• Click the sort icon and press the Delete key.
• Drag the sort icon from the object in the Result Objects box, to the Classes
and Objects list, where you release your mouse button.
In both cases, the sort icon disappears from the object in the Result Objects box.
To invert the order of a sort
Double-click the sort icon beside the object. The arrow in the sort icon appears
the other way up, to indicate that you have inverted the order of the sort.
Dates will
appear
Building Queries
To manage multiple sorts
You can manage multiple sorts by using the Sorts dialog box, which appears
when you click the Manage Sorts button on the Query Panel toolbar. You also
use this dialog box to apply sorts on objects that are not included in the query.
Manage Sorts
For information on managing sorts in the Sorts dialog box, refer to “What you can
do with the Sorts dialog box” in the online help.
Setting options and running a query
Before running a query, you can set options that enable you to:
• Specify the number of rows of data that you want the query to return. The
Default Value option is the maximum number of rows that the universe
designer specified for queries on the current universe, in the Designer
module.
• Eliminate duplicate rows of data. This feature is useful if you think that the
query will return many rows containing the same data.
• Retrieve no data when you run the query. In this case, the query’s SQL is
generated, but no connection to the database is made. The names of the
objects included in the query appear as column headings in Excel.
This option is useful if you want to build a query when working in offline mode,
then refresh it when you log in online.
To set options, then run the query:
1. Click the Options button in the Query Panel.
The Query Options dialog box appears:
BusinessQuery for Excel’s User Guide 43
2. Click No Duplicate Rows if you want to eliminate duplicate rows of data from
the query result.
3. To obtain a partial result, you can:
Click 10 rows or 20 rows.
Click Other, then type a number in the Other field.
Building a more powerful query
44 BusinessQuery for Excel User’s Guide
4. Click Do Not Retrieve Data if you do not want the query to connect to the
database when you run it.
When you refresh the query, this option will be automatically switched off,
meaning that the query will connect to the database and the data will appear
in the worksheet. For information on refreshing queries, refer to page 56.
5. Click Delete Trailing Blanks if you do not want trailing blanks to appear in the
query result.
Trailing blanks are spaces that appear at the end of rows of data. They can
occur, for example, if the database has been set up with a fixed number of
characters per row. This option ensures that trailing blanks do not appear in
the query result.
6. Click OK to return to the Query Panel, then click Run.
Building Queries
Working with universes
BusinessQuery allows you to perform certain operations on the universes on
which you build queries. These operations include the following:
• Importing universes from the repository to your computer.
• Refreshing universes to reflect any changes which the designer may have
made in the D
• Viewing, editing and purging lists of values.
• Creating, editing, or deleting user objects.
This section describes how to import and refresh universes. For information on
working with lists of values and user objects, refer to “Ways of managing and
customizing universes” in the online help.
To import and/or refresh a universe:
1. Select the Universes command on the BusinessQuery menu.
The Universes dialog box appears:
ESIGNER module.
BusinessQuery for Excel’s User Guide 45
2. In the Universes list, click the universe you want to import or refresh.
Note that if the repository is holding a more recent copy of a universe you
have already imported, To Be Refreshed is displayed in the Status column.
Working with universes
46 BusinessQuery for Excel User’s Guide
3. Click Import.
The universe’s corresponding .unv file is copied to the Universes folder, which
is found under the Business Objects 6 folder in the Application Data\Business
Objects folder on your computer.
NOTE
The Refresh button does not refresh universes. It updates the information
displayed in the Universes dialog box.
Building Queries
Managing Queries in the Workbook
chapter
48 BusinessQuery for Excel User’s Guide
Overview
This chapter presents topics on managing queries. It includes information on:
• Inserting queries in worksheets.
• Renaming, duplicating, deleting and viewing the properties of query files.
A query file stores the definition of a query and the data it returns.
• Editing queries.
• Refreshing queries.
• Working with the QueryDirector.
The QueryDirector provides information on the queries in the workbook, and
lets you specify how to update them.
Managing Queries in the Workbook
Inserting an existing query
When you build a query, BusinessQuery stores its definition and the data it
retrieves in a query file. The file takes the name that was entered when the query
was built.
When you insert an existing query, the data from its corresponding query file is
placed in the worksheet. You can also request that the query be edited or
refreshed before the data appears.
Before inserting a query, be sure that:
• You have selected an empty cell or range of cells in which data can be placed.
If you select a cell containing data, the Insert Query command is dimmed.
• You have specified a suitable SmartSpace strategy. This is especially
important when the worksheet contains other queries or data from other data.
By controlling the way data is inserted, SmartSpace ensures the integrity of
the data in the worksheet.
For information on SmartSpace, refer to Using SmartSpace on page 95.
To insert an existing query:
1. Click a blank cell in the worksheet where you want the data to appear
2. Select the Insert Query command or click the Insert Query button on the
toolbar.
Insert Query
3. BusinessQuery displays the Insert Query dialog box which lists the query files
currently stored in the MyBQY folder. By default, this is the folder that stores
all query files. However, you can choose another folder from the File
BusinessQuery for Excel’s User Guide 49
Inserting an existing query
50 BusinessQuery for Excel User’s Guide
Locations tab of the Options dialog box.
In the dialog box above, there are queries called Best Customers, Revenue per Customer
from 1995, and Customers and Services. They were built on a universe called Island
Resorts Marketing stored in the local repository.
4. Click the query you wish to insert.
- To select a query that is not stored locally, click the Browse button, and use
the browser to indicate its location.
- Provided that the supervisor has upgraded the corresponding universes,
you can also insert queries built with BusinessQuery 5.1. These queries are
stored in the Storage folder as files with the .req extension.
5. In the Action group box, specify how you want the query to be inserted, then
click OK.
The actions are described in the following table.
Managing Queries in the Workbook
BusinessQuery for Excel’s User Guide 51
Action Description
Insert the query as it is Inserts the data from the query’s corresponding .bqy
file.
Refresh the query, then
insert the results
Edit the query, then insert
the results
Refreshes the data by establishing a connection to
the database, then inserts the results.
Displays the Query Panel, thus enabling you to edit
the query. For example, you can add or remove
objects.
When you run the query, the modified results appear
in the worksheet.
Inserting an existing query
52 BusinessQuery for Excel User’s Guide
Manipulating query files
The Insert Query dialog box lets you duplicate, delete, rename and view the
properties of query files.
Renaming or deleting a query file affects all workbooks containing its
associated query. Check that the query is not used in any of your
Warning
Duplicating a query file
workbooks before you rename or delete the file.
Here are the steps for duplicating a query file:
1. Click the query file to be duplicated within the Insert Query dialog box.
2. Click the right mouse button.
A pop-up menu appears.
3. Click Duplicate from the pop-up menu.
- The query file appears in the dialog box. It is automatically named Copy of
followed by the name of the query file you clicked in step 1.
- You can rename the new query file. To do so, refer to Renaming a query file
on page 53.
Deleting a query file
Here are the steps for deleting a query file from the file system:
1. Click the query file within the Insert Query dialog box.
2. Click the right mouse button.
A pop-up menu appears.
3. Click Delete from the pop-up menu.
The query file disappears from the dialog box.
You can delete a query file only if you have been granted the privilege by the
supervisor.
Managing Queries in the Workbook
Renaming a query file
To rename a query file:
1. Click the query file to be renamed within the Insert Query dialog box.
2. Click the right mouse button.
A pop-up menu appears.
3. Click Rename from the pop-up menu.
4. Enter a new name for the query file in its name label.
The query file is displayed with its new name.
You can rename a query file only if you have been granted the privilege by the
supervisor.
Viewing the properties of a query file
The properties of a query file are:
• The universe on which it was built, and the repository domain on which the
universe resides.
• The query’s name and description.
• The name of the user who created the query.
• The date and time the query was created.
• The date and time the query was last refreshed.
BusinessQuery for Excel’s User Guide 53
Manipulating query files
54 BusinessQuery for Excel User’s Guide
To view the properties of a query file, click the query in the Insert Query dialog
box, then click the Properties button. The Query Properties dialog box appears:
NOTE
You can also view the properties of a query file in the Insert Query dialog box by
selecting a query, right-click the selected query and select Properties...
To change the description of the query file, type over the text in the Query
Description box. The description provides more detailed information on the query
for you and other users.
You can also view the properties of a query file from the QueryDirector. Simply
Properties
click the query, then click the Properties button in the dialog box.
Note that you cannot change the description of the query file when you view its
properties from the QueryDirector.
For more information on the QueryDirector, refer to Working with the
QueryDirector on page 57.
Managing Queries in the Workbook
Editing queries
Once you have inserted a query in BusinessQuery, you may decide to edit it. For
example, you may wish to add and/or remove objects, conditions, sorts, and so
on.
When you edit a query, the number of rows and columns returned may change.
However, no data in the worksheet is lost thanks to SmartSpace. This is because
SmartSpace controls the way data is inserted, thereby ensuring the integrity of
your data. For more information on SmartSpace, refer to Using SmartSpace on
page 95.
To edit a query:
1. Specify the query to be edited by clicking any one of its cells in the workbook.
2. Select the Edit Query command, or click the Edit Query button on the
Edit Query
toolbar.
The Query Panel is displayed.
3. In the Query Panel, edit the query as needed.
For details on editing queries refer to "Building a more powerful query" on page
39. You can also consult “Building More Advanced Queries” in the online help.
BusinessQuery for Excel’s User Guide 55
4. Once you have finished making all changes, click Run.
The Query Panel closes, and the workbook reflects the new data resulting
from the modified query.
Editing queries
56 BusinessQuery for Excel User’s Guide
Refreshing queries
When you refresh a query, BusinessQuery establishes a connection to the
database, retrieves the new set of data, and inserts it in the worksheet. By
refreshing queries on a regular basis, you can be sure that the data in your
workbook remains up-to-date.
When you refresh a query, the number of rows returned may change. However,
no data in the worksheet is lost thanks to SmartSpace. This is because
SmartSpace controls the way data is inserted, thereby ensuring the integrity of
your data. For more information on SmartSpace, refer to Using SmartSpace on
page 95.
BusinessQuery provides two commands with which you can refresh the data of
your worksheet periodically: Refresh Query and Refresh All Queries. These
commands are described in the next sections.
Refreshing a specific query
To refresh a specific query, select the cell or cell range containing the data it
returned. Then select the Refresh Query command.
An alternative to selecting the command from the menu is to click the button on
Refresh Query
the toolbar.
Refreshing all queries
To refresh all the queries in your workbook, select the Refresh All Queries
command.
Refresh All
Queries
Managing Queries in the Workbook
An alternative to selecting the command from the menu is to click the button on
the toolbar.
The queries are refreshed in the order in which they appear in the Update tab of
the QueryDirector. For more information on the Update tab, refer to Specifying
how queries in the workbook are updated on page 61.
Working with the QueryDirector
The QueryDirector feature of BusinessQuery helps you manage the queries you
insert in your workbook.
To display the QueryDirector, select the QueryDirector command or click the
corresponding button on the toolbar.
You cannot use the QueryDirector unless you have inserted at least one query in
QueryDirector
Viewing the queries in the workbook
your workbook.
The QueryDirector is made up of three tabs called Workbook, Update, and
Output:
• In the Workbook tab, you get an overview of the queries in the workbook. You
can consult query properties, as well as refresh and remove queries. From
this tab, you can also change a query's SmartSpace strategy.
• The Update tab, you select actions for updating the workbook, such as
refreshing queries.
• In the Output tab, information on the execution of queries is provided, such as
when a query was last refreshed, how long the execution took, and so on.
The Workbook tab displays all the queries you inserted in your workbook. You
can view these queries with one of two options. The options appear as radio
buttons called View by Sheet and View by Universe.
BusinessQuery for Excel’s User Guide 57
View by Sheet
With View by Sheet, QueryDirector lists all the queries in your workbook by
worksheet. This means that when you click the plus sign (+) beside the name of
a worksheet you can see all the queries it contains.
In this view, QueryDirector identifies each query with the following information:
• A number indicating the order in which the query was inserted.
• The name of the query file from which the query was inserted.
Working with the QueryDirector
58 BusinessQuery for Excel User’s Guide
• The name of the universe on which the query is based.
• The location of the query: local, or the name of the repository on a remote
server.
worksheet name
queries
Read and insert a query
Edit a query in the workbook
Refresh a query in the workbook
Remove a query from the workboo
View the properties of a query in
the workbook
Options for viewing the queries
inserted in your workbook
For example, in the previous illustration, the second query inserted in Sheet1 was
obtained from a query file called Monthly. The query file was created from a
universe called BEACH. The universe is stored locally in the Universe folder.
When you click any query listed in the QueryDirector, BusinessQuery highlights
the corresponding cells in the workbook.
Managing Queries in the Workbook
BusinessQuery for Excel’s User Guide 59
View by Universe
If you select View by Universe, QueryDirector displays a hierarchical view of the
repository, universes, query files, and queries inserted in your workbook:
repository
universe
query file
query
Thus, when you click the plus sign (+) beside the name of a universe or query file,
you can see all the related queries that you inserted in the workbook.
QueryDirector identifies each query in the way described in the previous section.
When you click any query listed in the QueryDirector, BusinessQuery highlights
the corresponding cells in the workbook.
Manipulating queries in the workbook
The Workbook tab of the QueryDirector provides toolbar buttons which enable
you to manipulate the queries in the current workbook. You can:
• Read and insert the data from a query’s corresponding .bqy file.
This task is useful when the data displayed in the workbook may be out-ofdate with the data in the .bqy file, for example when another user has sent you
a new copy of the file.
• Refresh queries, which ensures that the data in the workbook is up-to-date
with the data in the database.
Read and insert a query
Edit a query in the workbook
Refresh a query in the workbook
Remove a query from the workbook
View the properties of a query in
the workbook
Options for viewing the queries
inserted in your workbook
Working with the QueryDirector
60 BusinessQuery for Excel User’s Guide
• Edit queries, for example to remove objects, apply sorts or conditions, and so
on.
• Remove queries.
BusinessQuery lets you choose whether to remove the query completely, or
to remove only the link between the query in the workbook and its .bqy file.
The latter option renders the data static in Excel.
• View query properties, such as information on the way the query is inserted
in the workbook, and information on the query’s .bqy file.
To perform any of the above tasks:
1. Click the QueryDirector button on the BusinessQuery toolbar.
QueryDirector
2. In the Workbook tab, click the query concerned.
The data returned by the query is highlighted in the workbook.
3. Click the button for the task you want.
The buttons are illustrated on page 57.
NOTE
From the QueryDirector, you can also change the SmartSpace strategy and the
display settings for a specific query in the workbook. For more information, refer
to:
• Changing the SmartSpace strategy of an inserted query on page 100.
• Changing the display settings of an inserted query on page 93.
Renaming a query in the workbook
When you insert or build a query, BusinessQuery names it according to the
following syntax:
QueryNumber.QueryFileName.UniverseName.Repository
For example, 1.Sales95.Beach.Marketing is the first query to have been
inserted in the workbook. Its query file is Sales95 - this is the name the user
provided when he or she built the query. The query was built on the Beach
universe, which resides on the Marketing repository.
QueryDirector
You can rename queries in the workbook in the following way:
1. Click the query’s name a second time.
2. Type over the highlighted text, then press the Enter key.
Using Excel commands to manipulate query data
You can manipulate the cells of the queries in the workbook in the same way as
any other data cells in an Excel workbook.
Managing Queries in the Workbook
To perform certain actions on a cell, or a range of cells, first select the cells and
then click the right mouse button. These actions are listed below:
• Clipboard operations: Cut, Copy, Paste
• Insert
• Delete
• Clear Contents
• Format Cells
• Pick from list.
For more information on these actions, refer to your Excel documentation.
Specifying how queries in the workbook are updated
The Update tab of the QueryDirector displays the actions and the order by which
BusinessQuery updates queries in the workbook.
When you select the Update Workbook command, or click the corresponding
Update
Workbook
button on the toolbar, BusinessQuery updates the queries based on the
information specified in this table.
BusinessQuery for Excel’s User Guide 61
List of queries
Moves a query up the list
Moves a query down the list
Modifies the update action assigned
to a query
Refreshes all the queries
in the workbook according to the information
set in the tab
Working with the QueryDirector
62 BusinessQuery for Excel User’s Guide
The update order is based on the position of the queries in the list. By default, the
queries are listed sequentially according to the way in which you inserted them.
Thus, the first item in the list is the first query inserted, the second item is the
second query inserted, and so on.
The update action of a query is represented by a symbol, which appears to the
left of it. The symbols and their meaning are given below:
Symbol Action
Modifying the update order of queries
To move a query up or down the list:
1. Click the query in the Update tab of the QueryDirector.
2. Click the up or down button to move the query up or down the list, as needed.
Refreshes a query by reading the data in the database. This
action sets a connection to the database.
Reads and inserts a query by reading the data contained in
the associated .bqy file.
Has no effect on the query (for example, the data remains
unchanged).
• The up and down arrows are shown below:
Modifying a query’s update action
To modify the update action assigned to a query:
1. Click the query in the Update tab of the QueryDirector.
2. Click the arrow button one or more times until you obtain the symbol for the
Managing Queries in the Workbook
Down
Up
• The query appears in its new position in the list.
BusinessQuery updates the workbook based on the information set in the Update
tab of the QueryDirector.
Update
Workbook
You can update the workbook by clicking the Update Workbook button in the
QueryDirector. Alternatively, you can select the Update Workbook command on
the BusinessQuery menu.
You can refresh all the queries in a workbook by selecting the Refresh All Queries
command on the BusinessQuery menu. This command does not override the update
actions specified in the Update tab of the QueryDirector. Thus, the next time you
select the Update Workbook command, your queries’ update actions are carried out.
The Output tab
From the Output tab of the QueryDirector, you can view all the messages output
by BusinessQuery during your current work session.
You can save output messages to a log file. For more information, refer to
Customizing application settings on page 90.
BusinessQuery for Excel’s User Guide 63
desired action. Alternatively, double-click the query one or more times.
The arrow button looks like this:
Updating the workbook
TIP
Working with the QueryDirector
64 BusinessQuery for Excel User’s Guide
Managing Queries in the Workbook
Sending and Retrieving Queries and Other Documents
chapter
66 BusinessQuery for Excel User’s Guide
Overview
BusinessQuery makes it easy for you to share your business information with
other people in your company or organization. You can send the queries you
build with BusinessQuery to individual users, or publish them for groups of users
across your enterprise.
Your BusinessQuery data is not restricted to BusinessQuery users. Knowledge
workers using BusinessObjects and <smalcap>WebIntelligence can also
retrieve the BusinessQuery files that you send or publish.
NOTE
To send and retrieve queries, you must be working online. For information on
online mode, refer to “Online/Offline modes” on page 25.
BusinessQuery also provides a workbook packing feature, which enables you to
distribute workbooks containing BusinessQuery data to other people via email or
a network drive.
New send and retrieve features
This version of BusinessQuery provides new send and retrieve features to help
you distribute information across your enterprise.
Corporate categories for publishing and retrieving documents
When retrieving a document or selecting categories (publishing), the list of
documents and categories will appear in a hierarchical category tree.
functionality gives greater control and security when distributing and creating your
documents because each category represents a restricted distribution list.
Finding documents on the repository
When you’re working with large numbers of queries, it’s not always easy to find
the ones you want. BusinessQuery provides a Find feature, which enables you
to search for queries by different criteria, such as, creation date.
Sending and Retrieving Queries and Other Documents
This
Using Corporate Categories
As with previous versions of BusinesObjects, you can assign categories to your
Corporate documents. The latest release of BusinessObjects also enables you to
use corporate categories. This functionality gives greater control and security
when distributing and creating your documents because each category
represents a restricted distribution list. Only users with access to the categories
you select can view the documents.
If you have access to a Business Objects server or INFOVIEW and have the rights to
send to users, receive from users and publish to corporate documents, you can
use the corporate categories feature.
You can manage categories if you are have supervisor rights or are owner of the
category. Supervisors can give you ownership via BusinessObjects Supervisor. For
more information on getting ownership of the categories, please see the
Supervisor
If you have access to the Send To and Publish To commands in the File menu,
the following section explains how to use and assign and manage hierachical
categories to your BusinessObjects document.
Assigning corporate categories to your document
You can assign corporate categories to a BusinessObjects document that is already
open before sending it to users. You can also assign the same categories to
several documents at once by adding documents to the list of documents to send
in the Send dialog box.
User’s Guide.
BusinessQuery for Excel User’s Guide 67
Using Corporate Categories
68 BusinessQuery for Excel User’s Guide
To assign corporate categories to an open document:
1. Click Send To Users in the File menu.
2. Select the Domain for the document.
3. Select the Users to send to by clicking the To... button and clicking the name
of the person in the list.
4. Click the Select button to select the corporate categories you want to assign
to your document.
Sending and Retrieving Queries and Other Documents
BusinessQuery for Excel User’s Guide 69
The Select categories dialog box appears.
5. Click the check box next to the category you want to assign to your document.
You can check as many categories and sub-categories you want. You can
also assign one sub-category.
A message indicates how many categories you select at the bottom of the
pane.
6. Click OK to save your selection and return to the Send dialog box.
NOTE
If you do not see all the categories you need, it is possible that the list has not
been updated. The most up to date category list is held in the .lsi file. For better
performance, a local cache provides the category list until you click the Refresh
Categories button.
Using Corporate Categories
70 BusinessQuery for Excel User’s Guide
Managing corporate categories
If you have the rights assigned to you by the BusinessObjects supervisor, you
can add, delete, edit and determine ownership rights for corporate categories.
Creating corporate categories
To create a new category:
1. Click the Manage button in the Select categories dialog box.
The Categories dialog box appears.
2. Click Add to create a new category below the selected category.
3. Type the name for the new category.
4. Press Enter on your keyboard.
TIP
To create a new category on the highest level, click Home Category and then click
New. Notice that only the New button displays when you select the Home Category.
This is because you cannot delete or modify the Home Category.
Sending and Retrieving Queries and Other Documents
BusinessQuery for Excel User’s Guide 71
When you select one of the categories the Selection box provides details on the
category such as:
• Location in the category tree
• User name of the owner of the category
• When it was last updated
• How many documents the category contains
• How many sub-categories the selected category contains
Determining ownership rights for new categories
BusinessObjects allows you to manage the ownership of the categories you
create. For example, you are responsible for creating the category but you want
to maintain the same ownership rights as the categories above, you need to
follow the steps below.
1. Click the category you created below the ownership you want to reproduce.
2. Click the Reproduce Owner button.
3. Click the category you propagated to see the differences.
Editing corporate categories
You can change the names of categories that appear in the category list. There
are three ways to edit corporate categories.
• Select the category and click the Edit button in the Categories dialog box.
• Right click the category and select Edit from the menu.
• Click once to select the category and click again to activate the text box.
Here is one way to change a corporate category:
1. Click the Manage button from the Select categories dialog box.
2. Click the category you want to change.
3. Click the Edit button.
The text box becomes active.
4. Enter the changes to the category name.
Click OK.
5. The category list updates.
TIP
If you do not want to save your changes, simply click the Cancel button before you
click OK.
Using Corporate Categories
72 BusinessQuery for Excel User’s Guide
Deleting corporate categories
You can delete the categories that no longer apply. There are two ways to delete
corporate categories.
• Select the category and click the Delete button in the Categories dialog box.
• Right click the category and select Delete from the menu.
To delete a category:
1. Click the Manage button from the Select categories dialog box.
2. Click the category you want to delete.
3. Click the Delete button.
The category disappears from the list.
4. Click OK to confirm.
TIP
If you do not want to save your changes, simply click the Cancel button before you
click OK.
Sending and Retrieving Queries and Other Documents
Sending and publishing queries
In BusinessQuery there are two methods for sending queries to users:
• If you are working in an environment in which a supervisor has set up a
repository, you can use the Send To command on the BusinessQuery menu.
This command lets you specify the document domain that is to store the query
files as well as the users who are to receive them. This is the method
described in this guide.
• You can also make queries available to users by moving the corresponding
.bqy files through the file system. However, this method does not afford the
same degree of security as the first method; for example, such files can be
deleted or opened by unauthorized users.
Send to Users or Send to Corporate Documents?
To send queries to users, you use the Send To command on the BusinessQuery
menu. When you select this command, a submenu appears. The submenu
comprises two further commands: Users and Corporate Documents. The
following table helps you to choose the right command for your needs.
Choose… If you want to…
Send To>Users Send BusinessQuery data to individual users or
groups of users.
Send To>
Corporate
Documents
Publish BusinessQuery data to groups of users
across your company or organization.
The queries that you send using this command remain
in the repository until the BusinessObjects supervisor
removes them.
BusinessQuery for Excel User’s Guide 73
Sending and publishing queries
74 BusinessQuery for Excel User’s Guide
f
y
r
Sending queries to users
To send queries:
1. Select the Send To>Users command on the BusinessQuery menu.
The Send BusinessQuery Files dialog box appears:
2. Click To...
The document domain o
the repository is set up b
your BusinessObjects
supervisor. It can contain
BusinessObjects
documents and queries,
If more than one domain
is available, and if you do
not know which one to
select, contact you
supervisor. By default,
one domain, called
Document, is available.
Sending and Retrieving Queries and Other Documents
BusinessQuery for Excel User’s Guide 75
The Select Users and Groups dialog box appears:
3. In the Select Users and Groups dialog box, select the users and groups who
are to receive the queries.
To select a series of users or groups, hold down the Shift key, then click the
first and last one. Otherwise, click each one while holding down the Ctrl key.
4. Click Add, then click OK to close the Select Users and Groups dialog box.
You go back to the Send BusinessQuery Files dialog box. The groups and
users you selected now appear in the Send To box:
5. Click Browse... to select the queries that you want to send.
The Select the File to Send dialog box appears.
Sending and publishing queries
76 BusinessQuery for Excel User’s Guide
6. Select the queries, then click Add.
- The Select the File to Send dialog box closes.
- All the queries you selected are now listed in the Send BusinessQuery Files
dialog box.
NOTE
To remove a query, click it and then click Remove.
7. To assign categories to the queries you are sending, click Categories...
The Select Categories dialog box appears:
8. Select the categories you want, then click OK.
Sending and Retrieving Queries and Other Documents
9. Click OK to send the queries.
BusinessQuery displays a message informing you that the operation was
successful:
Publishing queries
Publishing queries means making your BusinessQuery data available to groups
of users across your company or organization.
The queries that you publish remain in the repository until the BusinessObjects
supervisor removes them.
To publish queries:
1. Select the Send To>Corporate Documents command on the BusinessQuery
menu.
The Send BusinessQuery Files dialog box appears.
BusinessQuery for Excel User’s Guide 77
Sending and publishing queries
78 BusinessQuery for Excel User’s Guide
2. To select the groups of users for whom you want to publish queries, Click
To...
The Select Groups dialog box appears:
3. Select the groups that you want, then click Add.
The groups you selected now appear in the Document Recipients list.
4. Click OK in the Select Groups dialog box.
You return to the Send BusinessQuery Files dialog box.
5. Click Browse to select the queries that you want to send.
The Select the File to Send dialog box appears.
NOTE
If you select a document that already has categories assigned to it, the xx dialog
box appears asking you to either append those specific categories to the list or
to ignore them and keep the list as is.
Sending and Retrieving Queries and Other Documents
BusinessQuery for Excel User’s Guide 79
6. Select the queries, then click Add.
- The Select the File to Send dialog box closes.
- All the queries you selected appear in the Send BusinessQuery Files dialog
box.
To remove a query, click it and then click Remove.
7. To assign categories to the queries you are sending, click Categories...
The Select Categories dialog box appears:
The categories will appear as a hierarchical check box tree.
8. Select the categories you want, then click OK.
Categories will appear as a list in the Send BusinessQuery Files dialog box.
Sending and publishing queries
80 BusinessQuery for Excel User’s Guide
9. Click OK to send the queries.
BusinessQuery displays a message informing you that the operation was
successful:
Sending and Retrieving Queries and Other Documents
Retrieving queries
When other users send you queries, you must retrieve them from the repository
before inserting them in your workbooks.
To retrieve queries, you use the Retrieve From command on the BusinessQuery
menu. When you select this command, a submenu appears. The submenu
comprises two further commands:
• The Retrieve From>Users command enables you to retrieve queries that
were sent to you individually.
In this case, the queries you retrieve are removed from the repository.
• The Retrieve from>Corporate Documents command enables you to retrieve
queries that were sent to a group of users to which you belong.
In this case, the queries you retrieve remain on the repository until they are
removed by the BusinessObjects supervisor.
The steps below describe how to retrieve queries by using the Retrieve
From>Users command. If you want to retrieve queries that were sent to a group
of users, follow the steps but use the Retrieve From>Corporate Documents
command.
To retrieve queries:
1. Select the Retrieve From>Users command on the BusinessQuery menu.
The Retrieve BusinessQuery Files dialog box appears:
BusinessQuery for Excel User’s Guide 81
Retrieving queries
82 BusinessQuery for Excel User’s Guide
- A hierarchical category tree appears with the first level open; no queries are
displayed in the root category. Please note that the tree cannot be edited.
- “Uncategorized” categories are displayed with a icon. These categories
appear at the bottom of the root category list.
2. Select the queries that you want to retrieve.
To select a series of queries, expand or collapse the tree. Hold down the Shift
key, then click the first and last query. Otherwise, click each query while
holding down the Ctrl key.
3. Click Retrieve.
BusinessQuery retrieves the queries and displays a confirmation message:
4. Click OK in the Import results box.
- The Retrieve BusinessQuery Files dialog box closes.
- You can now insert the data retrieved by the queries using the Insert Query
command. For information on working with existing queries, refer to page 49.
Refreshing Categories
To refresh the list of queries, click an item in the category tree.
Click the Refresh Categories button to automatically update the category tree
and list of queries in the repository.
A message appears in the message bar during the category refresh indicating
that the operation is in progress.
NOTE
The Refresh Categories button is grayed by default. To use this button, you need
to request the appropriate security permissions.
Sending and Retrieving Queries and Other Documents
BusinessQuery for Excel User’s Guide 83
Finding Documents
To search for a document, you must first display the “Find Documents” panel by
clicking on the “Find>>” button in the dialog box. The “Find Documents” section
will then appear and the “Find>>” button will be become a “Browse Categories<<“
button.
Corporate categories, as well as the Refresh Categories button are hidden during
search mode.
Where does BusinessQuery store retrieved query files?
By default, BusinessQuery stores query files that you retrieve in the following
folder:
C:\Documents and Settings\user\My Documents\BusinessObjects
Documents\MyBQY
TIP
You can change the default setting by using the Options command on the
BusinessQuery menu. For more information, refer to Options for file locations on
page 94.
When you retrieve query files, you can choose a different folder in the following
way:
1. In the Retrieve BusinessQuery Files dialog box, select the query files that you
want to retrieve.
Retrieving queries
84 BusinessQuery for Excel User’s Guide
2. Click your right-mouse button.
A pop-up menu appears:
3. Select the Retrieve Into command on the pop-up menu.
The Retrieve Into dialog box appears:
4. Browse to the folder in which you want to store the queries, then click OK.
- BusinessQuery now retrieves the queries into the folder that you specified.
- Remember to browse to this folder when you insert the queries in a
workbook using the Insert Query command.
Sending and Retrieving Queries and Other Documents
Packing and unpacking workbooks
Packing workbooks enables you to distribute them to other people via email or a
network drive, without using the repository. This feature, available with Microsoft
Excel 2000 or higher, puts all the workbook's query files inside the workbook,
meaning that you can distribute one workbook, containing any number of
queries, as one file.
A workbook can contain several insertions of the same query. However, when
you pack such a workbook, only one instance of the query file is included, thus
not increasing the workbook file size unnecessarily.
You can refresh and update packed workbooks, but you cannot use the New
Query, Insert Query, Edit Query and Remove Query commands. This makes
packed workbooks ideal if you simply want is to view and refresh the data in the
workbook. If you do want to use any of the commands listed, you must first
unpack the workbook.
Refreshing or updating a packed workbook writes information to the temporary
folder of your Windows system. BusinessQuery adds the
TempBQY\RepositoryName\UniverseName folders below your temporary folder.
These folders are automatically deleted when you unload BusinessQuery
(Unload command), or when you quit Excel.
BusinessQuery for Excel User’s Guide 85
To pack/unpack a workbook:
1. Select the Pack/Unpack command on the BusinessQuery menu, then select
the appropriate command on the submenu that appears:
- If you selected Pack Workbook, BusinessQuery now packs the workbook
and all you have to do is save the Excel file before distributing it by mail,
diskette, email and so on.
Packing and unpacking workbooks
86 BusinessQuery for Excel User’s Guide
- If you unpack a workbook that contains query files with the same name as
other query files on your computer, BusinessQuery prompts you to overwrite
or rename these with the following dialog box:
2. To avoid overwriting existing query files, click No.
BusinessQuery then prompts you to rename the query file in this dialog box:
3. Type the new name then click OK.
Sending and Retrieving Queries and Other Documents
Customizing BusinessQuery
chapter
88 BusinessQuery for Excel User’s Guide
Overview
This chapter explains how you can customize BusinessQuery settings to suit
your own requirements.
You can find these settings in the Options dialog box which you can obtain by
selecting the Options command. The dialog box is organized into three tabs:
General, File Locations, and Default SmartSpace.
The next sections cover the options in these tabs.
Customizing BusinessQuery
General options
The options in the General tab relate to:
• Application settings
• Security
• Language setting
• Data access
• Display settings.
These options are illustrated below.
BusinessQuery for Excel’s User Guide 89
General options
90 BusinessQuery for Excel User’s Guide
Customizing application settings
Customizing BusinessQuery
BusinessQuery for Excel’s User Guide 91
The options for customizing application settings are summarized below:
Option Description
AutoLoad
BusinessQuery
Automatically runs the Load command on the
BusinessQuery menu when you launch Excel.
You can use this option in combination with the Prompt
User Login option in the Security group box in this tab.
If Prompt User Login is deactivated, and Autoload
BusinessQuery is activated, you automatically run
BusinessQuery without entering your user name and
password, every time you launch Excel.
This option can be made unavailable by your
BusinessObjects supervisor.
Set Interactive
Refresh
By default, this option lets you refresh queries or
update your workbook interactively, for example, if you
are prompted to enter a value when you run a query,
Set Interactive Refresh lets you do just that.
If you deactivate this option, you will not be able to
respond to prompts or choose a SmartSpace strategy
when you use the commands Update Workbook,
Refresh Query, and Refresh All Queries.
BusinessQuery uses the settings from the last time you
refreshed your queries or updated your workbook. This
option is therefore useful if you want to refresh queries
and update workbooks while you are away from your
desk.
Log Output Messages Causes BusinessQuery to write all output messages to
a log file (located by default in c:\BQ6.log). Such
messages can help you keep track of queries that were
inserted, refreshed, or deleted.
In the text box, you can enter a different name (and
path) for the log file provided that you keep the .log
extension. You can also specify another location for
the file by clicking the button to the right of the text box.
General options
92 BusinessQuery for Excel User’s Guide
Setting security options
The options in the Security group box pertain to the way you can access
BusinessQuery.
The default, Prompt User Login, means that the User Identification dialog box
appears when you launch BusinessQuery. To continue, you need to enter the
name and password assigned to you by your supervisor. This option prevents
unauthorized users from running BusinessQuery while you are away from your
computer.
With Use Automatic Login, you can launch BusinessQuery directly without
having to enter user identification. If you select the option, the user identification
of all subsequent work sessions becomes that of the current work session. The
option thus takes effect when you restart BusinessQuery.
Changing language
A drop down list box in the General tab indicates the current language setting:
English, French, German, or any other language currently installed.
The languages available to you are those that were installed with the product. If
the language you wish to use is not listed, contact the person who installed
BusinessQuery at your site. If you are responsible for installing BusinessQuery,
refer to the BusinessObjects Installation Guide for more information.
To switch to another language, click it in the list box, click OK, and then restart
BusinessQuery.
Specifying data access
The options in the Data Access group box apply to the universes that you can
access for building queries.
The Select a Universe option lets you select any universe stored in the repository.
Clicking the option Use the Default Universe activates the list box displaying the
names of available universes. From this list box, you can click the universe you
wish to use as the default. From then on, this universe will be preselected in the
New Query dialog box.
Customizing BusinessQuery
BusinessQuery for Excel’s User Guide 93
Specifying display settings for building and inserting queries
The options in this group box apply when you build new queries or insert existing
queries in worksheets. By default, all the options are activated.
Option Description
Hide Excel Range
Names
Display Column Headers Displays or removes the names of the objects that
Use Default Formatting Applies default BusinessQuery formats when data is
AutoFit Columns When data is inserted, this option automatically
An Excel range name (_bq4.n) identifies each query
in a worksheet. This option prevents these names
from appearing in the Name box, located at the left
end of the Formula bar in Excel.
You can use Excel names in query conditions. For
more information, refer to “Applying a condition with
the Select an Excel Name operand” in the online
help.
appear as column headers whenever you build new
queries or insert existing ones.
inserted in a worksheet. The default formats are:
- Blue background for column headers
- Yellow background for cells containing data
- Standard black cell borders
- Thick black border around each block of data
adjusts the width of columns to accommodate the
data.
Changing the display settings of an inserted query
The options in the Query Insertion group box are also available in the View in
Sheet tab of the Query Properties dialog box. Thus, you can set these options for
a specific query in the workbook. In this case, the options take effect whenever
you refresh or edit the query in question.
General options
94 BusinessQuery for Excel User’s Guide
Options for file locations
The File Locations tab lets you specify where you want to store documents,
universes, and queries. A default location is proposed for each file type.
FileVar Location lets you
specify the path to a text
file in which you declare
variables. For more
information, refer to the
online help on this dialog
box.
To change the location of a file type:
1. Click the file type.
2. Click the Change button.
A browser is displayed.
3. Use the browser to indicate the location.
The new location is displayed in the dialog box.
BusinessObjects file types, their extensions, and the products from which they
are created are listed in the table below:
File Type Extension Created From
Document .rep or .ret BusinessObjects
Universe .unv Designer
Query .bqy BusinessQuery
Query .wqy W
Customizing BusinessQuery
EBINTELLIGENCE
Using SmartSpace
This section is about SmartSpace, a powerful BusinessQuery feature that helps
you protect the data in your workbook. It covers the principles of SmartSpace and
shows you how to specify a SmartSpace strategy.
What is SmartSpace?
SmartSpace is a feature by which BusinessQuery manages the cells in your
workbook whenever you insert, edit, refresh, or remove a query.
For example, when you insert a query, the rows and columns returned may
require cells occupied by an adjacent query. Likewise, when you edit a query, the
rows and columns returned may exceed those currently in the worksheet.
In all such situations, SmartSpace guarantees that no data is lost or corrupted by
controlling the way BusinessQuery inserts data in cells. It thus ensures the
integrity of your data at all times.
Setting a default SmartSpace strategy
Your default SmartSpace strategy will be applied whenever you build, insert, edit,
refresh or remove a query. You set the default strategy in the Default
SmartSpace tab of the Options dialog box, which you display by selecting the
Options command on the BusinessQuery menu:
BusinessQuery for Excel’s User Guide 95
Using SmartSpace
96 BusinessQuery for Excel User’s Guide
Selecting how the strategy is to be applied
When setting the strategy, you first select one of the following options:
Option Description
Prompt the user when data
is inserted
Always use the following
strategy
Causes a prompt to appear each time that
data is to be inserted or removed. This prompt
indicates whether the query has returned
more or less data.
With this option, you can select the most
appropriate strategy for a given situation.
Note that the prompt does not appear if the
changes do not affect the data in the
worksheet. For example, no prompt appears
when you build a query in an empty
worksheet.
Lets you set an automatic strategy. This
means that BusinessQuery applies the same
strategy each time data is inserted or
removed.
When you click this option, the Predefined
Strategy group box becomes active. You can
then select a strategy: Portrait Layout,
Landscape Layout, or Custom. These
strategies are described below.
Selecting the strategy
The Options dialog box contains three options for predefined strategies called
Landscape Layout, Portrait Layout, and Custom.
It also has a graphic and description depicting the optimal use of the selected
predefined strategy.
The online help provides an example for each strategy. To view an example, type
SmartSpace in the Index tab of the Help Topics dialog box. Double-click
“examples”, then double-click the strategy that interests you.
Customizing BusinessQuery
BusinessQuery for Excel’s User Guide 97
Landscape Layout
Use the Landscape Layout strategy if you position your queries and other data
from left to right in the worksheet.
Shows an ideal use of the
selected strategy
When a query returns more data, this strategy:
• Inserts new columns
• Shifts existing rows of data down.
When you remove a query, or when the query returns less data, this strategy:
• Deletes the columns that the query used
• Shifts existing rows of data up.
Using SmartSpace
98 BusinessQuery for Excel User’s Guide
Portrait Layout
Use the Portrait Layout strategy if you position your queries and other data from
top to bottom in a worksheet.
Shows an ideal use of
the selected strategy
When the query returns more data, this strategy:
• Shifts existing columns of data to the right
• Inserts new rows.
When you remove the query, or when the query returns less data, this strategy:
• Shifts columns to the left
• Deletes the rows that the query used.
Customizing BusinessQuery
BusinessQuery for Excel’s User Guide 99
Custom
The Custom option lets you define your own strategy based on the options you
select in the Columns and Rows group boxes; these options are described below.
Shows how cells are
managed when more
data is returned
Shows how cells are
managed when less
data is returned
Overwrite/Clear
This option has the following effect depending on the data returned:
• When more data is returned, it overwrites existing rows or columns.
• When less data is returned, it clears existing rows or columns.
Shift Cells
Shifts the cells of existing data to the right or left, up or down.
Insert/Delete
This option has the following effect depending on the data returned:
• When more data is returned, it inserts new rows or columns.
• When less data is returned, it deletes existing rows or columns.
Copy/Delete
This option has the following effect depending on the data returned:
• When more data is returned, it copies the formulas of existing columns or
rows.
• When less data is returned, it deletes existing rows or columns.
Using SmartSpace
100 BusinessQuery for Excel User’s Guide
TIP
The options you select in the Columns and Rows group boxes need not be the same.
For example, you can select Shift Cells in the Columns group box and Overwrite/
Clear in the Rows group box. This flexibility results in numerous possibilities for
strategies that best suit your need.
Changing the SmartSpace strategy of an inserted query
Your default SmartSpace strategy will be applied whenever you build, insert, edit,
refresh or remove a query, as described in the previous section. However, via the
QueryDirector you can change the SmartSpace strategy of a specific query that
is already present in the workbook. The new settings apply when you edit, refresh
or remove that query and that query only.
To change an inserted query’s SmartSpace strategy:
1. Click the QueryDirector button on the BusinessQuery toolbar.
QueryDirector
Properties
2. Click a query in the Workbook tab of the Query Director.
3. Click the Properties button.
4. Click the View in Sheet tab:
5. Click Change.
The SmartSpace dialog box appears.
Customizing BusinessQuery
Loading...