Business Objects products in this release may contain redistributions of software
licensed from third-party contributors. Some of these individual components may
also be available under alternative licenses. A partial listing of third-party
contributors that have requested or permitted acknowledgments, as well as required
notices, can be found at: http://www.businessobjects.com/thirdparty
Contents
About Web Intelligence7Chapter 1
How Web Intelligence performs business intelligence over the web...........8
How Web Intelligence performs business intelligence offline......................8
Interacting with Web Intelligence reports.....................................................8
Creating and editing Web Intelligence documents....................................10
Accessing Web Intelligence from InfoView13Chapter 2
To log in to InfoView...................................................................................14
To log out of InfoView................................................................................15
Web Intelligence InfoView options.............................................................15
Viewing and printing Web Intelligence reports.......................................9
Drilling on Web Intelligence reports........................................................9
Combining prompts with query filters...................................................66
To change the order of prompts in Query - HTML.....................................66
Get More Help67Appendix A
Index71
6Building queries with Web Intelligence Query - HTML
About Web Intelligence
1
About Web Intelligence
1
How Web Intelligence performs business intelligence over the web
How Web Intelligence performs business
intelligence over the web
Web Intelligence provides business users an easy to use interactive and
flexible user interface for building and analyzing reports on corporate data
over the web, on secured intranets and extranets. The Web Intelligence
software is installed by your administrator on a web server on your corporate
network.
To use Web Intelligence from your local computer, you log into the business
intelligence portal InfoView via your Internet browser. Then, depending on
your security profile, you can interact with the reports in corporate documents
or edit or build your own documents using a Web Intelligence report panel
or query panel.
How Web Intelligence performs business
intelligence offline
Web Intelligence can be used offline as Web Intelligence Rich Client, a
standalone Microsoft Windows application, equivalent to the Java Report
Panel, that you can install on your computer. Web Intelligence Rich Client
lets you continue to work with Web Intelligence (WID) documents when you
are unable to connect to a CMS, when you want to perform calculations
locally rather than on the server, and when you want to work with Web
Intelligence documents without installing a CMS or application server.
Web Intelligence Rich Client can also be used when connected to a CMS.
Interacting with Web Intelligence reports
Depending on your security profile and on how Web Intelligence is deployed
across your organization, you can view, analyze, or enhance and modify the
data displayed on reports.
8Building queries with Web Intelligence Query - HTML
About Web Intelligence
Interacting with Web Intelligence reports
Viewing and printing Web Intelligence reports
Once logged into the business intelligence portal InfoView, you can access
Web Intelligence documents and view reports. Onscreen navigation is made
easy with page-to-page navigation buttons and a document map that allows
you to jump from section to section or report to report.
The same document can provide the information adapted to each user due
to prompts that request each user, who opens the document, to specify the
data they want to return to the reports.
When you print reports, Web Intelligence automatically generates a copy of
reports in Portable Document Format (PDF) format for optimum print quality.
Drilling on Web Intelligence reports
Drilling on Web Intelligence reports enables you to analyze the detailed data
behind the displayed results. You can turn the report you are viewing into a
drillable report or drill on a duplicate of the original report to retain a version
of the results before your drill analysis.
1
Once you have found the information you need, you can save a snapshot
of the drilled report to share the results of your analysis with other Web
Intelligence users, or save the document in Excel or Portable Document
(PDF) format to print or email to other business contacts.
Performing on-report analysis
Viewing Web Intelligence reports in Interactive view format enables you to
enhance reports and fine-tune the data reports contain, to highlight the
information that most interests you on demand.
On-Report Analysis is designed for:
•users who need to build queries and then want to build reports
•report consumers who need to manipulate the reports created by others
With On-Report Analysis you can:
Building queries with Web Intelligence Query - HTML9
About Web Intelligence
1
Creating and editing Web Intelligence documents
•view document metadata to understand the data behind reports and see
how reports are structured and filtered
•filter and sort results
•add new tables and charts
•add formulas and create variables
•format and change the layout of charts and tables
•slice and dice results by adding other data to charts and tables
Note: On-report analysis of Web Intelligence reports in Interactive view
format is only available if your administrator has deployed Web Intelligence
in JSP mode.
Creating and editing Web Intelligence
documents
You can create or edit Web Intelligence documents using several tools:
•
Web Intelligence Query - HTML on page 10
•
Web Intelligence Java Report Panel on page 11
•
Web Intelligence Rich Client on page 11
•
Web Intelligence HTML Report Panel on page 11
Web Intelligence Query - HTML
Designed for users requiring a pure HTML environment to build queries,
Web Intelligence Query – HTML offers the ability to define the data content
of documents on multiple data sources. You can use Query – HTML to create
new documents from scratch or edit the queries in documents created using
any of the other Web Intelligence tools.
Used together with On-Report Analysis, Query – HTML provides a complete
solution for building queries and designing powerful reports in a pure HTML
environment. Once you have run the queries to generate a standard report,
you can leverage Web Intelligence On-Report Analysis features to format
multiple reports, add formulas, and create variables.
Note: Web Intelligence Query – HTML and On-Report Analysis in Interactive
view format are only available, if your administrator has deployed Web
Intelligence in JSP mode.
10Building queries with Web Intelligence Query - HTML
Creating and editing Web Intelligence documents
Web Intelligence Java Report Panel
The Java Report Panel is designed for users who need more flexibility with
designing report layout and defining formulas and variables. A graphical
Formula Editor enables you to build formulas rapidly using drag-and-drop.
Note: The Web Intelligence Java Report Panel is available if your
administrator has deployed Web Intelligence in ASP mode and if your
administrator has deployed Web Intelligence in JSP mode.
Web Intelligence Rich Client
Web Intelligence Rich Client is a locally installed Microsoft Windows
application that lets you work with Web Intelligence (WID) documents that
are stored locally or in a CMS.
When working without a CMS connection you can work on your local machine
with either CMS-secured or unsecured documents.
About Web Intelligence
1
Web Intelligence Rich Client is based on the Web Intelligence Java Report
Panel and provides equivalent document creation, editing, formatting, printing
and saving capabilities.
There are a number of reasons for using Web Intelligence Rich Client to
work with WID documents:
•You want to work with Web Intelligence documents but you are unable
to connect to a CMS (while traveling, for example).
•You want to improve calculation performance: Web Intelligence Rich
Client performs calculations locally, rather than on the server, and local
calculations can perform better than server calculations.
•You want to work with Web Intelligence documents without installing a
CMS or application server.
Web Intelligence HTML Report Panel
Designed for users who need to build basic reports, the HTML Report Panel
provides query and report features in a simple wizard-like interface. Each
Building queries with Web Intelligence Query - HTML11
About Web Intelligence
1
Creating and editing Web Intelligence documents
document is based on a single data source and can contain multiple reports,
displaying different subsets of information.
In addition, the HTML Report Panel is 508 compliant and can be customized
for specialized deployments.
Note: The Web Intelligence HTML Report Panel is only available if your
administrator has deployed Web Intelligence in JSP mode.
12Building queries with Web Intelligence Query - HTML
Accessing Web Intelligence
from InfoView
2
Accessing Web Intelligence from InfoView
2
To log in to InfoView
You access Web Intelligence reports and set global Web Intelligence options
from InfoView, the corporate business intelligence portal.
To log in to InfoView
Before you can use InfoView and Web Intelligence you need the following
information:
•a URL to the InfoView server
•the InfoView server name and port number
•your login and password
•your authentication, which controls the InfoView resources available to
you
Contact your adminstrator for these details if you do not already know them.
Note: By default the InfoView server name and authentification method are
not displayed on the InfoView logon page. You need to supply this information
only if your administrator has made these options visible.
You access Web Intelligence by using your web browser to log into InfoView,
the corporate business intelligence portal. Once you are in InfoView, you
can analyze and enhance Web Intelligence reports.
1. Launch your web browser.
2. Point your browser to the InfoView bookmark or URL.
The InfoView login page appears.
3. If the System box is blank, type the name of the InfoView server followed
by a colon (:), and then type the port number.
4. In the Username box, type your user name.
5. In the Password box, type your password.
6. In the Authentication box, select the authentication provided to you by
your administrator.
7. Click Log On.
The InfoView home page appears.
14Building queries with Web Intelligence Query - HTML
Accessing Web Intelligence from InfoView
To log out of InfoView
To log out of InfoView
When you finish using InfoView or Web Intelligence you need to log out,
instead of simply closing your web browser.
Logging out of InfoView ensures that any preferences you modified during
your InfoView session are saved. It also lets your administrator track how
many users are logged into the system at any given time and thus optimize
InfoView and Web Intelligence performance.
•Click Log Out.
The login page appears. You are logged out of InfoView
Web Intelligence InfoView options
Web Intelligence document creation and viewing
options
2
You can set your Web Intelligence options to determine how you create,
view and interact with documents using Web Intelligence.
You create documents using a query editor to build the query to retrieve the
document data. After the query returns the data to the document, you can
view and interact with the data.
Building queries with Web Intelligence Query - HTML15
Accessing Web Intelligence from InfoView
2
Web Intelligence InfoView options
Advanced
DescriptionDocument creation option
You create documents using the Java
Report Panel, a Java applet than
launches in your Web browser.
The Java Report Panel is a combined
query building, report editing and data analysis environment. If you
choose Advanced as your document creation option, you also use
the Java Report Panel for working
with the data returned by the query.
The View format option is not taken
into account.
The Java Report Panel provides the
richest feature set of all Web Intelligence query building, report editing
and data analysis environments.
Interactive
Desktop
Web Accessibility
You build queries using Query HTML, an HTML-based query editor.
You build queries, edit reports and
analyze data using Web Intelligence
Rich Client, a standalone version of
the Java Report Panel that runs outside your web browser.
You create documents using the
HTML Report Panel, a 508-compliant
query-building and report-viewing
environment.
You can use the following view formats to view and interact with existing
Web Intelligence documents, or documents that you have just created using
a query editor:
16Building queries with Web Intelligence Query - HTML
HTML
Interactive
Accessing Web Intelligence from InfoView
Web Intelligence InfoView options
DescriptionView format
Use HTML format when you want to
open reports, answer prompts, navigate reports, and/or perform drill analysis.
Use Interactive format when you want
to apply filters, sorts, calculations,
modify formatting and data displayed
on tables and charts, and/or perform
drill analysis.
Use Interactive format if you are using
Query – HTML to define queries, and
you want to format reports based on
those queries and add formulas and
variables.
Interactive view format is only available
if your administrator has deployed Web
Intelligence in JSP mode.
2
PDF
Use PDF mode when you want to view
static reports.
To select the Web Intelligence query editor
1. Click the Preferences button on the InfoView toolbar.
2. Click Web Intelligence Preferences to display the Web Intelligence
options.
3. Select the query editor beneath Select a default creation/editing tool.
4. Click OK.
To select the Web Intelligence view format
You can select different view formats for Web Intelligence documents
depending on how you want to interact with the information displayed on the
reports. You select your Web Intelligence view options in InfoView. When
Building queries with Web Intelligence Query - HTML17
Accessing Web Intelligence from InfoView
2
Web Intelligence InfoView options
you modify your view options, the new settings are implemented the next
time you open a Web Intelligence document.
1. Click the Preferences button on the InfoView toolbar.
2. Click Web Intelligence Preferences to display the Web Intelligence
options.
3. In the Select a view format section, select the view format.
To select a default universe for new documents
1. Click Preferences on the Infoview toolbar.
2. ClickWeb Intelligence Preferences to display the Web Intelligence
options.
3. Click Browse beneath Select a default universe and browse to the
universe you want to select as the default.
To set Web Intelligence drill options
Drilling on reports lets you look deeper into data to discover the details behind
a good or bad summary result displayed on a table, chart, or section. Before
you begin a drill session, you can set your drill options in InfoView to specify
how reports will change each time you drill.
1. Click Preferences on the Infoview toolbar.
2. Click Web Intelligence Preferences to display the Web Intelligence
options.
3. Select the drill options under Drill options and Start drill session.
Hide drill toolbar option
When you drill on a value displayed on a report, the Drill toolbar appears
and displays the value on which you drilled. The value displayed on the
toolbar filters the results displayed on the drilled report.
For example, if you drill on year 2001, the results displayed on the drilled
table are Q1, Q2, Q3, and Q4 for year 2001. This means that the quarterly
values you drilled to are filtered by 2001.
18Building queries with Web Intelligence Query - HTML
Accessing Web Intelligence from InfoView
Web Intelligence InfoView options
Note: If the drilled report includes dimensions from multiple queries, a ToolTip
appears when you rest your cursor on the value displayed on the filter. The
ToolTip displays the name of the query and the dimension for the value.
The Drill toolbar allows you to select alternative values on the same level,
in order to filter the results differently. For example, if you use the Drill toolbar
illustrated above to select “2002,” the results displayed on the drilled table
would be Q1, Q2, Q3, and Q4 for year 2002.
You can opt to hide the Drill toolbar when you start drill mode. The Drill
toolbar is only useful if you want to select filters during your drill session.
Prompt when drill requires additional data option
When you drill the results displayed on a Web Intelligence report, you may
want to drill to higher- or lower-level information that isn’t included in the
scope of analysis for the document. When this is the case, Web Intelligence
needs to run a new query to retrieve the additional data from the data source.
Since queries on large selections of data may take a long time to be
completed, you can choose to be prompted with a message every time a
new query is necessary. The prompt message asks you whether you want
to run the additional query or not. In addition, the prompt lets you apply filters
to the extra dimensions you include in the new query. This means you can
restrict the size of the query to only the data necessary for your analysis.
2
You need permission from your administrator to drill out of the scope of
analysis during a drill session.
Synchronize drill on report blocks option
When you select the Sychronize drill on all report blocks option, the display
of all blocks changes to correspond with your drill actions. For example, if
you drill down on a block from year to quarter, and your report also contains
a chart showing data by year, the chart display also changes to display data
by quarter.
If you do not select the option, only the drilled block changes in response to
drill actions.
Building queries with Web Intelligence Query - HTML19
Accessing Web Intelligence from InfoView
2
Web Intelligence InfoView options
Start drill session option
The Start drill session option controls how Web Intelligence behaves when
you start drill mode.
Start drill session on existing report option
When you select Start drill session on existing report, the current report
becomes drillable when you start drill mode. When you end drill mode, the
report displays the drilled values.
Start drill session on a duplicate report option
When you select Start drill on a duplicate report, Web Intelligence creates
a duplicate of the current report when you start drill mode, and you drill on
the duplicate. This allows you to compare the results of the original report
with the results you discover during your drill analysis.
Web Intelligence locale options
The locale determines how a Web Intelligence document displays data. It
affects, for example, the formatting of numbers and the default sort order.
Three locale settings combine to determine how Web Intelligence displays
data:
Product locale
Document locale
20Building queries with Web Intelligence Query - HTML
DescriptionLocale
The locale in which InfoView displays
data by default.
The locale associated with a particular
Web Intelligence document. When you
save a document, the document locale
becomes the current product locale or
the preferred viewing locale.
You can permanently associate a locale with a document. For more information, see To permanently associate
a locale with a document on page 22.
Accessing Web Intelligence from InfoView
Web Intelligence InfoView options
DescriptionLocale
2
Preferred viewing locale
The product locale
The product locale is the locale that InfoView uses by default. You set the
product locale in the Infoview General preferences.
To set the product locale
1. Click Preferences on the main InfoView toolbar.
2. Click General to display the general options.
3. Select the product locale from the Product locale list.
The document locale
The document locale is the locale associated with a particular document. By
default, the document takes the product locale when you save a document,
or the preferred viewing locale if this is different from the product locale and
your settings give the preferred viewing locale priority. The document retains
this locale until the next time it is saved, when it again takes either the product
locale or the preferred viewing locale.
The locale in which you choose to view
documents
You can permanently associate the current document locale with a document.
For more information, see To permanently associate a locale with a document
on page 22.
The GetContentLocale() Web Intelligence function returns the document
locale.
To display data using the document locale
1. Click Web Intelligence Preferences to display the Web Intelligence
options.
2. Click Use the document locale to format the data beneath When
viewing a document.
Building queries with Web Intelligence Query - HTML21
Accessing Web Intelligence from InfoView
2
Web Intelligence InfoView options
The preferred viewing locale
The preferred viewing locale is the locale that you choose to dislay data. The
preferred viewing locale overrides the product locale if it is different from the
product locale and your settings give the preferred viewing locale priority.
To set the preferred viewing locale
1. Click Preferences on the main InfoView toolbar.
2. Click General to display the general options.
3. Select the preferred viewing locale from the Preferred viewing locale
list.
4. Click Web Intelligence Preferences to display the Web Intelligence
options.
5. If you want data to be formatted using the preferred viewing locale, click
Use my Preferred Viewing Locale to format the data beneath When
viewing a document.
To permanently associate a locale with a document
1. In Web Intelligence Interactive, select Document > Properties from the
menu to display the" Document Properties" dialog box.
2. Select Permanent regional formatting.
3. Save the document.
The current document locale is associated permanently with the document
and overrides the product locale and the preferred viewing locale.
22Building queries with Web Intelligence Query - HTML
Building and editing queries
using Query - HTML
3
Building and editing queries using Query - HTML
3
Creating, editing and saving documents
Creating, editing and saving documents
You create Web Intelligence documents by first selecting a universe in
InfoView. Each universe maps to a database containing corporate business
information. When you connect to a universe, Web Intelligence automatically
launches the document editor selected on the Web Intelligence Document
Preferences page in InfoView.
After you have selected a universe, you use the objects in the universe to
build a query to return data from the database to your Web Intelligence
document.
After creating a document you can save it to the InfoView repository.
To select a universe
1. On the InfoView Home page, click the arrow next to New on the top
toolbar.
2. Click Web Intelligence Document.
3. Click the title of the universe on which you want to create a document.
Your selected query editor opens and displays the objects in the universe.
To edit a Web Intelligence document
1. Navigate to the document you want to open on the InfoView home page.
2. Click Actions > Modify.
The document opens in the Web Intelligence document editor you selected
in the InfoView preferences.
To save a document in Query - HTML
1. Click the arrow to the right of the Save document button on the left of
the Query - HTML toolbar.
24Building queries with Web Intelligence Query - HTML
Building and editing queries using Query - HTML
How universe objects map to data
2. Click Save (to resave the document if it has already been saved) or Save
as (to save the document under a new name) from the dropdown list that
appears.
If you clicked Save As or if you are saving the document for the first time,
the "Save As" dialog box appears.
3. Use the "Save As" dialog box to save the document to the InfoView
repository.
How universe objects map to data
Classes and subclasses
Objects are grouped into folders called classes. Each class can also contain
one or more subclasses. Subclasses contain objects that are a further
subcategory of the objects in the upper level of the class.
The role of classes is to organize the objects into logical groups. When you
create queries on the universe, classes help you to find the objects that
represent the information that you want to use in a query.
3
Dimension object
A dimension object represents data that provides the basis for analysis in a
report. Dimension objects typically retrieve character-type data, for example;
customer names, resort names, or dates.
Dimension objects appear as follows in the Web Intelligence query panel:
Detail object
A detail obect provides descriptive data about a dimension. A detail is always
attached to the dimension for which it provides additional information. For
Building queries with Web Intelligence Query - HTML25
Building and editing queries using Query - HTML
3
To set query properties
example, [Age] is a detail object that is associated with the (Customer]
dimension.
Detail objects appear as follows in the Web Intelligence query panel:
Measure object
The measure object retrieves numeric data that is the result of calculations
on data in the database. For example, [Revenue] is the calculation of the
number of items sold multiplied by item price. Measure objects are often
located in a Measures class.
Measure objects appear as follows in the Web Intelligence query panel:
There are two types of measure:
•classic measures - calculated by Web Intelligence
•smart measures - calculated by the database on which the universe is
based
In certain situations, smart measures impact the way in which Web
Intelligence displays calculations. For more information on smart measures,
see the Using Functions, Formulas and Calculations in Web Intelligence
guide.
To set query properties
1. Click Query Properties to display the "Query Properties" dialog box.
2. Set the properties in the dialog box.
26Building queries with Web Intelligence Query - HTML
Building and editing queries using Query - HTML
Web Intelligence query properties
Allow other users to edit all queries property
When selected, other users who have the appropriate editing rights can
access Query View and modify the queries in the document. When cleared,
only the report creator can modify the queries. This option is selected by
default. Unlike the other query properties, which only apply to the selected
query, this option applies to all of the queries in the document.
Max retrieval time query property
Maximum time that a query can run before the query is stopped. This can
be useful when a query is taking too long due to an excess of data, or network
problems. You can set a time limit so a query can stop within a reasonable
time.
To set query properties
3
Max rows retrieved query property
The Max rows retrieved query property determines the maximum number
of rows of data that are displayed when a query is run. If you only need a
certain amount of data, you can set this value to limit the number of rows of
data in your document.
Max rows retrieved does not operate at the database level. If you set Max
rows retrieved to 1000, and your query returns 5000 rows, Web Intelligence
initially retrieves all 5000 rows, before discarding 4000 and retaining only
the first 1000 rows.
The Sample result set query property also applies a restriction on the
number of rows in the query, but at the database level. If you set Max rowsretrieved to 2000 and Sample result set to 1000, the query retrieves a
maximum of 1000 rows only.
This setting can be overridden by the limits set by your administrator in your
security profile. For example, if you set the Max rows retrieved setting to
400 rows, but your security profile limits you to 200 rows, only 200 rows of
data will be retrieved when you run the query.
Building queries with Web Intelligence Query - HTML27
Building and editing queries using Query - HTML
3
To set query properties
Query prompt order
Prompts are listed in the list. You can use the up and down arrows to move
a prompt up or down the list to change the order in which they are executed
Retrieve duplicate rows query property
In a database, the same data may be repeated over many rows. You can
choose to have these repeated rows returned in a query, or to have only
unique rows returned.
Allow other users to edit all data providers query property
When selected, other users who have the appropriate editing rights can
access Query View and modify the data providers in the document. When
cleared, only the report creator can modify the data providers. This option
is selected by default. Unlike the other query properties, which only apply to
the selected query, this option applies to all of the data providers in the
document.
Reset contexts on refresh query property
When selected, you are prompted to choose a context each time a query
requiring a context is run. When unselected, Web Intelligence retains the
context specified the first time you run the query.
Clear contexts query property
When this property is selected, Web Intelligence clears the contexts listed
in the list when you next run the query or refresh the data.
Sample result set query property
The Sample result set property determines the maximum number of rows
that a query returns. The property applies this restriction at the database
28Building queries with Web Intelligence Query - HTML
Building and editing queries using Query - HTML
To set query properties
level. If the database supports sampling, Web Intelligence adds the restriction
to the SQL that Web Intelligence generates to return the data.
If you select the Fixed option, Web Intelligence uses fixed sampling. At each
data refresh, the query returns the same rows. If you do not set the Fixed
option, Web Intelligence uses random sampling. At each data refresh, the
query returns a different set of sampled rows.
Sample result set is more efficient than the Max rows retrieved property,
which discards rows beyond the maximum limit only after retrieving all the
rows in the query.
Not all databases support fixed and random sampling. If they are not
supported, the properties are disabled (in the Java Report Panel) or invisible
(in Query - HTML). Sampling is not available at all in the HTML Report Panel.
3
Building queries with Web Intelligence Query - HTML29
Building and editing queries using Query - HTML
To set query properties
3
30Building queries with Web Intelligence Query - HTML
Working with queries
4
Working with queries
4
To build and run a query in Query- HTML query
To build and run a query in QueryHTML query
You create Web Intelligence documents by first selecting a universe in
InfoView. Each universe maps to a database containing corporate business
information. When you connect to a universe, Web Intelligence automatically
launches the query editor selected in InfoView. For Query - HTML to open,
you must have selected Interactive as your document creation option.
1. Click Document List, then New > Web Intelligence Document in
InfoView.
2. Click the universe on which you want to build the query.
Web Intelligence Query – HTML opens.
3. Select an object in the Universe pane and drag it over to the Result
Objects pane to add an object to the query.
•To add all the objects in a class to the query, drag the class folder to
the Result Objects pane.
•To remove an object from the query, drag it from the from the Result
Objects pane back to the Universe pane.
•To remove all objects from the query, click Remove All at the right of
the Result Objects pane.
4. Select an object in the Result Objects pane and drag and drop it to the
left or right, or click Move Left/Move Right to the right of the Result
Objects pane, to change its position in the query.
5. Set the scope of analysis.
6. Define query filters.
7. Set query properties.
8. Click Run.
To rename a query in Query - HTML
1. Right-click the tab showing the name of the query you want to rename.
2. ClickRename on the menu.
3. Type the new name, then press Enter.
•The query name cannot have more than 50 characters.
•The query name cannot contain the following character: [
32Building queries with Web Intelligence Query - HTML
Working with queries
Viewing the SQL generated by a query
•Two queries cannot have the same name.
The new name appears on the query tab.
Viewing the SQL generated by a query
When you build a query in Web Intelligence, Web Intelligence generates
SQL to run against the database to return the query result. S(tructured)
Q(uery) L(anguage) is the query language understood by all relational
databases.
You can view and edit the SQL generated by Web Intelligence.
To view and edit the generated SQL
Note: You cannot edit the query SQL when the query contains optional
prompts. Edit the query to remove the optional prompts before attempting
to edit the SQL.
4
1. Click SQL on the query toolbar to display the "SQL Viewer" dialog box.
When SQL is not editable, values supplied in response to prompts appear
directly in the query.
For example, if "UK" was supplied in response to a prompt on [Country],
a line similar to
Resort_country.country In ('UK')
appears in the query.
If no value has yet been supplied for the prompt, the Web Intelligence
syntax for prompts (described below) appears in the query.
2. Click Use custom SQL to make the generated SQL editable.
When you make the SQL editable, the Web Intelligence syntax for prompts
appears in the query.
Building queries with Web Intelligence Query - HTML33
Working with queries
4
To interrupt a query
3. Click Validate after editing the SQL to check that your edits are valid.
4. Click Copy (Java Report Panel and Web Intelligence Rich Client only) to
copy the SQL to the clipboard.
5. Click Print (Query - HTML only) to print the SQL.
To interrupt a query
When you interrupt a query, only partial data is returned to the document.
The values displayed in the document do not accurately reflect the definition
in the query.
1. On the "Waiting - Refresh Data" dialog box, click Cancel.
The "Interrupt Data Retrieval" dialog box appears.
2. Click OK.
To remove a query
1. Select a the query you want to remove by right-clicking the appropriate
Query tab.
2. Click Remove.
Multiple queries
You can include one or multiple queries in a Web Intelligence document.
When you include multiple queries, those queries can be based on a single
universe or on multiple universes available in InfoView.
For example, you can include product sales data and customer data in the
same document. In this case, your corporate data for product line sales is
available on one universe and data on customers is available on another
universe. You want to present product line sales results and information on
customer age groups in the same report. To do this, you create a single
document that includes two queries; one query on each universe. You can
then include and format results from both queries on the same report.
Defining multiple queries in a single document is necessary when the data
you want to include in a document is available on multiple universes, or when
you want to create several differently-focused queries on the same universe.
You can define multiple queries when you build a new document or add more
34Building queries with Web Intelligence Query - HTML
To add a query in Query - HTML
queries to an existing document. You can present the information from all
of the queries on a single report or on multiple reports in the same document.
To add a query in Query - HTML
1. Click Add a Query.
The Add Query window appears.
2. Select the universe you want to use to build the query.
You can create a new query on a universe already used in the document
or select a different universe. By default, the universe used in the current
document is displayed first.
3. Click OK.
4. Define the objects, filters, scope of analysis, and properties you want for
the query.
The data content, scope of analysis, and filters you define here will only
apply to the selected query. The query properties you define only apply
to the selected query.
Working with queries
4
To duplicate a query
If you want to build a different query on a universe already included in the
document, you can duplicate the existing query on that universe and then
modify it, instead of starting from scratch.
1. Select the query you want to duplicate by right-clicking the appropriate
Query tab at the bottom of the report panel.
2. Select Duplicate
Building queries with Web Intelligence Query - HTML35
Working with queries
To duplicate a query
4
36Building queries with Web Intelligence Query - HTML
Setting the scope of
analysis
5
Setting the scope of analysis
5
Scope of analysis
Scope of analysis
The scope of analysis for a query is extra data that you can retrieve from the
database to give more details on the results returned by each of the objects
in a query. This extra data does not appear in the initial result report, but it
remains available in the data cube, so you can pull this data in to the report
to allow you to access more detail at any time. This process of refining the
data to lower levels of detail is called drilling down on an object.
In the universe, the scope of analysis corresponds to the hierarchical levels
below the object selected for a query. For example, a scope of analysis of
one level down for the object Year, would include the object Quarter, which
appears immediately under Year.
You can set this level when you build a query. It allows objects lower down
the hierarchy to be included in the query, without them appearing in the
Results Objects pane. The hierarchies in a universe allow you to choose
your scope of analysis, and correspondingly the level of drill available.
In the Java Report Panel and in Web Intelligence Rich Client, you can also
create a custom scope of analysis by selecting specific dimensions for the
Scope of Analysis pane.
Note: You cannot set the scope of analysis when working in query drill mode
because this drill mode causes Web Intelligence to modify the scope
dynamically in response to drill actions.
Levels of scope of analysis
You can set the following levels for scope of analysis:
DescriptionLevel
Only the objects that appear in the
None
38Building queries with Web Intelligence Query - HTML
Results Objects pane are included in
the query.
Setting the scope of analysis
To set the scope of analysis in Query - HTML
DescriptionLevel
For each object in the Result Objects
•One level down
•Two levels down
•Three levels down
Custom
Note: This option is available in the
Java Report Panel and in Web Intelligence Rich Client only.
Including a scope of analysis in a document increases the document size
significantly. This is because the data necessary for the scope you specify
is saved with the document, even though it is not visible in the reports unless
you start drill mode and drill down to the data to display the corresponding
values.
pane, one, two, or three objects lower
down the hierarchy tree are included
in the query. The data from these objects is stored in the cube until you add
them to the document.
All objects added manually to the
Scope of Analysis panel are included
in the query.
5
In order to minimize the size of documents and optimize performance, we
recommend that you only include a scope of analysis in documents where
you are certain that users will need to drill.
We suggest the following method because it will be easier for you to set the
scope of analysis seeing the hierarchy of the classes and objects.
To set the scope of analysis in Query HTML
1. Click Query Properties in the Query toolbar.
2. Choose: None (the default), One Level, Two Levels, or Three Levels
from the drop-down list.
Building queries with Web Intelligence Query - HTML39
Setting the scope of analysis
To set the scope of analysis in Query - HTML
5
40Building queries with Web Intelligence Query - HTML
Query contexts
6
Query contexts
6
What is an ambiguous query?
What is an ambiguous query?
An ambiguous query is a query that contains one or more objects that can
potentially return two different types of information.
In a universe, certain dimensions may have values that are used for two
different purposes in the database. For example, the [Country] dimension in
the query below can return two types of information:
•Customers and the country in which they spent their vacation.
•Customers and the country for which they have made their reservation.
The role that Country plays in this query is ambiguous. A country can be
either the country where a vacation was sold, or a country where a vacation
is reserved. One is existing information (sales), and the other is future
information (reservations).
To avoid ambiguities in a query, the universe designer identifies the different
ways that objects can be used in the universe, and implements restrictions
on how these objects can be combined. These restrictions are called contexts.
What is a context?
A context is a defined group of objects that share a common business
purpose. This business purpose is usually the type of information that these
related objects represent. For example, a sales context is a grouping of all
the objects that can be used to create sales queries. A reservations context
is a grouping of all the objects that can be used in reservation queries.
Contexts are defined in a universe by the universe designer.
You can combine any object within the same context to create a query. You
can also combine objects in different contexts. If you use an object that is
common to both contexts, Web Intelligence will try to determine the context
that best fits the other objects in the query.
If it cannot determine a context, you are prompted to choose the context that
you want to apply to the query.
42Building queries with Web Intelligence Query - HTML
Query contexts
Choosing a context when you run a query
Choosing a context when you run a query
When you create a query or refresh a report, you may be asked to choose
a context before the query can run. Contexts are set up in a universe to avoid
ambiguous queries.
To choose a context when you run a query
•Click the context in the Select a Context dialog box.
6
Building queries with Web Intelligence Query - HTML43
Query contexts
Choosing a context when you run a query
6
44Building queries with Web Intelligence Query - HTML
Filtering data using query
filters in Query - HTML
7
Filtering data using query filters in Query - HTML
7
Query filters defined
Query filters defined
You limit the data returned to the document by applying filters when you
define the query. Using query filters enables you to secure the data that you
don’t want specific user groups to see and limits the size of the documents
that are stored on your network. When you run the query on the document
data, the Web Intelligence returns only the values that meet the query filter
definitions.
Query filters limit the data Web Intelligence returns to a document. They
retrieve a sub-set of the data from the database and return the corresponding
values to the document. You define filters to match business questions. For
example, you can filter the [Year] dimension to view only sales revenue for
Year 2003; or filter the [Annual Income] dimension to view only customers
whose annual income is equal to or greater than $1.5M.
Query filters allow you to:
•retrieve only the data you need to answer a specific business question
•hide the data you don’t want specific users to see when they access the
document
•minimize the quantity of data returned to the document to optimize
performance
Example: In Q4 2002, which stores in my sales region gained margins
above $130K?
As Regional Marketing Manager for Texas, you are only interested in
analyzing margins for Texas, but the sales universe includes data US-wide.
In addition, you only want to view information for stores where margins
reached over your 4Q 2002 quarterly target figure: $130K. To create a
document with only the information you need, you apply a filter on the
[State], [Year], and [Quarter] dimensions and a filter on the [Margin]
measure:
46Building queries with Web Intelligence Query - HTML
Filtering data using query filters in Query - HTML
Query filters and report filters compared
Year Equal to 2002
Quarter Equal to Q4
AND
State Equal to Texas
Margin Greater than or equal to 130000
To avoid displaying the filtered values Texas, 2002, and Q4 in the table
columns Year, Quarter, and State, you exclude the [Year], [Quarter], and
[State] objects from the Result Objects pane. When you generate the report,
the report values correspond to Texas stores with 4Q 2002 margins greater
than or equal to $130K:
MarginSales RevenueStore name
133,802307,914e-Fashion Houston
7
e-Fashion Houston
Leighton
136,055316,232
Query filters and report filters compared
You can apply filters at two levels within a document:
•query filters – these filters are defined on the query; they limit the data
retrieved from the data source and returned to the Web Intelligence
document
•report filters – these filters limit the values displayed on reports, tables,
charts, sections within the document, but they don’t modify the data that
is retrieved from the data source; they simply hide values at the report
level
Types of query filter
You can create the following types of query filter:
Building queries with Web Intelligence Query - HTML47
Filtering data using query filters in Query - HTML
7
Types of query filter
•predefined filters – created by your administrator
•custom filters – you define on the query
•quick filters - a simplified form of custom filter for simple filters
•prompts – you define these dynamic filters to display a question or a list
of values so you or other users can select different filter value(s) at each
run query
You can different types of filters on a single query.
Predefined query filters
Predefined filters make the specific data you most typically need for reports
permanently available in Web Intelligence. They are created by an
administrator and saved with the universe. Predefined filters often contain
complex expressions that require a detailed knowledge of the database
structure. Including predefined filters on the universe means you don’t need
to create the same custom filters every time you create new Web Intelligence
documents based on the same universe.
As a Web Intelligence user, you cannot view the component parts of
predefined filters or edit predefined filters.
To select a predefined query filter
•Double-click the predefined filter or drag it to the Query Filters pane.
When you run the query, the data corresponding to the query filters you
selected is returned to the report.
Custom query filters
You create custom query filters to limit document data to information
corresponding to:
•a specific business question
•the business information needs of a specific group of users
For example, you can create custom filters to retrieve sales results data for
specific dates, products, or services, or to view customer information only
for customers who are high wage earners or who live in a particular region.
48Building queries with Web Intelligence Query - HTML
Filtering data using query filters in Query - HTML
Query filter and prompt operators
Equal To operator
Use the Equal to operator to obtain data equal to a value.
For example, to return data for the US only, create the filter "County Equal
To US".
Not Equal To operator
Use the Not Equal To operator to obtain data not equal to a value.
For example, to return data for all countries except the US create the filter
"County Not Equal To US".
Different From operator
Types of query filter
7
Use the Different From operator to retrieve data different from a value.
For example, to retrieve data for all quarters execpt Q4, create the filter
[Quarter] Different From "Q4"
Greater Than operator
Use the Greater Than operator to retrieve data greater than a value.
For example, to retrieve data for customers aged over 60, create the filter
"[Customer Age] Greater than 60".
Greater Than Or Equal To operator
Use the Greater Than Or Equal To operator to retrieve data greater than or
equal to a value.
Building queries with Web Intelligence Query - HTML49
Filtering data using query filters in Query - HTML
7
Types of query filter
For example, to retrieve data for revenue starting from $1.5M, create the
filter "[Revenue] Greater than or equal to 1000500".
Less Than operator
Use the Less Than operator to retrieve data lower than a value.
For example, to retrieve data for exam grades lower than 40, create the filter
"[Exam Grade] Less Than 40".
Less Than Or Equal To operator
Use the Less Than Or Equal To operator to retrieve data less than or equal
to a value.
For example, to retrieve data for customers whose age is 30 or less, create
the filter "[Age] Less Than Or Equal To 30".
Between operator
Use the Between operator to retrieve data between and including two values.
For example, to retrieve data for weeks starting at week 25 and finishing at
36 (including week 25 and week 36), create the filter "[Week] Between 25
and 36".
Not Between operator
Use the Not Between operator to retrieve data outside the range of two
values.
For example; to retrieve data for all the weeks of the year, except for and
not including weeks 25 through 36, create the filter "[Week] Not between 25
and 36".
50Building queries with Web Intelligence Query - HTML
In List operator
Use the In List operator to retrieve data corresponding to values in a list of
values.
For example, to retrieve data for the US, UK and Japan only, create the filter
[Country] In List ("US";"UK";"Japan").
Not In List operator
Use the Not In List operator to retrieve data that does not correspond to
multiple values.
For example, if you do not want to retrieve data for the US, UK and Japan,
create the filter [Country] Not In ("US";"UK";"Japan").
Matches Pattern operator
Filtering data using query filters in Query - HTML
Types of query filter
7
Use the Matches Pattern operator to retrieve data that includes a specific
string or part of a string.
For example, to retrieve customers whose date of birth is 1972, create the
filter [DOB] Matches Pattern "72".
Different From Pattern operator
Use the Different From Pattern operator to return data that doesn't include
a specific string.
For example, to retrieve customers whose date of birth is not 1972, create
the filter [DOB] Different From Pattern '72'.
Both operator
Use the Both operator to retrieve data that corresponds to two values.
For example, to retrieve customers who have both a fixed and a mobile
telephone, create the filter [Account Type] Both 'Fixed' And 'Mobile'.
Building queries with Web Intelligence Query - HTML51
Filtering data using query filters in Query - HTML
7
Types of query filter
Except operator
Use the Except operator to retrieve data that corresponds to one value and
excludes another.
For example, to retrieve customers who have a fixed telephone and do not
have a mobile telephone, create the filter [Account Type] 'Fixed' Except
'Mobile'.
The Except operator is more restrictive than Different From or Not In
List. For example, a report that returns customers and that includes the
filter [Lines] Different From 'Accessories' excludes all sales records
where the item sold is part of the 'Accessories' line. If the same customer
has purchased Accessories and non-Accessories items, the customer still
appears in the report, but their spending total includes only non-Accessories
sales.
If the filter is [Lines] Except 'Accessories', only customers who have
bought no accessories are included in the report.
Related Topics
•Not In List operator on page 62
•Different From operator on page 60
Combining query filters
The AND and OR operators
You use the AND and OR operators to combine and nest query filters or
report filters. When you use AND to combine filters, Web Intelligence displays
only the data that matches the criteria in both the filters linked by the operator.
When you use OR, Web Intelligence returns the data that matches the criteria
in either one of the filters linked by the operator.
You can mix the AND and OR operators when combining and nesting filters.
For example, you can link three filters in the relationship (Filter1 OR Filter2)
AND Filter3. In this case, Web Intelligence first restricts the data by the
conditions in either Filter1 or Filter2. Web Intelligence then compares this
52Building queries with Web Intelligence Query - HTML
data with the condition in Filter3 and returns only the data that corresponds
to this condition.
Combining query filters
Typical business questions require you to retrieve information that matches
more than one criteria. For example, if you are analyzing customer services
data, you will most likely want to focus on customers for a specific time period
and also for a specific region, and probably also for a specific level of
customer service contract. You can retrieve data that answers several criteria
like this by combining filters in the same query.
Example: Analyze sales revenue this year at stores where the floor size
is over 4,000 square feet and sales revenue figures are equal to or less
than $1.5M
In this example, you are an operations manager for a retail chain. You want
to analyze information about the large retail stores in your chain that are
making less than the sales revenue figure your company has set as the
target.
Filtering data using query filters in Query - HTML
Types of query filter
7
To do this you add a predefined filter on the [Year] dimension to specify
that you only want to retrieve values for this year. Then you create a second
filter on the [Sales Floor Size] dimension to specify that you only want to
retrieve data for stores where the floor size is greater than 4,000 square
feet. After this, you create a third filter on the [Sales Revenue] measure to
specify that you only want to retrieve data for stores where the sales revenue
figures are equal to or less than $1.5M. Finally, you combine these three
filters with the And operator:
Last Year
AND
When you run the query, only data for stores that satisfy all three criteria
will be returned to the report.
Sales Floor Size Group Greater than or equal to:
4000
Sales Revenue Less than 1,500,000
Building queries with Web Intelligence Query - HTML53
Filtering data using query filters in Query - HTML
7
Types of query filter
To combine query filters
1. Create the filters and add them to the Query Filters pane.
By default, Web Intelligence combines the filters with the AND operator.
2. Double-click the operator (in Query - HTML) or click the arrow next to the
operator checkbox and select the other operator (HTML Report Panel)
to toggle between AND and OR.
Nesting query filters
Nesting query filters allows you to create more complex filter conditions than
is possible when you combine filters at the same level.
When you nest filters, you set the order in which Web Intelligence evaluates
them. For example, Web Intelligence can return the data given by two query
filters joined in an OR relationship (where either one filter condition or the
other is true) and then further restrict this data by applying another filter to
it. In this case, the two filters in an OR relationship are nested, then compared
with the other filter in an AND relationship.
The following example illustrates this:
Example: List all sales made in Japan either in Q4 or where the revenue
was greater than 1000000
To answer this question you create the following nested query filter:
Country Equal To Japan
AND
OR
Web Intelligence first returns sales data where the sale was made in Q4
or the revenue was greater than 1000000, then restricts this data further
by returning only those sales made in Japan.
54Building queries with Web Intelligence Query - HTML
Quarter Equal To Q4
Revenue Greater Than
1000000
Filtering data using query filters in Query - HTML
Filtering queries with the Keydate variable
To nest query filters in Query - HTML
1. Add the query filter or filter combination that you want to be evaluated
last to the Query Filters pane.
2. Select any filter or the AND or OR operator (if there are multiple filters)
and click Add nested filter on the right of the Query Filters pane.
The nested filter outline appears with the OR operator by default.
3. With the nested filter operator selected, select the object or objects that
you want to appear in the nested filter and click >> to add them to the
nested filter.
4. Double-click the nested filter operator to toggle between AND and OR.
5. To add further nested filters, select any object at any level in the current
set of filters, then click Add nested filter to add a further nested filter.
6. To add or remove filters at any level in the set of nested filters, select the
AND or OR operator that links the filters at that level, then add or remove
objects from the list of filters.
Filtering queries with the Keydate
7
variable
The keydate variable is a SAP BW variable which restricts dimension
members to the values on a given date. You can set a keydate value when
building queries on universes based on SAP BW data sources.
When a keydate is set, the lists of values in any prompts defined on the query
are filtered according to the keydate. The dimension values returned by the
query are also filtered according to the keydate.
You can define one keydate per query. This means that, for example, you
can compare data changes by creating two data providers based on the
same query, but with different keydates.
If a keydate is available in the universe, Web Intelligence allows you to set
its value in the query panel. The keydate can either be the default date used
by the SAP BW cube, or a date set in Web Intelligence.
When you define a keydate in the query panel, you can also define that the
report user must re-select the keydate each time they refresh the report data.
Building queries with Web Intelligence Query - HTML55
Filtering data using query filters in Query - HTML
7
Filtering queries with the Keydate variable
To set the keydate for a single query in the query
panel
1. Click Keydate Properties to display the "Keydate Properties" dialog
box.
2. Select Use the default date if you want the SAP BW cube to determine
the keydate value.
If a specific default date is set in the SAP BW cube, Web Intelligence
displays the date. If no date is set in the cube, the cube uses the last
available keydate and Web Intelligence displays Last Available.
3. Select Set a date and choose a date from the calendar if you want to set
the keydate yourself.
To set the keydate for multiple queries in the query
panel
1. Click Keydate Properties to display the "Keydate Properties" dialog
box.
2. Select Use the default date if you want the SAP BW cube to determine
the keydate value.
If a specific default date is set in the SAP BW cube, Web Intelligence
displays the date. If no date is set in the cube, the cube uses the last
available keydate and Web Intelligence displays Last Available.
3. Click Set date for all queries and choose a date from the calendar if you
want to set the same date for all queries.
4. Click Set a date for each query if you want to set different dates for each
query.
5. For each query, select Default from the drop-down list if you want the
SAP BW cube to determine the keydate value. To choose your own date,
select Specific from the drop-down list, then choose the date from the
calendar.
56Building queries with Web Intelligence Query - HTML
Filtering data using prompts
in Query - HTML
8
Filtering data using prompts in Query - HTML
8
What is a prompt?
What is a prompt?
A prompt is a special type of query filter. It is a dynamic filter that displays a
question every time you refresh the data in a document. You answer prompts
by either typing or selecting the value(s) you want to view before you refresh
the data. Web Intelligence retrieves only the values you specified from the
database and returns those values to the reports within the document.
Prompts allow multiple users viewing a single document to specify a different
sub-set of the database information and display it in the same report tables
and charts. Prompts also reduce the time it takes for the data to be retrieved
from the database.
A prompt contains the following elements:
•a filtered object
•an operator
•a message
For example, to prompt users to select a specific year, you define a prompt
on the [Year] dimension:
Year Equal To ("Which year?°)
In this prompt, the filtered object is [Year], the operator is Equal To, and the
prompt message is "Which year?".
You can define prompts on any dimension, measure, or detail object. For
example, you can filter the [Year] dimension to return values for a specific
year, filter the [Sales Revenue] measure to return values for a range of
revenue figures, or filter the [Year/week] detail to return values for a specific
week in a year.
You can create multiple prompts, related by the AND or OR operators, in
the same query. You can also nest prompts. When the user runs a query,
Web Intelligence displays the prompts.
Prompts appear in the SQL generated by a Web Intelligence query as either
the value supplied in response to the prompt (when the SQL is read-only),
or as Web Intelligence prompt syntax (when the SQL is editable). For
example, a prompt on [Country) can appear in the generated SQL as
58Building queries with Web Intelligence Query - HTML
or as
Resort_country.country In ('UK')
Merged prompts
When a document contains multiple data providers, any prompts that include
(1) objects with the same data type, (2) operators of the same operator type,
and that (3) use the same prompt text are merged.
When all the data providers are refreshed, a single prompt message appears
for such prompts.
The List of Values displayed by the merged prompt is the list associated with
the object in the prompt that has the most display property constraints.
Cascading prompts
Some objects cause Web Intelligence to display a cascading prompt when
they are included in a prompt definition. The universe designer defines the
lists of values of these objects hierarchically in relation to other object lists
of values in the universe.
Filtering data using prompts in Query - HTML
Cascading prompts
8
Cascading prompts help the user to focus on the object values they want to
include in the prompt without the need to search all possible object values.
Example: Choosing a store
In this example the universe designer has defined the [Store Name], [City]
and [State] objects in a hierarchy. If you include a prompt [Store Name]
Equal To <value>, Web Intelligence displays these objects in a hierarchy
in the Prompts dialog box. In order to select the store, the user must first
select the state in which the store city is found, then the city in which the
store is found, then the store itself. When the user selects the state, Web
Intelligence restricts the values of City to the cities in the state; when the
user selects the city, Web Intelligence restricts the stores to the stores in
the city.
Building queries with Web Intelligence Query - HTML59
Filtering data using prompts in Query - HTML
8
Hierarchical lists of values
Hierarchical lists of values
If your universe contains hierararchical lists of values, these lists appear in
tree form. You navigate down through the tree to the items you want.
Whether a list of values appears as a cascading prompt or hierarchically
depends on how the list is configured in the universe. See your administrator
for more information.
Query filter and prompt operators
Equal To operator
Use the Equal to operator to obtain data equal to a value.
For example, to return data for the US only, create the filter "County Equal
To US".
Not Equal To operator
Use the Not Equal To operator to obtain data not equal to a value.
For example, to return data for all countries except the US create the filter
"County Not Equal To US".
Different From operator
Use the Different From operator to retrieve data different from a value.
For example, to retrieve data for all quarters execpt Q4, create the filter
[Quarter] Different From "Q4"
60Building queries with Web Intelligence Query - HTML
Filtering data using prompts in Query - HTML
Greater Than operator
Use the Greater Than operator to retrieve data greater than a value.
For example, to retrieve data for customers aged over 60, create the filter
"[Customer Age] Greater than 60".
Greater Than Or Equal To operator
Use the Greater Than Or Equal To operator to retrieve data greater than or
equal to a value.
For example, to retrieve data for revenue starting from $1.5M, create the
filter "[Revenue] Greater than or equal to 1000500".
Less Than operator
Query filter and prompt operators
8
Use the Less Than operator to retrieve data lower than a value.
For example, to retrieve data for exam grades lower than 40, create the filter
"[Exam Grade] Less Than 40".
Less Than Or Equal To operator
Use the Less Than Or Equal To operator to retrieve data less than or equal
to a value.
For example, to retrieve data for customers whose age is 30 or less, create
the filter "[Age] Less Than Or Equal To 30".
Between operator
Use the Between operator to retrieve data between and including two values.
Building queries with Web Intelligence Query - HTML61
Filtering data using prompts in Query - HTML
8
Query filter and prompt operators
For example, to retrieve data for weeks starting at week 25 and finishing at
36 (including week 25 and week 36), create the filter "[Week] Between 25
and 36".
Not Between operator
Use the Not Between operator to retrieve data outside the range of two
values.
For example; to retrieve data for all the weeks of the year, except for and
not including weeks 25 through 36, create the filter "[Week] Not between 25
and 36".
In List operator
Use the In List operator to retrieve data corresponding to values in a list of
values.
For example, to retrieve data for the US, UK and Japan only, create the filter
[Country] In List ("US";"UK";"Japan").
Not In List operator
Use the Not In List operator to retrieve data that does not correspond to
multiple values.
For example, if you do not want to retrieve data for the US, UK and Japan,
create the filter [Country] Not In ("US";"UK";"Japan").
Matches Pattern operator
Use the Matches Pattern operator to retrieve data that includes a specific
string or part of a string.
For example, to retrieve customers whose date of birth is 1972, create the
filter [DOB] Matches Pattern "72".
62Building queries with Web Intelligence Query - HTML
Different From Pattern operator
Use the Different From Pattern operator to return data that doesn't include
a specific string.
For example, to retrieve customers whose date of birth is not 1972, create
the filter [DOB] Different From Pattern '72'.
Both operator
Use the Both operator to retrieve data that corresponds to two values.
For example, to retrieve customers who have both a fixed and a mobile
telephone, create the filter [Account Type] Both 'Fixed' And 'Mobile'.
Except operator
Filtering data using prompts in Query - HTML
Query filter and prompt operators
8
Use the Except operator to retrieve data that corresponds to one value and
excludes another.
For example, to retrieve customers who have a fixed telephone and do not
have a mobile telephone, create the filter [Account Type] 'Fixed' Except
'Mobile'.
The Except operator is more restrictive than Different From or Not In
List. For example, a report that returns customers and that includes the
filter [Lines] Different From 'Accessories' excludes all sales records
where the item sold is part of the 'Accessories' line. If the same customer
has purchased Accessories and non-Accessories items, the customer still
appears in the report, but their spending total includes only non-Accessories
sales.
If the filter is [Lines] Except 'Accessories', only customers who have
bought no accessories are included in the report.
Related Topics
•Not In List operator on page 62
•Different From operator on page 60
Building queries with Web Intelligence Query - HTML63
Filtering data using prompts in Query - HTML
8
To create a prompt in Query - HTML
To create a prompt in Query - HTML
You can apply one or multiple prompts to a document. The prompts appear
in the order you create them, but you can also edit the order in which the
prompts appear later.
1. From the Universe pane, drag the object on which you want to apply a
prompt onto the Query Filters pane.
An outline query filter appears in the Query Filters pane.
2. Click Define filter type on the right of the outline query filter and select
Prompt from the list of filter operators.
3. Edit the prompt message to form a business question, or leave the default
message.
4. Click Properties to display the "Prompt Properties" dialog box.
5. Use the "Prompt Properties" dialog box to define how the prompt
displays.
Defining how prompts display
By default, prompts display a box and a list of values. You answer the prompt
by either typing the value(s) in the box or by selecting value(s) from the list.
You can modify how prompts display by checking one, some, or all of the
following options:
If you want the prompt
to display...
the list of values associated with the filtered dimension, measure, or detail,
64Building queries with Web Intelligence Query - HTML
want to view all the values for the object and
then select from those
values
then...(useful when you...)
leave the option selected
by default: Prompt with
List of Values
Filtering data using prompts in Query - HTML
To remove a prompt
8
If you want the prompt
to display...
the value(s) specified the
last time the prompt was
answered (users can select a different value(s)),
the value(s) you specify
as the default (users can
select a different value(s)),
a list of values from which
users select a value(s),
often reselect the same
value(s) when you refresh the document, but
want the ability to select
a different value when
necessary, such as the
name of the current
month
almost always reselect
the same value(s) when
you refresh the document, but want the ability
to select a different value
when necessary, such as
the number for the current year
prevent users from typing
a value that might not exist on the database
then...(useful when you...)
select the option: Keep
last values selected
select the option: Set default value(s)
select the option: Select
only from List
Note: If the prompt is for a date and you want users to see the popup
calendar in order to select the date(s) then do not select Prompt with List
of Values
To remove a prompt
•Select the prompt and then click the Delete key.
Combining prompts
Combining multiple prompts on a single query enables you to filter the data
returned to the document so that each person viewing the reports sees only
the information relevant to their business need. For example, you can
combine the following three prompts on a Customer Accounts document:
Building queries with Web Intelligence Query - HTML65
Filtering data using prompts in Query - HTML
8
To change the order of prompts in Query - HTML
•Which customer?
•Which account?
•Which calendar period: from? to?
This enables each accounts manager viewing the document to view report
values for a specific customer account during a specific period.
You combine prompts in the same way that you combine query filters.
Combining prompts with query filters
Combining prompts and filters on a single query enables you decide the
values for some of the selected objects on the query using filters and allow
users to decide the values of other selected objects using prompts. For
example, if you combine the following filters and prompts on a HR document:
•[Year] Equal to This Year
•[Job title] Not equal to Senior Executive
•Which employee?
Users viewing the document can choose which employee they view
information for, but they can only view data for the current year and they
can’t view data for senior executives.
To change the order of prompts in Query
- HTML
1. Click Query Properties to display the Query Properties dialog box.
2. Select the prompt you want to move up or down in the prompt order in
the Prompt Order box, then press the Up or Down arrow next to the box.
66Building queries with Web Intelligence Query - HTML
Get More Help
A
A
Get More Help
Online documentation library
Business Objects offers a full documentation set covering all products and
their deployment. The online documentation library has the most up-to-date
version of the Business Objects product documentation. You can browse
the library contents, do full-text searches, read guides on line, and download
PDF versions. The library is updated regularly with new content as it becomes
available.
The Business Objects Customer Support web site contains information about
Customer Support programs and services. It also has links to a wide range
of technical information including knowledgebase articles, downloads, and
support forums.
http://www.businessobjects.com/support/
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:
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 web site:
http://www.businessobjects.com/services/training
68Building queries with Web Intelligence Query - HTML
Get More Help
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:
mailto: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: http://www.businessobjects.com/support/.
Business Objects product information
For information about the full range of Business Objects products, visit:
http://www.businessobjects.com.
A
Building queries with Web Intelligence Query - HTML69
A
Get More Help
70Building queries with Web Intelligence Query - HTML
cascading 59
changing the order of 66
combining with query filters 66
creating 64
properties 64
Q
queries
adding 35
adding objects to in Query - HTML 32
ambiguous query defined 42
duplicating 35
editing the SQL 33
interrupting 34
multiple queries defined 34
Query - HTML
adding objects in 32
removing objects in 32
query contexts defined 42
query filters defined 46
removing 34
renaming in Query - HTML 32
viewing the SQL 33
Query - HTML defined 10
query context
choosing 43
query contexts
choosing 43
query contexts defined 42
query editor
selecting 17
query editors
Query - HTML 10
query filter and prompt opertors
Between 50, 61
Both 51, 63
Different From 49, 60
Different From Pattern 51, 63
Equal To 49, 60
Except 52, 63
Greater Than 49, 61
Greater Than Or Equal To 49, 61
In List 51, 62
Less Than 50, 61
Less Than Or Equal To 50, 61
Matches Pattern 51, 62
Not Between 50, 62
Not Equal To 49, 60
Not In List 51, 62
query filters 47
about combining query filters 53
about nesting query filters 54
combining 54
combining with prompts 66
compared with report filters 47
nesting in Query - HTML 55
predefined query filters defined 48
selecting a predefined query filter 48
query filters defined 46
query properties
allow other users to edit all queries 27
clear contexts 28
max retrieval time 27
max rows retrieved 27
prompt order 28
reset contexts on refresh 28
retrieve duplicate rows 28
sample result set 28
72Building queries with Web Intelligence Query - HTML
Index
R
report filters 47
compared with query filters 47
reports
drilling on 9
viewing and printing 9
S
sample result set query property 28
scope of analysis
levels 38
setting in Query - HTML 39
Scope of analysis
defined 38
SQL
viewing and editing 33
subclasses 25
U
universe
selecting 24
selecting default 18
V
view format
selecting 17
W
Web Intelligence
and business intelligence 8
Building queries with Web Intelligence Query - HTML73
Index
74Building queries with Web Intelligence Query - HTML
Loading...
+ hidden pages
You need points to download manuals.
1 point = 1 manual.
You can buy points or you can get point for every manual you upload.