Building reports using the Java
Report Panel
BusinessObjects Enterprise XI 3.0
Copyright
© 2008 Business Objects. All rights reserved. Business Objects owns the following
U.S. patents, which may cover products that are offered and licensed by Business
Objects: 5,555,403; 5,857,205; 6,289,352; 6,247,008; 6,490,593; 6,578,027;
6,831,668; 6,768,986; 6,772,409; 6,882,998; 7,139,766; 7,299,419; 7,194,465;
7,222,130; 7,181,440 and 7,181,435. Business Objects and the Business Objects
logo, BusinessObjects, Business Objects Crystal Vision, Business Process On
Demand, BusinessQuery, Crystal Analysis, Crystal Applications, Crystal Decisions,
Crystal Enterprise, Crystal Insider, Crystal Reports, Desktop Intelligence, Inxight,
the Inxight Logo, LinguistX, Star Tree, Table Lens, ThingFinder, Timewall, Let
there be light, Metify, NSite, Rapid Marts, RapidMarts, the Spectrum Design, Web
Intelligence, Workmail and Xcelsius are trademarks or registered trademarks in
the United States and/or other countries of Business Objects and/or affiliated
companies. All other names mentioned herein may be trademarks of their respective
owners.
Third-party
Contributors
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
2008-02-06
Contents
About Web Intelligence 17Chapter 1
How Web Intelligence performs business intelligence over the web.........18
How Web Intelligence performs business intelligence offline....................18
Interacting with Web Intelligence reports...................................................18
Creating and editing Web Intelligence documents....................................20
Accessing Web Intelligence from InfoView 23Chapter 2
To log in to InfoView...................................................................................24
To log out of InfoView................................................................................25
Web Intelligence InfoView options.............................................................25
Viewing and printing Web Intelligence reports.....................................19
Drilling on Web Intelligence reports......................................................19
Performing on-report analysis..............................................................19
Web Intelligence Query - HTML...........................................................20
Web Intelligence Java Report Panel....................................................21
Web Intelligence Rich Client................................................................21
Web Intelligence HTML Report Panel..................................................21
Web Intelligence document creation and viewing options...................25
To set Web Intelligence drill options.....................................................28
Web Intelligence locale options............................................................30
Building and editing queries in the Java Report Panel 33Chapter 3
Creating, editing and saving documents...................................................34
To select a universe..............................................................................34
To edit a Web Intelligence document...................................................34
How universe objects map to data............................................................35
Building reports using the Java Report Panel 3
Contents
Classes and subclasses.......................................................................35
Dimension object..................................................................................35
Detail object..........................................................................................35
Measure object.....................................................................................36
Building and using queries........................................................................36
To build and run a query in the Java Report Panel..............................36
To set query properties.........................................................................37
To interrupt a query..............................................................................38
To remove a query................................................................................38
To duplicate a query.............................................................................39
Multiple queries....................................................................................39
Controlling access to queries....................................................................40
Allow other users to edit all data providers query property..................40
Controlling how queries retrieve data........................................................40
Max retrieval time query property.........................................................40
Max rows retrieved query property.......................................................40
Retrieve duplicate rows query property................................................41
Setting the scope of analysis.....................................................................41
Scope of analysis.................................................................................41
Levels of scope of analysis..................................................................42
To set the scope of analysis.................................................................43
Query contexts...........................................................................................43
What is an ambiguous query?..............................................................43
What is a context?................................................................................44
Choosing a context when you run a query...........................................44
Defining how contexts are used...........................................................45
Using combined queries 47Chapter 4
Combined queries defined.........................................................................48
Types of combined query...........................................................................48
What can you do with combined queries?.................................................49
4 Building reports using the Java Report Panel
Contents
How does Web Intelligence generate combined queries?........................50
To build a combined query.........................................................................50
Combined query structure.........................................................................51
To return a list of years and reservation years based on the number of
guests...................................................................................................51
Combined query precedence....................................................................52
Multiple combined queries....................................................................53
To set the order of precedence of combined queries in the Java Report
Panel....................................................................................................54
Filtering queries 55Chapter 5
Query filters defined...................................................................................56
Query filters and report filters compared...................................................57
Types of query filter...................................................................................57
Predefined query filters........................................................................58
Quick filters...........................................................................................58
Custom query filters.............................................................................59
Query filter and prompt operators........................................................60
Combining query filters.........................................................................63
Filtering data with subqueries 67Chapter 6
What is a subquery?..................................................................................68
What can you do with subqueries?............................................................68
How do subqueries work?.........................................................................68
To build a subquery....................................................................................68
To find out which customers bought a service that had previously been
reserved in Q1 of 2003, and how much revenue have they generated.70
Subquery parameters................................................................................70
Ranking data using database ranking 73Chapter 7
What is database ranking?........................................................................74
Building reports using the Java Report Panel 5
Contents
Database ranking parameters...................................................................74
To create a database ranking....................................................................76
To create a report that returns the top 10 employees based on salary,
and calculated by department..............................................................77
Filtering data using prompts 79Chapter 8
What is a prompt?......................................................................................80
Merged prompts...................................................................................81
Cascading prompts....................................................................................81
Hierarchical lists of values.........................................................................82
Query filter and prompt operators..............................................................82
Equal To operator.................................................................................82
Not Equal To operator..........................................................................82
Different From operator........................................................................82
Greater Than operator..........................................................................83
Greater Than Or Equal To operator......................................................83
Less Than operator..............................................................................83
Less Than Or Equal To operator..........................................................83
Between operator.................................................................................83
Not Between operator..........................................................................84
In List operator.....................................................................................84
Not In List operator...............................................................................84
Matches Pattern operator.....................................................................84
Different From Pattern operator...........................................................85
Both operator........................................................................................85
Except operator....................................................................................85
To create a prompt.....................................................................................86
To remove a prompt...................................................................................86
Defining how prompts display....................................................................87
Combining prompts....................................................................................88
Combining prompts with query filters...................................................88
6 Building reports using the Java Report Panel
Contents
To change the order of prompts.................................................................88
Merging prompts........................................................................................89
The Java Report Panel reporting interface 91Chapter 9
Web Intelligence viewing modes 95Chapter 10
To switch between viewing modes............................................................96
Draft mode............................................................................................96
Page mode...........................................................................................96
PDF mode............................................................................................96
Quick Display mode.............................................................................97
To select Enhanced Viewing mode......................................................98
Using alerters to highlight results 99Chapter 11
Creating, using and removing alerters.....................................................100
Alerters defined..................................................................................100
Using formulas to create advanced alerters............................................104
To create an alerter containing a formula...........................................106
To use a formula to generate cell contents........................................106
Merging dimensions from multiple data providers 107Chapter 12
Merged dimensions defined....................................................................108
When to merge dimensions.....................................................................108
Choosing which dimensions to merge.....................................................108
Merged dimension example....................................................................109
Forcing merged calculations with the ForceMerge function....................111
Creating, editing and deleting merged dimensions.................................113
To merge dimensions.........................................................................113
To merge dimensions automatically...................................................114
To edit a merged dimension...............................................................114
Building reports using the Java Report Panel 7
Contents
To delete a merged dimension...........................................................115
Understanding the effects of merged dimensions...................................115
Synchronizing data providers with different aggregation levels.........115
Detail objects and merged dimensions..............................................116
Filtering merged dimensions..............................................................118
Drilling on merged dimensions...........................................................120
Extending the values returned by merged dimensions......................120
Ranking data in Web Intelligence reports 123Chapter 13
Ranking data............................................................................................124
Rankings and sorts..................................................................................124
Tied rankings...........................................................................................125
Ranking parameters................................................................................127
Example of a ranking..........................................................................129
Ranking and data order...........................................................................130
Ranking workflows...................................................................................130
To create a ranking.............................................................................130
Ranking examples...................................................................................131
Tracking changes in data 137Chapter 14
Tracking changes in your data.................................................................138
Types of data change..............................................................................138
Data tracking modes................................................................................139
Automatic data tracking mode............................................................139
Manual data tracking mode................................................................139
To activate data tracking..........................................................................139
To change the reference data..................................................................140
Displaying changed data.........................................................................140
To display or hide changed data........................................................140
Configuring the appearance of changed data....................................140
8 Building reports using the Java Report Panel
Contents
How changed data is displayed in blocks..........................................141
How changed data is displayed in reports with merged dimensions...143
How changed data is displayed in sections.......................................144
How changed data is displayed in blocks with breaks.......................147
How changed data is displayed in charts...........................................147
Data tracking limitations...........................................................................147
Data tracking and drill.........................................................................148
Using the Web Intelligence formula language to track changed data.....148
The RefValue function........................................................................148
The RefValueDate function................................................................149
Building formulas using the RefValue function...................................149
Changed data and the calculation context..............................................150
Using sections, breaks and sorts 153Chapter 15
Using sections to group data...................................................................154
Grouping information with sections....................................................154
Sections in Structure View.................................................................156
Creating and removing sections and subsections..............................157
Section properties..............................................................................159
To set the page layout for a section...................................................160
Using breaks............................................................................................160
Breaks defined...................................................................................160
Breaks compared to sections.............................................................161
Default sort order in breaks................................................................161
To insert a break.................................................................................161
To prioritize breaks.............................................................................162
To define display properties of a break..............................................162
To remove a break..............................................................................164
Using sorts to organize data....................................................................164
Sorting the results displayed on reports.............................................164
To apply an ascending or descending sort.........................................165
Building reports using the Java Report Panel 9
Contents
To apply a custom sort.......................................................................165
To prioritize multiple sorts...................................................................166
Calculations, formulas and variables 167Chapter 16
Calculations, formulas and variables.......................................................168
Working with standard calculations.........................................................168
To insert a standard calculation in a table or crosstab.......................168
To remove a standard calculation......................................................169
Working with formulas.............................................................................169
The Formula toolbar...........................................................................169
Working with variables.............................................................................171
To create a variable using the Create Variable dialog box.................171
To create a variable using the Variable Editor....................................171
To edit a variable................................................................................172
To delete a variable............................................................................172
Filtering reports 173Chapter 17
Report filters defined................................................................................174
Query filters and report filters compared.................................................174
Report filter operators..............................................................................175
Equal To operator...............................................................................175
Not Equal To operator........................................................................175
Different From operator......................................................................175
Greater Than operator........................................................................175
Greater Than Or Equal To operator...................................................175
Less Than operator............................................................................176
Less Than Or Equal To operator........................................................176
Between operator...............................................................................176
Not Between operator........................................................................176
In List operator...................................................................................177
10 Building reports using the Java Report Panel
Contents
Not In List operator.............................................................................177
Is Null operator...................................................................................177
Is Not Null operator............................................................................177
Creating, editing and deleting report filters..............................................178
To create a report filter using the Quick Filter option..........................178
To create a report filter using the Filter Editor....................................178
To combine multiple filters on a report...............................................179
To view the filters on a report.............................................................179
To edit a report filter............................................................................179
To delete a report filter........................................................................180
Drilling on report data 181Chapter 18
What is drill?............................................................................................182
Scope of analysis...............................................................................183
To set the scope of analysis...............................................................185
Drill paths and hierarchies..................................................................185
To switch to drill mode.............................................................................186
Retrieving more levels of data to the report.............................................187
To drill out of the scope of analysis....................................................187
To choose a drill path when more than one is available..........................188
Drilling on dimensions in tables and sections..........................................188
Drilling down.......................................................................................189
Drilling up...........................................................................................191
Drilling by............................................................................................191
Drilling on measures in tables and sections............................................194
To drill down on a measure value.......................................................194
To drill up on a measure value...........................................................194
Synchronizing drill across multiple tables and charts..............................195
Drilling on charts......................................................................................195
Drilling on dimensions via chart axes.................................................196
Drilling on measures in charts............................................................197
Building reports using the Java Report Panel 11
Contents
Drilling on axis legends......................................................................199
Using filters when you drill.......................................................................199
To change a filter value on the Drill toolbar........................................200
To add or remove a drill filter .............................................................200
Saving reports with drill filters..................................................................200
Refreshing data in a drilled report with prompts......................................201
Drilling with query drill..............................................................................201
Query drill defined..............................................................................201
Displaying data in tables 205Chapter 19
Tables in Web Intelligence.......................................................................206
Table types in Web Intelligence...............................................................206
Vertical table.......................................................................................206
Horizontal table..................................................................................207
Crosstab.............................................................................................207
Forms.................................................................................................208
Structure View and Results View.............................................................209
Creating, editing and removing tables.....................................................209
To create a table by dragging objects onto a report...........................209
To create a table by selecting a template...........................................210
To duplicate a table............................................................................211
To apply a different template to a table by using drag and drop........211
To apply a different template to a table with Turn To..........................212
To add rows or columns to tables using drag and drop......................212
To add table rows or columns using the Insert row or column toolbar
menu..................................................................................................213
To remove table rows or columns......................................................213
To move a row or column...................................................................213
To swap a row or column....................................................................213
To replace a row or column................................................................214
To clear cell contents from a table......................................................214
12 Building reports using the Java Report Panel
Contents
To remove a table...............................................................................214
Modifying tables to create crosstabs.......................................................215
To create a crosstab by adding an object to a vertical or horizontal
table....................................................................................................215
To create a crosstab by moving a column or row...............................215
Formatting tables and table cells.............................................................215
To select a background color for the table.........................................215
To define alternate row and column colors for a table........................216
To select or remove a table skin.........................................................216
To insert an image in a table..............................................................217
To format table borders......................................................................217
To format text in table cells.................................................................218
To set cell height and width................................................................218
To copy formatting using the Format Painter.....................................219
To set the position of a table or chart on the report page...................220
To layer tables and cells.....................................................................220
Determining how tables display data.......................................................221
Showing or hiding empty tables, rows or columns.............................221
Aggregating duplicate rows................................................................221
To show or hide headers and footers.................................................222
To start tables on a new report page..................................................222
To display object names in headers on crosstabs..............................222
To avoid page breaks in tables...........................................................222
To repeat table headers or footers on report pages...........................222
Copying tables.........................................................................................223
To copy a table...................................................................................223
To copy a table as text........................................................................223
Displaying data in free-standing cells 225Chapter 20
Free-standing cells defined.....................................................................226
To insert a blank cell on a report..............................................................226
Building reports using the Java Report Panel 13
Contents
To copy a free-standing cell.....................................................................227
To copy a free-standing cell to another application as text......................227
To format a free-standing cell..................................................................227
Displaying data in charts 229Chapter 21
Creating charts........................................................................................230
Chart types in Web Intelligence...............................................................230
Bar charts...........................................................................................230
Line charts..........................................................................................231
Area charts.........................................................................................231
Pie charts...........................................................................................232
Radar, polar and scatter charts..........................................................232
3D charts............................................................................................233
2D charts............................................................................................233
Adding, copying and removing charts.....................................................234
To add a chart to a report...................................................................234
To copy a chart...................................................................................235
To remove a chart...............................................................................235
Changing the chart type...........................................................................235
To change the chart type by using drag and drop..............................235
To change the chart type by using TurnTo.........................................236
Positioning and sizing charts...................................................................236
To set the position of a table or chart on the report page...................236
To position a chart in relation to another chart or table......................237
To resize a chart.................................................................................237
Formatting charts.....................................................................................238
To insert and format a chart title.........................................................238
To display a chart with a 3D look........................................................238
To add background colors to a chart..................................................238
To modify chart borders......................................................................238
To select and format chart floors and walls........................................239
14 Building reports using the Java Report Panel
Contents
To show and format axis legends.......................................................239
To avoid page breaks in charts..........................................................240
To show axis labels on pie charts.......................................................240
To format axis label text, borders, and background...........................240
To show, hide or format the axes grid................................................240
Displaying and formatting chart data..................................................241
Formatting numbers and dates 245Chapter 22
Predefined and custom formats...............................................................246
Predefined formats.............................................................................246
Custom formats..................................................................................247
Working with documents 253Chapter 23
Saving documents...................................................................................254
To save a new Web Intelligence document in InfoView.....................254
To save a Web Intelligence document as an Excel spreadsheet.......254
To save a Web Intelligence document as a PDF file..........................255
To delete a Web Intelligence document from InfoView............................255
Editing document properties....................................................................255
To display document properties.........................................................257
Printing Web Intelligence documents......................................................257
To print a report from the Java Report Panel.....................................258
Get More Help 259Appendix A
Index 263
Building reports using the Java Report Panel 15
Contents
16 Building reports using the Java Report Panel
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.
18 Building reports using the Java Report Panel
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 reports using the Java Report Panel 19
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 20
•
Web Intelligence Java Report Panel on page 21
•
Web Intelligence Rich Client on page 21
•
Web Intelligence HTML Report Panel on page 21
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.
20 Building reports using the Java Report Panel
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 reports using the Java Report Panel 21
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.
22 Building reports using the Java Report Panel
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.
24 Building reports using the Java Report Panel
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 reports using the Java Report Panel 25
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:
26 Building reports using the Java Report Panel
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 reports using the Java Report Panel 27
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.
28 Building reports using the Java Report Panel
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 reports using the Java Report Panel 29
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
30 Building reports using the Java Report Panel
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 32.
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 32.
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 reports using the Java Report Panel 31
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.
32 Building reports using the Java Report Panel
Building and editing queries in the Java Report Panel
3
Building and editing queries in the Java Report Panel
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.
34 Building reports using the Java Report Panel
Building and editing queries in the Java Report Panel
How universe objects map to data
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.
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.
3
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
example, [Age] is a detail object that is associated with the (Customer]
dimension.
Detail objects appear as follows in the Web Intelligence query panel:
Building reports using the Java Report Panel 35
Building and editing queries in the Java Report Panel
3
Building and using queries
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.
Building and using queries
To build and run a query in the Java Report Panel
1. On the InfoView Home page, click the arrow next to New, then select
Web Intelligence Document on the top toolbar. Alternatively, click Add
Query in the Query Panel if you are adding a query to a document that
already contains at least one query.
2. Click the title of the universe on which you want to create a document.
3. If the query you are building is not the first query in the document, you
need to specify how its data will be displayed.
36 Building reports using the Java Report Panel
Building and editing queries in the Java Report Panel
Building and using queries
DescriptionOption
3
Insert a table in a new report
Insert a table in the current
report
Include the result objects in
the document without generating a table
4. In the Data tab, open a class.
5. Select the objects you want to include in the query and drag them to the
Result Objects pane. To add all the objects in the class, drag the class
to the Result Objects pane.
6. Repeat the previous step until the query contains all the objects you want
to include.
7. Select the objects on which you want to define query filters and drag them
to the Query Filters pane. To create a quick filter on an object, select
the object in the Result Objects pane then click Add Quick Filter at the
top right of the pane.
8. Set the scope of analysis and other query properties.
9. To remove an object from the Result Objects or Query Filters panes,
click Remove at the top right corner of the pane.
10. To remove all objects from the Result Objects or Query Filters panes,
click Remove All at the top right corner of the pane.
11. Click Run Query to run the query.
Display the data on a new report in the
document
Display the data on the currently selected
report in a new table
Include the data in the document without
displaying the data on a report. (You can
add the objects returned by the query to
the report(s) later.)
To set query properties
1. Verify that you are in Query View.
2. Click the Properties tab.
3. Click the Fold/Unfold arrows at the top right of each section of the options
to expand or close the property groups.
4. Select or type query property options.
Building reports using the Java Report Panel 37
Building and editing queries in the Java Report Panel
3
Building and using queries
To interrupt a query
You can interrupt a query before Web Intelligence has returned all the data
to the document.
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.
Before returning the data to the document, Web Intelligence requests you
to choose which version of the data you want retrieved
1. On the "Waiting - Refresh Data" dialog box, click Cancel.
The "Interrupt Data Retrieval" dialog box appears.
2. Select one of the options on the "Interrupt Data Retrieval" dialog box.
DescriptionOption
Restore the results
from the previous
data retrieval
Purge all data from
the document
Return the partial
results
To remove a query
1. Select a the query you want to remove by right-clicking the appropriate
Query tab.
2. Click Remove.
Web Intelligence restores the values to the document
that were retrieved the last time the query was run.
The values displayed will not be the most up to data
information available on the database. You can run
the query later to return the up to date values from
the database
Web Intelligence displays the document empty of
values. The structure and formatting of the document
is retained. You can run the query later to return the
up to date values from the database
Web Intelligence displays the new values retrieved
so far in the appropriate parts of the document. The
rest of the document will display the values retrieved
the last time the query was run
38 Building reports using the Java Report Panel
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
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.
Building and editing queries in the Java Report Panel
Building and using queries
3
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
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.
Multiple queries, combined queries and synchronized queries compared
It is important to understand the relationship between multiple data providers,
combined queries and synchronized data providers .
• A single data provider, or query, can contain multiple queries, called
combined queries.
Building reports using the Java Report Panel 39
Building and editing queries in the Java Report Panel
3
Controlling access to queries
• A document can be based on multiple data providers (each one of which
can contain multiple queries). These data providers do not need to be
synchronized. If they are not synchronized, the document contains multiple
sources of unrelated data.
• Multiple data providers can be synchronized if they have common
dimensions around which they can be linked. You synchronize data
providers by merging these common dimensions.
Controlling access to queries
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.
Controlling how queries retrieve data
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.
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
40 Building reports using the Java Report Panel
Building and editing queries in the Java Report Panel
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 rows
retrieved 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.
Retrieve duplicate rows query property
Setting the scope of analysis
3
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.
Setting the 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.
Building reports using the Java Report Panel 41
Building and editing queries in the Java Report Panel
3
Setting the scope of analysis
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:
None
DescriptionLevel
Only the objects that appear in the
Results Objects pane are included in
the query.
• 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.
42 Building reports using the Java Report Panel
For each object in the Result Objects
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.
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
1. Click the Show/Hide Scope of Analysis Pane button so that it appears
pressed in.
The Scope of Analysis panel appears at the bottom of the Result Objects
pane. The default scope of analysis is None. Each dimension in the Result
Objects pane appears in the Scope of Analysis pane.
2. Click the down arrow in the Scope of Analysis drop-down list box.
3. Select a level for the scope of analysis.
The level appears in the list box and the dimensions that are hierarchically
below each dimension in the Result Objects pane appear in the Scope
of Analysis pane.
Building and editing queries in the Java Report Panel
Query contexts
3
4. If you want to add selected dimensions to the scope of analysis or create
a custom scope of analysis, select dimensions in the Query Manager and
drag them across to the Scope of Analysis panel.
Query contexts
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.
Building reports using the Java Report Panel 43
Building and editing queries in the Java Report Panel
3
Query contexts
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.
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.
44 Building reports using the Java Report Panel
Building and editing queries in the Java Report Panel
Defining how contexts are used
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.
To reset contexts on query refresh
1. Make sure you are in Query View.
2. Click the Properties tab to display the query properties.
3. Select Reset contexts on refresh.
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.
Query contexts
3
To clear contexts
1. Make sure you are in Query View.
2. Click the Properties tab to display the query properties.
3. Click Clear Contexts
Building reports using the Java Report Panel 45
Building and editing queries in the Java Report Panel
Query contexts
3
46 Building reports using the Java Report Panel
Using combined queries
4
Using combined queries
4
Combined queries defined
Combined queries defined
A combined query is a group of queries that work together to return a single
result. All queries in the group must be based on the same universe.
Types of combined query
You can combine queries in three relationships:
• union
• intersection
• minus
In a union combination, Web Intelligence takes the all the data from both
queries, eliminates duplicate rows, and builds a combined data set.
In an intersection combination, Web Intelligence returns the data that is
common to both queries.
In a minus combination, Web Intelligence returns the data in the first query
that does not appear in the second.
Example: Union, intersect and minus queries
In this example you have two queries that return lists of countries as shown
in the following table:
Depending on the type of combined query, Web Intelligence returns the
following values:
48 Building reports using the Java Report Panel
ValuesQuery
US; UK; Germany; FranceQuery 1
US; SpainQuery 2
ValuesCombination type
US; UK; Germany; France;SpainUNION
Using combined queries
What can you do with combined queries?
ValuesCombination type
US; SpainINTERSECTION
UK; Germany; FranceMINUS
What can you do with combined queries?
Combined queries allow you to answer questions that are otherwise difficult
or impossible to frame in a single Web Intelligence query.
Example: Return a data set using a combined query
The Island Resorts Marketing sample universe contains the dimension
Year, which is associated with guests who have already stayed in a resort,
and Reservation Year, which is associated guests who have reserved to
stay in the future. Because of the structure of the database and universe,
these objects are incompatible, which means that you cannot include them
in the same block in a report.
4
What if you want to return a single list of years that includes those years
where more than n guests stayed in a resort and those years where more
than n guests reserved to stay in a resort? You can do this using a combined
query, as follows:
ReturnsQuery
Query 1
UNION
Query 2
The union between these two queries returns the list of years that you want.
Building reports using the Java Report Panel 49
Years where more than n guests
stayed in a resort
Years where more than n guests reserved to stay in a resort
Using combined queries
4
How does Web Intelligence generate combined queries?
How does Web Intelligence generate
combined queries?
If your database supports the type of combination in your query, combined
queries work at the database level: they alter the query that Web Intelligence
submits to the database. They do so by generating SQL (Structured Query
Language) queries containing UNION, INTERSECT and MINUS operators.
Note: SQL is the standard query language of relational databases, although
each database has its own dialect.
If your database does not support the type of combination in your query,
Web Intelligence performs the query at the report level by generating multiple
SQL queries whose data it resolves after retrieval from the database.
To build a combined query
1. Create an initial query in the Query Panel.
2. Click Add a combined query (to the right of the SQL) on the toolbar.
Web Intelligence adds a copy of the initial query to the data provider. The
second query has the following characteristics:
• It contains the same report objects as the original query.
• It does not contain the filters defined on the original query.
• It is combined with the original query in a UNION relationship.
3. To switch to a query, click Combined Query n in the bottom left pane of
the Query Panel.
The individual queries in the combined queries are named Combined
Query n.
4. To delete a query, right-click the Combined Query n you want to delete,
then select Remove on the menu.
5. To change the combination type, double-click on the operator. The
operator moves through the sequence UNION, INTERSECTION, MINUS.
6. Build each query within the combined query as you build any normal Web
Intelligence query.
7. Click Run Query.
50 Building reports using the Java Report Panel
Using combined queries
Combined query structure
Combined query structure
The queries within a combined query must return the same number of objects
of the same data type and the objects must be in the same order. You cannot
combine queries when the number of objects in the query results and the
data types of those objects are not identical. For example, you cannot
combine a query than returns Year with a query that returns Year and
Revenue, and you cannot combine a query that returns Year with a query
that returns Revenue.
You must also pay attention to the semantics of your combined queries.
While it is possible to combine a query that returns Year with a query that
returns Region if both dimensions are of the same data type, the result - a
mixed list of years and regions - is unlikely to be meaningful. Typically, if
your first query contains a Year dimension, your second query also contains
a dimension that returns a list of years.
To return a list of years and reservation years based on the number of guests
4
This example describes the workflow for the query described in the
exampleReturn a data set using a combined query on page 49. You want
to build a query that returns a list of years consisting of years where more
than n guests stayed in a resort and years where more than n guests reserved
to stay in a resort.
1. Select the Island Resorts Marketing universe in the list of universes to
open the Query Panel.
2. Drag the Year object to the Result Objects pane.
3. Drag the Number of Guests object to the Query Filters pane and create
a report filter that restricts Number of Guests to greater than n.
4. Click Combined Query.
The Combined Query pane appears in the bottom left of the Query panel
with the two queries joined by UNION.
5. Click on the second query and remove the Year and Number of Guests
objects.
6. Drag the Reservation Year object to the Result Objects pane.
Building reports using the Java Report Panel 51
Using combined queries
4
Combined query precedence
7. Drag the Future Guests object to the Query Filters pane and create a
report filter that restricts the future guests to greater than n.
8. Click Run Query.
The query returns the combined list of years and reservation years.
Combined query precedence
It is important to understand the order in which Web Intelligence executes
query combinations in a combined query. The order of execution is crucial
in determining the final result.
In the simplest form of combined query you combine two or more queries in
a relationship as follows:
Query 1
Query 2INTERSECTION
Query 3
In such a case, Web Intelligence first finds the set of data that represents
the union/intersection/minus between Combined Query n and Combined
Query n + 1, then finds the union/intersection/minus between that data set
and the data returned by Combined Query n + 2. Web Intelligence continues
in this way through all the queries in the relationship. This gives the following
result for the above example:
52 Building reports using the Java Report Panel
DataQuery
US; UK; France; GermanyQuery 1
US; France; FinlandQuery 2
US; FranceINTERSECTION of 1 and 2
US; SpainQuery 3
USFinal INTERSECTION
Multiple combined queries
You can combine multiple queries in complex relationships to determine the
order of execution, as in the following example:
MINUS
Combined Query 1
Using combined queries
Combined query precedence
4
INTERSEC
TION
Combined
Query 3
Web Intelligence processes query groups from right to left as they appear
in the Query Panel, and from top to bottom within each group.
(Higher-precedence groups, such as the MINUS group in the above example,
appear indented to the right in the Query Panel.) In the above query Web
Intelligence first determines the result of the minus combination then finds
the intersection of this result with the result of Combined Query 3 as shown
in the following table:
Combined Query 2
ResultQuery
US; UK; Spain; GermanyQuery 1
GermanyQuery 2
US; UK; SpainQuery 1 MINUS Query 2
US; Spain; FinlandQuery 3
(Query 1 MINUS Query 2)
INTERSECTION
Query 3
US; Spain
Building reports using the Java Report Panel 53
Using combined queries
4
Combined query precedence
Note: If your database directly supports the type of combined query you
wish to execute, Web Intelligence generates SQL containing combination
operators. In this case the order of precedence depends on the order of
precedence defined in the database. See your Web Intelligence administrator
for more details.
To set the order of precedence of combined queries in the Java Report Panel
1. Build the first query in the Query Panel.
2. Click Combined Query.
3. Repeat these steps until you have built all the component queries.
4. To increase the precedence of a pair of queries, drag and drop a query
on to the query with which you want to associate it in the
higher-precedence pair.
Web Intelligence indents the source and target queries in the
drag-and-drop operation and combines them by default in a UNION.
5. Continue adding queries to the higher-precedence group by dragging
and dropping them on to the space between any two queries already in
the group.
6. To create further higher-precedence groups within an existing
higher-precedence group, repeat the previous two steps.
7. Double-click the combination operators of all the groups in the query to
change them as required.
8. Click Run Query.
54 Building reports using the Java Report Panel
Filtering queries
5
Filtering queries
5
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:
56 Building reports using the Java Report Panel
Filtering queries
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
5
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 reports using the Java Report Panel 57
Filtering queries
5
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 mix 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.
Quick filters
Quick filters allow you to quickly define the values you want to retrieve for a
specific object without launching the Filter Editor. By default, Quick filters
use the Equal to operator if you select a single value or the In list operator
if you select multiple values.
For example:
• If you select the [Payment Status] dimension and the value “unpaid” you
create the filter: [Payment Status] Equal to “unpaid”
58 Building reports using the Java Report Panel
• If you select the [Country] dimension and the values US, Japan, Germany,
you create the filter: [Country] In list “US;Japan;Germany”
To create a quick filter in the Query Panel
1. Select the object you want to filter in the Result Objects pane.
2. Click Add quick filter at the top right corner of the Result Objects pane
to display the "Add Quick Filter" dialog box.
3. Select or type the values you want to filter and click OK.
Web Intelligence adds the filter to the Query Filters pane.
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
Filtering queries
Types of query filter
5
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.
To add and remove custom query filters
1. Select the object you want to filter and drag it to the Query Filters pane.
The query filter appears in outline in the Query Filters pane.
2. Click the arrow next to the default operator (In List) and select the query
operator from the list of operators.
3. Click the arrow on the right of the query filter and select Constant,
Value(s) from List or Object.
4. Type/select the constant, list of values or object you want to include in
the filter.
5. To remove the filter, select it and click the Delete key, or click Remove
at the top right corner of the Query Filters pane. To remove all filters,
click Remove All at the top right corner of the Query Filters pane.
Building reports using the Java Report Panel 59
Filtering queries
5
Types of query filter
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"
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.
60 Building reports using the Java Report Panel
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".
Filtering queries
Types of query filter
5
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".
Building reports using the Java Report Panel 61
Filtering queries
5
Types of query filter
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".
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'.
62 Building reports using the Java Report Panel
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.
Filtering queries
Types of query filter
5
Related Topics
• Not In List operator on page 177
• Different From operator on page 175
Combining query filters
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.
Building reports using the Java Report Panel 63
Filtering queries
5
Types of query filter
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.
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.
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.
Sales Floor Size Group Greater than or equal to:
4000
Sales Revenue Less than 1,500,000
64 Building reports using the Java Report Panel
Filtering queries
Types of query filter
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
5
AND
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.
To nest query filters
1. Drag and drop a report object onto an existing query filter.
A query filter outline on the report object appears in a nested AND
relationship with the existing query filter.
2. Define the new query filter.
OR
Quarter Equal To Q4
Revenue Greater Than
1000000
Building reports using the Java Report Panel 65
Filtering queries
Types of query filter
5
66 Building reports using the Java Report Panel
Filtering data with subqueries
6
Filtering data with subqueries
6
What is a subquery?
What is a subquery?
A subquery is a more flexible kind of query filter that allows you to restrict
values in more sophisticated ways than is possible with a ordinary query
filters.
Subqueries are more powerful than ordinary query filters for the following
reasons:
• They allow you to compare the values of the object whose values are
used to restrict the query with values from other objects.
• They allow you to restrict the values returned by the subquery with a
WHERE clause.
What can you do with subqueries?
Subqueries allow you to pose complex questions that are difficult or
impossible to formulate with simple query filters. For example: what is the
list of customers and their associated revenue where the customer purchased
a service that had previously been reserved (by any customer) in Q1 of 2003?
How do subqueries work?
Subqueries work by modifying the SQL that Web Intelligence generates to
retrieve the query data. Web Intelligence generates SQL containining a
subquery that restricts the data returned by an outer query. For more
information on SQL subqueries, see any book on SQL.
Note: SQL is the query language supported by all relational databases
(RDBMS), although each database has its own syntax.
To build a subquery
1. Add the objects that you want to appear in the query to the Result Objects
pane.
2. Select the object in the Result Objects pane that you want to filter with a
subquery and click Add a subquery at the top right of the Query Filters
pane.
68 Building reports using the Java Report Panel
Filtering data with subqueries
To build a subquery
The subquery outline appears in the Query Filters pane. By default the
object you selected appears as the Filter object and Filter By object.
3. To add a WHERE condition to the subquery, drag a report object to the
area of the subquery below the Drop an object here boxes.
4. To add a WHERE condition to the subquery, drag a report object to the
area of the subquery below the Drop an object here boxes.
You can use an existing subquery or standard query filter as a WHERE
condition in a subquery. To do so, drag and drop the existing filter or
subquery to the area of the subquery below the Drop an object here
boxes. To copy rather than move the existing filter to the WHERE
condition, hold down the Control key while dragging and dropping. In this
case the existing filter remains in its initial place and becomes part of the
WHERE condition of the subquery.
5. Select the operator and values used to filter the object in the WHERE
condition.
6. Click Subquery to add an additional subquery to the query filter.
In addition to linking subqueries in AND or OR relationships, you can nest
them (create subqueries within subqueries) by dragging an existing
subquery to the area beneath the Drop an object here boxes. In this
case the inner subquery becomes part of the WHERE condition of the
outer subquery. To copy rather than move the subquery to the WHERE
condition, hold down the Control key while dragging and dropping. In this
case the second subquery remains at the same level as the first, and
becomes part of the WHERE clause of the first.
By default the two subqueries are linked in an AND relationship. Click the
AND operator to toggle between AND and OR.
6
7. To nest a subquery (create a subquery within a subquery), drag an existing
subquery to the area beneath the Drop an object here boxes.
To copy rather than move the subquery to the WHERE condition, hold
down the Control key while dragging and dropping. In this case the second
subquery remains at the same level as the first, and becomes part of the
WHERE clause of the first
The inner subquery becomes part of the WHERE condition of the outer
subquery.
Related Topics
• Subquery parameters on page 70
Building reports using the Java Report Panel 69
Filtering data with subqueries
6
Subquery parameters
To find out which customers bought a service that had previously been reserved in Q1 of 2003, and how much revenue have they generated
1. Drag the Customer and Revenue objects to the Result Objects pane of
the Query Panel.
2. Select the Service object.
3. Click Subquery.
The subquery outline appears in the Query Filters pane.
Note: The selected object appears in both boxes in the subquery outline.
You often use the same object in both boxes, although this is not required.
If the objects do not return any common values, the subquery returns no
values, and the query therefore returns no values.
4. Drag the Reservation Year object to the area of the subquery outline
beneath the Service objects.
Web Intelligence adds a WHERE condition on the Reservation Year
object.
5. Set the Reservation Year condition operator to Equal To.
6. Type ‘FY2003’ in the Type a constant box.
7. Drag the Reservation Quarter object to the area of the subquery outline
beneath the Service objects.
Web Intelligence adds the Reservation Quarter object to the WHERE
condition.
8. Set the Reservation Quarter condition operator to Equal To.
9. Type ‘Q1’ in the Type a constant box.
10. Click Run Query to run the query.
Subquery parameters
A subquery or set of subqueries contains the following parameters:
70 Building reports using the Java Report Panel
Filter Object(s)
Filter By Object(s)
Operator
Filtering data with subqueries
Subquery parameters
DescriptionParameter
The object whose values are used to
filter the result objects.
You can include more than one Filter
Object. If you do, Web Intelligence
concatenates the values of the objects
you specify.
The object that determines which Filter
Object values the subquery returns.
You can include more than one Filter
By object. If you do, Web Intelligence
concatenates the values of the objects
you specify.
The operator that specifies the relationship between the Filter object and the
Filter By object.
Because of database restrictions you
cannot use certain combinations of
operators and Filter By objects together. For example, if you use the Equal
To operator with a Filter By object that
returns multiple values, the database
rejects the SQL because this type of
subquery requires the Filter By object
to return one value only.
In cases where the generated SQL is
rejected by the database, you see an
error message showing the error description returned by the database
6
WHERE condition (optional)
An additional condition that constrains
the list of values of the Filter By object.
You can use ordinary report objects,
predefined conditions or existing query
filters (including subqueries) in the
WHERE condition.
Building reports using the Java Report Panel 71
Filtering data with subqueries
6
Subquery parameters
Relationship operator
DescriptionParameter
If there is more than one subquery,
determines the relationship between
the subqueries.
AND - the conditions in all of the subqueries must be satisfied.
OR - the conditions in any one of the
subqueries must be satisfied.
72 Building reports using the Java Report Panel
Ranking data using database ranking
7
Ranking data using database ranking
7
What is database ranking?
What is database ranking?
When you rank data you sort and filter it according to ranking criteria. Web
Intelligence allows you to return unranked data from a database, then rank
it in Web Intelligence. (For more information, see Ranking data on page 124.)
A database ranking allows you to specify a ranking at the query and database
level so that the data returned to Web Intelligence by the query is already
ranked.
Database rankings allow you to answer questions like “Return the top 3
customers based on the revenue they generated for each year” at the query
level, without the need to return data that falls outside the ranking to Web
Intelligence and then filter it using a Web Intelligence ranking.
Database ranking has the following advantages:
• Ranking data can be processing-intensive. By ranking at the database
level you allow the server, which is typically far more powerful than the
client machine, to perform this processing.
• Pre-ranking data reduces the amount of data retrieved across the network
and stored in Web Intelligence.
A database ranking works by modifying the SQL that Web Intelligence
generates to retrieve the query data. If your database supports ranking, Web
Intelligence generates SQL to rank the data. Web Intelligence uses the
SQL-99 Rank function in ranking SQL. (SQL is the query language supported
by all relational databases (RDBMS), although each database has its own
syntax.)
Note: You can perform a database ranking only if your database supports
it. If this is not the case, the Add a database ranking button is disabled on
the Query Panel toolbar. Databases that support ranking are Oracle, DB2,
Terradata and Redbrick.
Database ranking parameters
A database ranking appears as follows in the Query Filters pane of the Query
Panel:
74 Building reports using the Java Report Panel
Ranking data using database ranking
Database ranking parameters
The following table describes the parameters from left to right in the ranking:
DescriptionParameter
Ranking order.
Top/Bottom
• Top - ranks in descending order.
• Bottom - ranks in ascending order.
7
Number of records
Ranking dimension
Based on
For Each (optional)
The number of records to return in the
ranking. For example, the top 10.
The dimension used in the ranking. For
example, if the dimension is Region
and the ranking is Top 10, the ranking
returns the top 10 regions.
The measure by which the ranking dimension is ranked. For example, if the
measure is Revenue and the dimension is Region, Web Intelligence ranks
regions by the amount of revenue they
generate
Dimension that specifies additional
calculation context for the ranking. For
example, if the ranking dimension is
Region, the measure is Revenue and
the For Each dimension is Country,
Web Intelligence ranks regions by revenue within each country.
Building reports using the Java Report Panel 75
Ranking data using database ranking
7
To create a database ranking
WHERE condition (optional)
To create a database ranking
1. Add the objects that you want to appear in your query to the Result Objects
pane of the Query Panel.
2. Select the dimension that you want to rank by.
3. Click Add a database ranking on the toolbar.
The ranking outline appears in the Query Filter pane. The dimension you
selected appears as the ranking dimension and the first measure in the
query appears as the ranking measure in the Based on box.
DescriptionParameter
Additional restriction on the values returned in the ranking that appears below the other parameters. For example,
a ranking of regions with a condition
that restricts Country to “USA” ranks
only those regions in the USA.
Note: The Add a database ranking button is disabled if your database
does not support ranking.
4. Select the ranking direction (Top or bottom.)
5. Type the number of records you want the ranking to return in the box next
to Top/Bottom.
You can specify a prompt instead of a constant by clicking on the arrow
next to the number. When you select a prompt the user must enter the
ranking number when the query is run.
6. Drag the dimension that provides the calculation context for the measure
to the For Each box.
This dimension is optional. To display the For Each box, click the arrow
to the right of the Based on measure.
7. Drag any dimensions that you want to include in the WHERE restriction
to the area at the bottom of the ranking. (For more information on the
WHERE restriction, see the explanation in What is a subquery? on
page 68.)
8. Click Run Query.
76 Building reports using the Java Report Panel
Ranking data using database ranking
To create a database ranking
Example: Create a report that returns the top 10 employees based on
salary, and calculated by department.
See To create a report that returns the top 10 employees based on salary,
and calculated by department on page 77.
To create a report that returns the top 10 employees based on salary, and calculated by department
1. Drag the Department, Employee Name, and Salary objects to the Report
Objects pane within the Query Panel.
2. Select the Employee Name object.
3. Click Add a database ranking on the toolbar.
Web Intelligence adds an outline database rank to the Query Filters pane.
The ranking dimension is Employee Name and the ranking measure is
Salary.
4. Set the ranking direction to Top.
5. Set the ranking number to 10.
6. Click the arrow next to the Based on measure if the For Each box is not
already visible.
7. Set the For Each dimension to Department by dragging and dropping the
dimension.
The ranking now looks like this:
7
8. Click Run Query.
Building reports using the Java Report Panel 77
Ranking data using database ranking
To create a database ranking
7
78 Building reports using the Java Report Panel
Filtering data using prompts
8
Filtering data using prompts
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
Resort_Country.country = @prompt('Enter Country:','A',
'Resort\Country', Mono,Free,Persistent,,User:0)
80 Building reports using the Java Report Panel
or as
Resort_country.country In ('UK')
Prompts can be optional. The user is not obliged to specfy a value for an
optional prompt. If no value is specified, Web Intelligence ignores the prompt.
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
Filtering data using prompts
Cascading prompts
8
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.
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 reports using the Java Report Panel 81
Filtering data using prompts
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"
82 Building reports using the Java Report Panel
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
Filtering data using prompts
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 reports using the Java Report Panel 83
Filtering data using prompts
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".
84 Building reports using the Java Report Panel
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
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 177
• Different From operator on page 175
Building reports using the Java Report Panel 85
Filtering data using prompts
8
To create a prompt
To create a prompt
1. Make sure the Query Panel is open.
2. From the Data tab, drag the object on which you want to apply a prompt
and drop it onto the Query Filters pane.
The query filter appears in outline in the Query Filters pane.
3. Click the arrow at the right of the Query Filter and select Prompt from
the menu.
4. Type the prompt text in the text box.
5. Click the icon next to the text box and use the dialog box that appears to
set the prompt properties.
• 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
• If the document contains multiple data providers, and there is already
a prompt that includes (1) objects with the same data type, (2)
operators of the same operator type, and (3) that uses the same prompt
text as the new prompt, Web Intelligence displays a warning to tell
you that the two prompts will be merged. This means that whenever
all the data providers are refreshed, a single prompt message will
appear for the two prompts.
6. Select Optional prompt to make the prompt optional.
7. To delete a prompt, right-click it and select Remove from the menu.
The prompt appears at each document refresh.
Related Topics
• Defining how prompts display on page 87
• Merged prompts on page 81
To remove a prompt
• Select the prompt and then click the Delete key.
86 Building reports using the Java Report Panel
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:
Filtering data using prompts
Defining how prompts display
8
If you want the prompt
to display...
the list of values associated with the filtered dimension, measure, or detail,
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)),
want to view all the values for the object and
then select from those
values
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
then...(useful when you...)
leave the option selected
by default: Prompt with
List of Values
select the option: Keep
last values selected
select the option: Set default value(s)
a list of values from which
users select a value(s),
prevent users from typing
a value that might not exist on the database
select the option: Select
only from List
To make the prompt optional, select Optional prompt. The user is not obliged
to specify a value for the prompt. In this case, Web Intelligence ignores the
prompt.
Building reports using the Java Report Panel 87
Filtering data using prompts
8
Combining prompts
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
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:
• 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
1. Click the Properties tab in the Query Panel.
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.
88 Building reports using the Java Report Panel
Merging prompts
When a document contains multiple queries, Web Intelligence merges any
prompts that include objects with the same data type, operators of the same
operator type, and that use the same prompt text. When all the queries are
refreshed, a single prompt message appears for such prompts.
Filtering data using prompts
Merging prompts
8
Building reports using the Java Report Panel 89
Filtering data using prompts
Merging prompts
8
90 Building reports using the Java Report Panel
The Java Report Panel reporting interface
9
The Java Report Panel reporting interface
9
By default, the Java Report Panel report interface appears as follows:
• Report tabs - a collection of four tabs at the left of the screen that you
use to work with reports.
DescriptionTab
Data
Templates
Map
Properties
• Toolbars
Main
Formatting
Displays the universe objects, formulas and variables that
can be included in the report.
Displays the tables, charts and cells that can be included
in the report.
Displays a hierarchical map of the report components (for
examples tables, cells, sections, filters).
Displays the editable properties used to configure the appearance and behavior of the report. The list of properties
varies depending on the report component selected.
DescriptionToolbar
You use the main toolbar to switch between query view
and report view, to save and print documents, and to activate data tracking and drill mode, and to configure the
report interface.
The main toolbar is always visible. All the other toolbars
can be hidden.
You use the formatting toolbar to format text and report
objects.
Reporting
Page Navigation
You use the report toolbar to add reporting features (for
example filters, variables, rankings, calculations).
You use the page navigation toolbar to navigate through
the pages in a report.
• Reports - by default the reports contained in the Web Intelligence
document appear to the right of the report tabs and below the toolbars.
Each report appears on its own
92 Building reports using the Java Report Panel
The Java Report Panel reporting interface
You can configure the report interface (for example by hiding toolbars or
changing the position of the report tabs) by selecting options from the
Configure View menu on the main toolbar.
9
Building reports using the Java Report Panel 93
The Java Report Panel reporting interface
9
94 Building reports using the Java Report Panel
Web Intelligence viewing modes
10
Web Intelligence viewing modes
10
To switch between viewing modes
To switch between viewing modes
You can view Web Intelligence reports in different modes depending on how
you want to work with data and how you want the data to appear.
1. In Web Intelligence Interactive, select the report tab of the report you
want to view.
2. Click the arrow next to the View button on the main toolbar above the
report.
3. Select the viewing mode.
Web Intelligence Interactive displays the report in the selected viewing
mode.
4. In the Java Report Panel, use Switch Page/Quick Display on the
Reporting toolbar to alternate between Page mode and Quick Display
mode.
Draft mode
Draft mode displays just the tables, reports, and free standing cells in reports.
Use Draft mode when you want to focus on analyzing results, add calculations
or formulas, or add breaks or sorts to tables to organize results.
Page mode
Page mode displays the page layout of reports, including page margins,
headers, and footers.
Use Page mode when you want to fine-tune the formatting of tables and
charts and the layout of report pages.
PDF mode
PDF mode displays the report in PDF format.
96 Building reports using the Java Report Panel
Use PDF mode when you want to view the report in PDF format or print the
report from within Adobe® Acrobat® Reader®.
Quick Display mode
Quick Display mode is the default display mode in Web Intelligence. It is a
pagination mode that is based on the data, rather than the physical size of
report pages. Quick Display mode displays just the tables, reports, and free
standing cells in reports and displays a maximum number of records vertically
and horizontally, depending on the Quick Display settings. Quick Display
mode also specifies the minimum page width and height and the amount of
padding around the edges of the report.
Because Quick display mode retricts the number of horizontal and vertical
rows, a report might not contain all possible data.
Use Quick Display mode when you want to focus on analyzing results, add
calculations or formulas, or add breaks or sorts to tables to organize results.
The Quick Display mode properties are configurable either by your
administrator in the CMC, or directly in Web Intelligence.
Web Intelligence viewing modes
To switch between viewing modes
10
Where configuredProperty
CMCMaximum vertical records
CMCMaximum horizontal records
CMCMinimum page width
CMCMinimum page height
CMCRight padding
CMCBottom padding
Web IntelligenceVertical records per page
Web IntelligenceHorizontal records per page
Building reports using the Java Report Panel 97
Web Intelligence viewing modes
10
To switch between viewing modes
To change Quick Display mode settings in Web Intelligence
You can change the number of horizontal and vertical records per page in
Quick Display mode in Web Intelligence.
1. In the Java Report Panel, set the Page Content > Vertical Records per
page and Page Content > Horizontal Records per page properties.
2. In Web Intelligence Interactive, right-click the report background, select
Format Report to display the "Format Report" dialog box, and set the
Number of vertical records per page and Number of horizontal
records per page in the General tab.
3. As a shortcut, vou can also use the icons on the Page Navigation toolbar
(Java Report Panel) or main toolbar (Web Intelligence Interactive) to
increase or decrease these settings by increments of 50.
To select Enhanced Viewing mode
Your administrator can define minimum page margins, headers and footers
that are applied only when you view reports onscreen. This means that the
maximum amount of information on report pages is visible when you view
reports via your computer screen. To apply the page definition set up by your
administrator you need to select Enhanced Viewing mode.
1. Select Document > Properties (in Web Intelligence Interactive) or
right-click a report and select Document Properties (in the Java Report
Panel).
2. Select Enhanced viewing mode.
98 Building reports using the Java Report Panel
Using alerters to highlight results
11
Using alerters to highlight results
11
Creating, using and removing alerters
Creating, using and removing alerters
Alerters defined
Alerters enable you to highlight results that meet or fail specific business
targets. You can create a simple alerter to highlight particularly high or low
results with a specific color or with a text comment, such as “High Performer.”
An alerter can contain up to six conditions. This enables you to highlight
information that meets multiple business criteria. For example, you can define
an alerter that highlights high revenue for a specific business activity or
customer sector.
You can apply alerters to table body cells (by column or row), to section cells,
to header cells, and to free-standing cells. However, you cannot apply alerters
to entire tables or forms, or to charts.
Business Objects officially supports up to 30 alerters in a Web Intelligence
document. You can apply those alerters to a maximum of 20 table columns
or rows, free-standing cells, or section cells on the reports.
Business Objects officially supports up to 10 different alerters on a single
table column or row, free-standing cell, or section cell.
You can define alerters to activate the following formatting changes to the
selected table columns/rows or cells:
• text color, size and style
• cell border colors and style
• cell background display – specific colors, images, or hyperlinks to web
pages
You can also define alerters that display a text or formula, an image, or a
hyperlink. In this case, the results that meet the condition defined in the
alerter will be replaced by the text for formula.
If you define an alerter with multiple conditions, each condition generates
the same formatting changes. To generate different formatting changes for
different conditions, you must use sub-alerters.
Alerters are dynamic. This means that when you refresh reports with the
latest data on the database, the alerters highlight the new results accordingly.
100 Building reports using the Java Report Panel
Loading...