AITS – Decision Support –

University Administration

Web Intelligence
Reporting Basics
HTML Version
 Page |2

Appropriate Use and Security of Confidential and Sensitive Information

Due to the integrated nature of the various Human Resources, Finance and Student modules in Banner
and the reporting information in the Enterprise Data Warehouse (EDW), you may have access to
information beyond what you need to perform your assigned duties. Your access to Banner and the
EDW has been granted based on business need, and it is your responsibility to ensure the information
you access is used appropriately.

Here are some reminders of good data stewardship to help you carry out your responsibility:

• Do not share your passwords or store them in an unsecured manner. Do not leave your
workstation unattended while logged on to administrative information systems. You are
responsible for any activity that occurs using your logon id.
• Do not share confidential and sensitive information with anyone, including colleagues, unless
there is a business reason.
• Retrieve printed reports quickly, and do not leave the reports lying around in plain view.
• Secure reports containing confidential and sensitive information (e.g., FERPA, EEO or HIPAA
protected data).
• Always use the Confidentiality Indicator object/column when reporting on Student information
for public distribution to ensure that FERPA sensitive information is excluded from your report
where appropriate.
• Do not include UIN or SSN whenever possible. This practice will mitigate the risk that sensitive
personal information may be improperly distributed.
• Shred the reports in a timely manner if they contain confidential or sensitive information.
• Be aware of Human Subjects data confidentiality. Specifically, Finance Accounts Payable data
contains confidential information on payments to human subjects along with information on
research topics. Retrieve reports quickly, shred unneeded paper reports and only provide
appropriate access to the information.
• Please note that the security state of the data 'travels' with the data. That is, data that is highly
restricted in a system like Banner or the Data Warehouse is just as confidential when it is used
downstream in a report, backend database, or local application. You are responsible for
maintaining the confidentiality of data in reports or systems you create and manage.
 Page |3

Table of Contents
About this Course ..............................................................................................................................6
Objective ................................................................................................................................................... 6
Instructor Led Course................................................................................................................................ 6
Training Data ............................................................................................................................................. 6
Training Accounts...................................................................................................................................... 6
Chapter 1: Creating a Basic Report......................................................................................................7
SAP BusinessObjects Products .............................................................................................................. 7
EDDIE / BI Launchpad............................................................................................................................ 7
Web Intelligence ................................................................................................................................... 7
Terminology .......................................................................................................................................... 8
Logging into Web Intelligence from EDDIE ............................................................................................... 9
Web Intelligence Settings ....................................................................................................................... 11
Creating a New Document ...................................................................................................................... 13
Query Panel............................................................................................................................................. 15
Sizing the window ............................................................................................................................... 15
Query window panels ......................................................................................................................... 15
Query Panel toolbar ............................................................................................................................ 16
Object Types........................................................................................................................................ 17
Finding Objects.................................................................................................................................... 18
Adding Objects to the Result Objects panel ........................................................................................... 18
Adding Objects to the Query Filters panel .............................................................................................. 20
Running the Query and Entering Prompt Values .................................................................................... 22
Report Design Window ........................................................................................................................... 24
Edit Query and Refresh Data............................................................................................................... 24
Page Navigation .................................................................................................................................. 24
Saving a Web Intelligence Document ................................................................................................. 25
Exercise 1: Creating a Basic Report ...................................................................................................26
Review ..................................................................................................................................................... 26
Exercise ................................................................................................................................................... 26
Chapter 2: Editing an Existing Query / User Defined Query Filters......................................................28
Adding Objects to an Existing Query....................................................................................................... 28
 Page |4

Adding a New Object to a Table ............................................................................................................. 29

User Defined Query Filters...................................................................................................................... 32
Grouping Filters Using the AND / OR Operator ...................................................................................... 33
No Data to Retrieve ................................................................................................................................ 36
Exercise 2: User Defined Query Filters ..............................................................................................37
Review ..................................................................................................................................................... 37
Exercise ................................................................................................................................................... 37
Chapter 3: Prompts ..........................................................................................................................38
Building a Prompt ................................................................................................................................... 38
Prompt Properties window ................................................................................................................. 39
Refresh Data / Change Prompt Values ................................................................................................... 40
Exercise 3: Prompts ..........................................................................................................................43
Review ..................................................................................................................................................... 43
Exercise ................................................................................................................................................... 43
Chapter 4: Formatting ......................................................................................................................44
Report Formatting .................................................................................................................................. 44
Print Layout ......................................................................................................................................... 44
Report Layout Settings ........................................................................................................................ 44
Table Formatting ..................................................................................................................................... 46
Deleting a Column ............................................................................................................................... 46
Rearranging Columns .......................................................................................................................... 46
Resizing Columns to Autofit Widths ................................................................................................... 47
Wrap Text............................................................................................................................................ 49
Exercise 4: Formatting ......................................................................................................................51
Review ..................................................................................................................................................... 51
Exercise ................................................................................................................................................... 51
Chapter 5: Report Analysis Features .................................................................................................52
Sorting Data in a Table ............................................................................................................................ 52
Duplicating and Renaming a Report ....................................................................................................... 53
Applying a Report Filter .......................................................................................................................... 54
Managing Report Filters.......................................................................................................................... 55
Inserting a Break ..................................................................................................................................... 56
 Page |5

Inserting Calculations .............................................................................................................................. 57

Inserting a Sum ................................................................................................................................... 58
Exercise 5: Report Analysis Features .................................................................................................60
Review ..................................................................................................................................................... 60
Exercise ................................................................................................................................................... 60
Chapter 6: Exporting Data and Sending .............................................................................................61
Exporting to Excel (.xlxs) ......................................................................................................................... 61
Sending to a BI Inbox .............................................................................................................................. 62
Exercise 6: Sending a Document .......................................................................................................64
Review ..................................................................................................................................................... 64
Exercise ................................................................................................................................................... 64
Appendix A: Additional Assistance ...................................................................................................65
Decision Support Website....................................................................................................................... 65
AITS Service Desk .................................................................................................................................... 65
SAP Web Intelligence Online Help .......................................................................................................... 65
Appendix B: Query Filter Operators ..................................................................................................66
Using In list .............................................................................................................................................. 67
Using wildcard characters ....................................................................................................................... 67
Wildcard Description .............................................................................................................................. 67
Appendix C: Query Filter Operand Options .......................................................................................68
 Page |6

About this Course

Objective
The objective of this course is to teach the basic functionality of the Web Intelligence editor for creating
reports from the EDW.

Instructor Led Course

This course is presented in a computer lab with an instructor. The instructor presents the information by
completing the examples in each chapter on the screen. The students in the class follow by doing the
same steps as the instructor. The students will then do exercises on their own.

Training Data
This course is designed to teach users how to use the Web Intelligence editor to create reports, not the
data. We will use the EDW - STU - Course Schedule universe for all the examples. This universe lists
current and past course schedules for the three campuses.

Training Accounts
To access the training data, we will login using training accounts. The account usernames are
DSTRAINING01 through DSTRAINING20, and they all have the same password: Foundationofin$ights

You will be assigned a training account to use during the training.

 Page |7

Chapter 1: Creating a Basic Report

SAP BusinessObjects Products
SAP BusinessObjects is a software suite of report creation, viewing, and distribution tools.
BusinessObjects is composed of applications that are accessible through the web or on your desktop
computer. The major components that you will interface with are:

• BI Launchpad via the website: EDDIE (Enterprise Data Delivery Information Environment)
• Web Intelligence Rich Client

EDDIE / BI Launchpad
EDDIE / BI Launchpad is a secured web based BusinessObjects Content Management System (CMS) that
allows you to create, store, retrieve, view, and print Web Intelligence reports from anywhere you have
internet access.

The University provides a number of pre-developed, standard reports which are accessed through EDDIE
by Folders or Categories. We also store our Solution Library reports in EDDIE’s Categories section. The
Solution Library reports serve as templates for commonly needed reports.

Web Intelligence
Web Intelligence is an easy to use report editor which allows you to create, edit, and analyze both
simple and complex business intelligence reports. There are two versions of Web Intelligence. An HTML
version is accessed through the EDDIE website and is run using a web browser. The Web Intelligence
Rich Client is a desktop application which can be accessed through EDDIE or from your computer
desktop. This training class will focus on the Web Intelligence Rich Client, but you are free to experiment
with the online HTML version.

The major difference between the two versions is that Web Intelligence Rich Client allows you to save
your documents locally and on network drives. The HTML version can only save reports to your Personal
Folder in EDDIE. The Web Intelligence Rich Client is a PC-based application and cannot be installed on
MAC computers. Since the HTML version is web-based, it can be used from MAC computers.
 Page |8

Terminology
The following are terms that will be used in this course:

Document

A document is the file created by Web Intelligence. A document can contain multiple queries and
multiple reports.

Query

A query is a request for data from a data source. It defines the data content of your report. The act of
building a query refers to selecting the data to include in your report and then applying filters to limit
the data returned.

Data Provider

The data returned by running a query is stored in the document as a data provider. There can be
multiple data providers in one document and one data provider can be used in multiple reports.

Report

A report is any formatted display of data from the data providers. A report can include one or more
blocks. There can be multiple reports in one document. They are represented as tabs at the top of the
screen.

Block

A block is a collection of data in a particular format. In Web Intelligence, the block types are table,
crosstab, and chart. Multiple blocks can appear in a single report and each block can display data from
one or more data providers.

Universe

A universe is a database interface which maps objects to fields in the database. The universe simplifies
report creation by eliminating the need to know the database structure. It also provides automatic joins
between database tables based on key values.
 Page |9

Logging into Web Intelligence from EDDIE

Web Intelligence Rich Client must first be installed on your computer before it can be used (see
Appendix C for installation instructions). Once installed, the editor must be started the first time from
the EDDIE website. This downloads a key to your computer. After the key is downloaded, the editor can
be started from your computer desktop.

1. Open the EDDIE login page: https://eddie.ds.uillinois.edu/

2. Click the Log in to EDDIE button.

3. Enter the Training Account username and password and click the LOG IN button.

Training Account username: DSTRAINING _ _

Training Account password: Foundationofin$ights

Note: When you log in as yourself, you will use your University NetID and Password to access EDDIE.

Note: you will be prompted to complete a DUO 2-factor Authentication challenge before being
logged into EDDIE. For this training, please choose ‘Send me a push’ option, which will go to the
instructor’s device to accept the challenge.
 P a g e | 10

The EDDIE Home Screen is displayed:

 P a g e | 11

Web Intelligence Settings

There are two different versions of the Web Intelligence editor. One is web-based (HTML version), and
one is desktop-based (Rich Client version). You can try the different versions and use whichever you
prefer. Since we will be using the HTML report editor, you should verify that the preferences are set
correctly.

1. Click the blue circle with your last and middle initials in the upper right corner and click Settings.

2. Click Application Preferences.

 P a g e | 12

3. Make sure the Web Client options are selected under Open in Reading mode and Open in Edit
mode.

4. Click Save at the bottom of the screen to save the changes.

 P a g e | 13

Creating a New Document

1. Click the Web Intelligence tile under Applications.

2. Select Universe on the Select a Data Source window and click OK.
 P a g e | 14

Note: A Universe is a database interface which maps objects to fields in the database. The universe
simplifies report creation by eliminating the need to know the database structure. It also provides
automatic joins between database tables based on key values.

3. Select the EDW – STU – Course Schedule universe and click OK.
The EDW – STU – Course Schedule universe is located in the Cat Sched Universes folder, or search by
name using the search bar at the top.

Note: the list of folders and universes visible is determined by the permissions set on the user’s
account. For example, if a user only has access to Financial data in the EDW, they will not see these
student-related universes.
 P a g e | 15

The Query Panel is displayed:

Query Panel
The Query Panel window is used to create the query that retrieves data from the database. The objects
and filters in the query window determine what data is included in the query results. The data returned
when the query is run is called a data provider, which is stored in the document and becomes the source
of the data displayed in the report.

Sizing the window

Click and hold the Query Panel top border to move the window. Drag the bottom right corner to enlarge
the window. Resize the panels by dragging the dots between panes.

Query window panels

• Universe outline: This panel displays the objects and filters contained in the universe. They are
organized by subject in folders called Classes. A class can be expanded or collapsed by clicking
the or to the left of the folder. You can also expand/collapse all using the buttons located
at the top of this panel.
 P a g e | 16

• Result Objects: This panel holds the objects to be included in the report. When building a new
query, each object added to the Result Objects panel is represented as a column in the initial
report table.
• Query Filters: This panel contains selection criteria for the query. The query filters determine
which rows from the database are included in the query results.
• Data Preview: Click Refresh to display a preview of the data that would be returned if the query
was run.

Query Panel toolbar

From left to right…

• Add Query: opens the Select a Data Source window to select a new data source for an
additional query
• Show/hide data outline panel: shows/hides the Result Objects panel
• Show/hide filters panel: shows/hides the Query Filters panel
• Show/hide Data Preview panel: shows/hides the Data Preview panel
• Show/hide Scope of Analysis panel: shows/hides the Scope of Analysis panel
• Add a combined Query: two queries from the same data source. Join results as a union,
intersection, or minus.
 P a g e | 17

• Show query properties: opens the Query Properties window. Allows users to rename queries,
set limits on rows/columns, adjust prompt order, etc.

• View query script: shows the SQL statement produced by the query.

Object Types
A universe is a collection of objects, which represent fields in a database table. Object names are
everyday terms which are easier to understand than the cryptic field names in the database.

Classes are logical groupings of objects to make objects easier to find. For example, all address
fields might be grouped together in one class.

• Dimension
o Dimension objects usually contain text or dates, such as Name, UIN, or Application Date.
However, some dimension objects may contain numeric data, such as Section
Enrollment.
o Dimension objects represent the basic structure of the data.

• Detail
o A detail object is always associated with a dimension object in a 1:1 relationship. For
example, Address Type PR is always associated with Address Description Permanent,
and vice versa.
 P a g e | 18

o A detail object provides additional information about the dimension object. This
additional information is typically descriptions, names, etc., of the codes being
represented. For example, College Name is the detail object associated with the College
Code dimension object.
• Measure
o Measure objects are numeric values that are the result of calculations.
o A measure’s value changes depending on the report context. For example, values
displayed for a Salary object differ if the report is for one pay period or for one year.
• Predefined Query Filters
o Predefined query filters are created for conditions that are complicated to create and/or
commonly used.
o Predefined query filters save time because they can simply be added rather than having
to create the filter yourself.

Finding Objects
Most of the universes you will be working with contain a large number of classes and objects, which can
make finding the objects difficult. You can look for objects by subject by expanding the related class
folders, or you can use the Filter option to search for objects that contain certain words or phrases.

Adding Objects to the Result Objects panel

Once you locate the object you are looking for, there are three ways to add the object to the Result
Objects panel:

• Double-click the object in the Universe outline.

• Drag and drop the object from the Universe outline into the Result Objects panel.

• Select the object in the Universe outline and click in the Result Objects panel.
 P a g e | 19

We will now create a simple query to pull records for Communications courses offered at UIUC during
the Fall 2019 semester.

1. Expand the Section Information folder.

2. Double-click the Course Subject Code object to add it to the Result Objects panel.

3. Locate the Course Number object.

4. Drag the Course Number object from the Universe outline to the right of the Course Subject Code
object in the Result Objects panel. Note the vertical blue bar showing the position of the object to
be added.

5. Locate the Section Number object and click in the Result Objects panel to add it.

6. Use the Filter box and to find and add the Section Enrollment object to the Result Objects
panel.
7. Find and add the Section Building Name object to the Result Objects panel. Note that the Section
Building Code object also gets added.
8. Find and add the Section Room Number object to the Result Objects panel.
 P a g e | 20

9. Select the Section Building Code object in the Result Objects panel and click the to remove it.
Your Result Objects panel should look like below.

Adding Objects to the Query Filters panel

Query filters add conditions to your query that limit the data returned from the database. Filters are
vital when running queries against a large database. There are two types of query filters: predefined and
user defined.

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, and
• Minimize the quantity of data returned to the document to optimize performance.

Predefined query filters are created for conditions that are complicated to create and/or commonly
used. Predefined query filters save time because they can just be added rather than having to create the
filter yourself.

As these filters are predefined, they can be found among the objects in the Universe outline, often in a
class called Conditions.

Once you locate the filter you are looking for, there are two ways to add the object to the Query
Filters panel:

• Double-click the filter in the Universe outline.

• Drag and drop the filter from the Universe outline into the Query Filters panel.

• Select the filter in the Universe outline and click in the Query Filters panel.

We will now add some predefined query filters to our simple query.
 P a g e | 21

1. Clear the Filter box by clicking the

2. Click the Collapse All icon to collapse the entire universe tree, then expand the universe row
by clicking . Next, expand the Conditions class so the Universe outline looks like below.

3. Double-click the Current Section Enrollment filter to add it to the Query Filters panel.
This filter limits the results to only the most recent value for the enrollment of a section.
4. Expand the Prompts class.
A prompt is a filter in the form of a question. Web Intelligence asks these questions to the user
running the query, at the time the query is being run.
 P a g e | 22

5. Drag the Select Term Code prompt from the Universe outline and drop it below the Current Section
Enrollment filter in the Query Filter panel. Note the horizontal blue bar showing the position of the
filter to be added.

6. Select the Select Course Subject Code prompt and click in the Query Filters panel to add it.
Your Query Filters panel should look like below.

Running the Query and Entering Prompt Values

1. Click the Run button in the bottom right corner of the Query Panel.
Since our query contains prompts, the Prompts window is displayed:
 P a g e | 23

2. By default, the first prompt – Select 6 digit Term Code, in this case – is selected on the left, and a list
of values for the Term Code object is generated on the right.

Note that the first time the list of values for an object is displayed, all values are retrieved from the
database and stored in a local file on your computer, which can take some time. However, the next
time you see the same list, the values will be read more quickly from the local file.

There are three methods for selecting a prompt value:

• Scroll through and select the value from the list of values generated,
• Search for values in the list by typing something into the text box and selecting the first
option, or

• Manually enter a value into the text box and select the second option.

Note that this method can result in no results being returned if the value entered does not
exactly match a value for that object in the database.

3. Select 120198 from the list of values.

4. Select the second prompt – Select Course Subject Code – and allow the list of values to populate.
5. Type CMN into the search box and select the first option. When the value is displayed in the list,
select it.
By answering both mandatory prompts, Web Intelligence will now allow us to run the query.
6. Click in the bottom right corner of the window to run the query.
 P a g e | 24

Report Design Window

Once your query has run, the report will be displayed in the Report Design window. This window has a
toolbar across the top, a main Report panel, and two panels on the right that contain the various
features available in Web Intelligence.

Edit Query and Refresh Data

Edit Query - Opens the Query Panel to make changes to the query.

Refresh Data - Refreshes the report data and/or changes prompt values.

Page Navigation
By default, Web Intelligence paginates results every 100 rows. The vertical and horizontal scroll bars will
allow you to navigate within the visible page.
 P a g e | 25

To see all results, you may need to use the Navigate section on the toolbar, located at the top of the
Report Design window.

Saving a Web Intelligence Document

Web Intelligence autosaves documents to the ~WebIntelligence folder every 10 minutes, 11and they will
be held in that folder for 24 hours. To be on the safe side, we recommend users manually save their
work often, as Web Intelligence will automatically time users out of their session after 60 minutes of
inactivity.

1. In the File section, click Save and select Save As

2. The Save Document window is displayed:

3. Click My Folders.
4. Name the document by typing Section Enrollment – [Month] [Year] in the File Name box
5. The Options tab allows users to add a description of the document and set the document to
automatically refresh the data providers upon opening the document.
6. Click in the bottom right corner of the window to save the document.
 P a g e | 26

Exercise 1: Creating a Basic Report

Review
1. A ____________ is an interface which maps objects to data in a database.

2. What are the three types of objects?

3. What are the two methods of adding objects to the Result Objects panel?

4. Describe the icon for Predefined query filters.

Exercise
1. Create a new document using the DM – STU – Section Capacity universe.
2. Display the following objects in a vertical table:
• Term Code
• Course Subject Code
• Course Number
• Course ID
• Course Title
• Section Number
• Section Enrollment
• Section Avail Seats
3. Add the predefined filters:
• Active and Gradable
• Prompt for Term Code
• Prompt for Course Subject Code
4. Run the query and enter these values for the prompts:
• Term code: 120191
• Course Subject code: ENGL
 P a g e | 27

Top of the table should look like below:

5. Save the document as Available Seats. If a document with this name already exits, overwrite it.
 P a g e | 28

Chapter 2: Editing an Existing Query / User Defined Query Filters

Adding Objects to an Existing Query
Most reports require changes to the data after the query is run for the first time, such as removing or
adding objects and adjusting query filters.

1. Click Edit Query in the File section of the toolbar to display the Query Panel.
2. Expand the Instructor class.
3. Find the Instructor Last Name object and drag and drop it to the Result Objects panel.
4. Use the Filter box to find Meeting Days and Start Time – End Time in the Universe outline and add
them to the Result Objects panel.
Your Result Objects panel should look like below.

5. Click in the bottom right corner of the window to run the query.
6. When the Prompts window displays, click to keep the retained prompt values and run the
query.
7. The Report Design window is displayed. Note that the new objects we added to the Result Objects
panel did not automatically get added to the existing table, but they are visible in the Document
Objects section in the Main Panel.
 P a g e | 29

Adding a New Object to a Table

The Report Design window is comprised of four main components:

• Toolbar
• Reports panel
• Format panel
• Main panel

Toolbar

Reports panel
Format
panel

Main panel

Within the Reports panel, there are many possible elements that can be selected, for example, the
block of data (a vertical table in our case), a column or cell within the block, the report header, or the
report itself.

The options available in the Format panel are dependent on which element is selected.

We will now add the objects that we just added to the Result Objects panel.

1. Click inside the vertical table in the Report panel so the entire outer border of the table is
highlighted blue.
 P a g e | 30

2. In the Format panel, select Report Element Data in the top row of icons and Feeding panel
in the bottom row of icons.

Note that the bottom row of icons is dependent on which icon is selected in the top row.

3. You should now see the list of objects that are currently in our table under Data Assignment in the
Format panel, and next to that, in the Main panel is the Document Object list, which includes all
objects being pulled by the query.
 P a g e | 31

4. To move objects into the table, we will drag them from the Document Object list in the Main panel

to at the bottom of the Data Assignment section in the Format

panel. Drag the Instructor Last Name Object into the space at the bottom of the Data Assignment
section in the Format panel. Your Data Assignment section should now look like below.

5. Go ahead and add the Meeting Days and Start Time – End Date objects in the same way, so your
Data Assignment section should look like below, and these three columns should be displayed as
the furthest columns to the right in the table in the Reports panel.
 P a g e | 32

User Defined Query Filters

As we learned in Chapter 1, filters are added to a query to limit data returned from the database. If
there are no predefined filters in the Universe for you to use to get the desired data, you can easily
create your own User Defined Query Filter. A filter contains three elements:

1. Object: What to compare

2. Operator: How to compare it Appendix B
3. Operand: What to compare it to Appendix C

We will now add a query filter that will limit our results to only sections with enrollment greater than
zero.

1. Click Edit Query in the File section of the toolbar to display the Query Panel.
2. Select and drag the Section Enrollment object from the Result Objects panel and drop it below the
predefined query filters already in the Query Filters panel.

The object remains in the Result Objects panel, but also gets added as a query filter. The Section
Enrollment filter box:

Object Operator Operand

3. Click the Operator drop-down menu and select Greater than from the list.

See Appendix B for more information about Query Filter Operators.

 P a g e | 33

4. Enter 0 in the Operand box, where Type a constant is written. The Section Enrollment filter box
should look like below:

5. Click to run the query.

6. When the Prompts window displays, click to keep the retained prompt values and run the
query.
We now only see rows with enrollment greater than zero:

Grouping Filters Using the AND / OR Operator

Web Intelligence permits the creation of complex query filters in which filters can be grouped together.
Complex query filters are created by grouping and connecting filters with logical operators.

• The AND operator requires that all conditions be true for the row to be included in the query.
• The OR operator requires that only one of the conditions be true for the row to be returned in
our query results.

We will now further refine our query by including sections that have an instructor assigned, even if the
enrollment is zero, by using the OR operator.
 P a g e | 34

1. Click Edit Query in the File section of the toolbar to display the Query Panel.
2. Click Add nested filter in the upper right corner of the Query Filter panel.

Note: Double-click the operator in the Query Filter panel to toggle it between AND and OR.

3. Select and drag the Instructor Last Name object from the Result Objects panel and drop it to the
right of the OR operator in the Query Filters panel.

You’ll see a vertical blue line appear to the right of the OR operator when you’ve positioned the
object correctly to drop it.

4. Select the Is not null operator from the list for the Instructor Last Name filter.

Note: The Is not null operator returns only rows that have a value in the Instructor Last Name field.
 P a g e | 35

5. Drag the Section Enrollment filter to the top of the Instructor Last Name filter and next to the OR
operator.

You’ll see a horizontal blue line above Instructor Last Name filter when you’ve positioned the
object correctly to drop it.

6. Click to run the query with the same prompt values.

We now see rows where the section enrollment is zero, but only where there is a value in the
Instructor Last Name column. These results are from the bottom of page 1.

7. Click to save the changes to the document.

 P a g e | 36

No Data to Retrieve
When working with filters, it is possible to create a situation where there are no rows that meet the
created conditions. When this happens, you will receive the following message:

This usually means you need to fix a problem with your query filters. Some reasons this may happen
include:

• Entering a bad value for a prompt or filter. If you added a value to a prompt by using the manual
entry method, try searching for the value and selecting it from the list instead.
• Creating two filters that are contrary to each other, for example requesting Section Enrollment
greater than 0 and less than 0 in the same query, or looking for a particular Fund Code, but
forgetting to change the Org code.
• Requesting data to which you do not have security access. For example, entering a program
code that is not in your department, or requesting college-level data when you are a
department-level user.
 P a g e | 37

Exercise 2: User Defined Query Filters

Review
1. What icon allows you to edit the query?

2. How many parts are there in a user defined query?

3. What is the difference between And and Or operators?

Exercise
1. Open the Available Seats document created in Exercise 1.
2. Add query filters to limit the data to rows where Course Title contains either the word American or
the word British.
3. Run the query.
The table should look like below:

4. Save the document.

 P a g e | 38

Chapter 3: Prompts
A prompt is a Query Filter where the Operand can be defined each time the report is refreshed.
Creating a query filter with a prompt allows the user to retrieve different data from the database
without changing the filters, making the report more flexible. Each time you run a query with a prompt,
you will be asked to supply the value(s) to limit the data.

We will now create a query filter with a prompt which will allow us to determine what level of courses
to return.

Building a Prompt
1. Click Edit Query in the File section of the toolbar to display the Query Panel.
2. Type level in the search box. Locate the Course Number Level object and drag it into the Query
Filters panel as seen below.

3. Leave the Operand value as In list. Click the Define filter type icon and select Prompt.
4. Now click the new Prompt Properties icon that is now displayed in the Course Number Level
filter box. The Prompt Properties window is displayed:
 P a g e | 39

Prompt Properties window

Prompt Text This is the text that will be shown to the user running this query. Feel
free to edit this text to make it clear to the user what value is being
requested by this prompt.
Default text: “Enter value(s) for [Object]”
Prompt with List of Values When a prompt is selected, a list of values is generated. This list can be
disabled if the list of values is large and takes too much time to
populate.
Default: Checked
Select only from list Removes the manual entry method of entering a value for this prompt.

Keep last value(s) selected Saves the value(s) entered the last time the query was run.
Default: Checked
Optional Prompt The user can choose to not enter a value for the prompt. If a value is
not entered, the filter will not be applied to the report.
Set default value(s) Allows you to select default values. User can modify this value at
prompt.
• Click to see a list of values, or
• Type a value to enter values manually.
 P a g e | 40

5. Check the Optional Prompt check box, and click

6. Click to run the query.

7. Select the prompt we just added and click Refresh to generate the list of values.
8. Select 100, 200 and 300 by checking the associated boxes, and click to run the query.

Refresh Data / Change Prompt Values

Since the EDW is updated and new data is added nightly, refreshing the data provider will ensure it is
displaying the most up-to-date data.

Refreshing the data will also allow us to adjust our prompt values.

1. Click in the Query section of the toolbar to refresh the data provider.

Note: If our document contained 2 or more queries, the icon next to Refresh would allow us to
refresh specific queries.
 P a g e | 41

2. In the Prompts window, click on the Select 6 digit Term Code prompt on the left, and then click the
Display Current Prompt Answers Page icon above the Search bar to display the currently
selected value(s).

3. Click the next to term code 120198 to remove it from the selected values.
4. Type 120201 into the Search bar and hit Enter, or choose the first option that pops up, to search for
the Spring 2020 semester at UIUC. Select term code 120201.
5. Next, click the Enter value(s) for Course Number Level prompt on the left and add 400 to the list of
selected values. Your filter should look like below:

6. Click to run the query with the new prompt values.

 P a g e | 42

7. When the report displays in the Reports panel, it now shows course sections offered in Spring 2020,
as opposed to Fall 2019 (though we can’t tell by looking solely at this table), and if we navigate to
the last page, we can see 400 level courses.

8. Click to save the changes to the document.

 P a g e | 43

Exercise 3: Prompts
Review
1. When are the prompts displayed?
2. When entering multiple values for a list, what separator should be used?

Exercise
1. Open the Available Seats document.
2. Add an optional prompt for a minimum Section Avail Seats value.
3. Run the query and enter 10 as the minimum available seats.
The table should look like below:

4. Refresh the document and remove the value for the minimum available seats.
5. Display the selected prompt values at the top of the first page of the report.
The table should look something like below:
 P a g e | 44

Chapter 4: Formatting
Most of the formatting of a report takes place after the query has been run and the results are displayed
in the Report Manager. This chapter will guide you through the process of formatting the report so that
the data can all be viewed and printed.

Report Formatting
Report formatting is used when you plan to print or save to a PDF.

Print Layout
In Print Layout, reports are displayed as they will look when printed.
To turn Print Layout on, click in the Display section of the Toolbar.

Report Layout Settings

The report’s layout settings dictate how the final output of the report will look. These settings become
more important when trying to fit large tables of data into a report.

Make sure you click in a blank white space in the Reports panel, to ensure you’re selecting the report
itself and not another element.

In the Format panel, select Report Element Format in the top row of icons and Layout Settings

in the bottom row of icons.

 P a g e | 45

• The top section should show the Quick Display Mode settings. Quick Display mode is the normal
view, as opposed to the Print Layout mode. Here you can adjust the number of rows and
columns to display in the Reports panel while not in Print Layout mode. We’ll leave this alone.

Note: If you’re seeing something other than the image above in the Format panel, you’re
probably selecting an object in the Reports panel. Click on a blank white space to select the
report itself.

• The next section down is Print Layout. Here you can select the size of the paper and its
orientation.

We’ll change the size to Letter and the orientation to Lansdscape.

• The next section down is Margins. Here you can adjust the size of the paper’s margins.

We’ll change all of the margins to 0.50 inches.

• The last section is Scaling, where we can adjust the report to fit on a certain number of pages
wide and/or tall. We’ll leave this alone as well.
 P a g e | 46

As you’ve noticed, each individual change we applied caused the report to refresh. This is due to the
Instant Apply box being checked at the bottom of the Format panel.

When making a lot of adjustments to a report, you may prefer to uncheck this box and manually click

when you want the report to refresh.

Table Formatting
Table formatting is more related to how the data will look in the table, and how the table looks within
the report.

Deleting a Column
Sometimes, after running a query and seeing the report, you’ll decide to get rid of some columns.

1. Make sure you click in the block of data to ensure the table, or an element within it, is selected.
2. In the Format panel, select Report Element Data in the top row of icons and Feeding panel
in the bottom row of icons.

3. We’ll demonstrate this by deleting the Course Subject Code object. Locate the object in the Data

Assignment section and select it by clicking on it. Click to delete it from the table.

Rearranging Columns
To arrange the columns in a different order, simply drag them to the position you want them in the
table, within the Data Assignment section. Make sure the blue horizontal line is where you want the
object to end up before dropping the it.
 P a g e | 47

1. Make sure you click in the block of data to ensure the table, or an element within it, is selected.
2. In the Format panel, select Report Element Data in the top row of icons and Feeding panel
in the bottom row of icons.

3. Locate the Section Enrollment object, drag it down until you see the horizontal blue line after the
Start Time – End Time object, and drop it to place it as the last column in the table.

Resizing Columns to Autofit Widths

You can select one or multiple columns (or rows) in the table to adjust the height and width of those
elements.

• You can select either the header columns or the data in the columns.
• CTRL or SHIFT for multiple columns.
 P a g e | 48

1. For our report, we’re going to set all of the columns to autofit the width based on the values in the
columns. Click anywhere in the first column, hold SHIFT and select the last column to select all
columns in the table.

2. In the Format Panel, select Report Element Format in the top row of icons and Layout Settings

in the bottom row of icons.

 P a g e | 49

3. Check the Autofit box under Minimum width.

As you can see, when we autofit all of the column widths, our table got wider than the print area:

There are a few solutions to this issue and based on the data being presented and your own personal
preferences, you can decide which to use in your own reports.

One option is to go back to the Report Layout Settings and set the Scaling to Fit to 1 page wide. This will
scale the table down, so it fits in a single page width.

Wrap Text
We’re going to use another option that doesn’t scale the table down.

1. Make sure you click in the block of data to ensure the table is selected.

2. In the Format panel, click Report Element Format in the top row of icons and Text Settings

in the bottom row of icons.

 P a g e | 50

3. Check the Wrap text box.

We can now see the text in the column headers is all wrapped to better display the data in the
columns.

4. Click to save the changes to the document.

 P a g e | 51

Exercise 4: Formatting
Review
1. What two icons do you select in the Format panel to display the Data Assignment section?

2. You can only wrap text in the column headers. True or false?

Exercise
1. Open the Available Seats document.
2. Change to the Print layout.
3. Change the margins to 0.5 inches on all sides.
4. Remove the Term Code and Course Subject Code columns from the table.
5. Wrap the text in the column headings and in the columns as needed.
6. Resize the columns to fit the table on one page.
7. Right-align the header for Section Enrollment and Section Available Seat.
8. Remove the Report Name cell and hide the report header.
The top of the table should look something like below:

9. Save the document.

 P a g e | 52

Chapter 5: Report Analysis Features

In this chapter, we will cover four features that can help you analyze the data in your reports: Sorting,
Filtering, Breaks, and Calculations.

The first three features mentioned can be found by clicking in the block to ensure the table is selected

and clicking Report Element Data in the top row of icons in the Format panel.

Filters Sorts Breaks

panel panel panel

Note the small zeros near the Sorts panel and Breaks panel icons. These serve as a visual indicator of how
many sorts and breaks are applied to the block (when the block itself is selected), or whether a column
has a sort or break applied to it (1) or not (0). The Filters panel icon and Ranking panel (not discussed in
this course) have a dark circle in place of the number, which simply indicates if a report filter or ranking
has been applied to the selected element.

Sorting Data in a Table

Even if no sorting has been applied, tables in Web Intelligence are sorted by default. The dimension and
detail objects are sorted from left to right in ascending order. Measure objects are not sorted by default.
We will now override the default sorting on our table by specifying the sort order for some columns.

1. Make sure you click in the block of data to ensure the table, or an element within it, is selected.

2. In the Format panel, select Report Element Data in the top row of icons and Sort panel
in the bottom row of icons.
3. Click the Add a sort menu and select Section Enrollment.
4. When the Section Enrollment sort appears, click to sort descending.

5. Next, we’ll add Course Number as a sort. Click Add a sort and select Course Number.
 P a g e | 53

6. We want the table to first sort by Course Number ascending and then by Section Enrollment
descending. To prioritize the sorting of Course Number, drag the Course Number sort and drop it
when the horizontal blue line is above the Section Enrollment sort.

The Sort panel should look like below.

Duplicating and Renaming a Report

It is good practice to first make a copy of a report before making major modifications. Each report within
a document has its own tab at the bottom of the editor. Next, we will create a duplicate report so that
we can save different variations of the report.

1. Click in the Report 1 tab in the upper left corner of the Reports panel and select Duplicate.

2. Next, we’ll Rename the new Report 1 (1) tab. Click the in the Report 1 (1) tab and select
Rename. When the Rename Report window comes up, type Lincoln Hall and click .
3. Let’s also rename Report 1. Let’s call it All CMN Enrollment.
The Reports panel’s tabs should now look like below.
 P a g e | 54

3. Click to save the changes to the document.

Applying a Report Filter

After running a query, you may decide that you want only certain data to be displayed in your report. If
you run a report for your college, you may want to separate the report by department. Applying a report
filter allows you to run a single query with all the data you need to produce a number of reports.

In our case, we only want to show data for the sections held in Lincoln Hall.

1. Make sure you’re on the Lincoln Hall report and not the All CMN Enrollment report.

2. In the Format panel, click Report Element Data in the top row of icons and Filter panel
in the bottom row of icons.
Note that if the report is selected, Filter panel will be the only icon in the bottom row, but if the block
is selected, the bottom row will include more icons. Either way will work.

3. Locate the Section Building Name object from Document Objects list in the Main panel to the
drop area in the Filter panel.
4. This brings up the Select values for Section Building Name window, where we will select Lincoln
Hall and click .
 P a g e | 55

Managing Report Filters

If we need to change the operator from the default ‘In list,’ we can do that as we set up the filter.

• In the Select values for Section Building Name window, click the Settings icon in the top
right corner to select from the standard list of operators.

If we should realize after setting up the report filter that we need to use a different operator, or change
the values we initially selected, or in cases of multiple report filters being applied at once, we can
manage all of the report filters in one location.

1. In the Format panel, click Report Element Data in the top row of icons and Filter panel
in the bottom row of icons. Click either of the icons below, and select Manage Filters.
 P a g e | 56

2. From the Report Filter window, we can access every report filter in the entire document (left panel),
add filters or nested filters and change operators of filters (top panel), and change the values
selected for each filter (bottom panel).

3. For our purposes, nothing needs to be done here. Click .

Inserting a Break
In our example, we would like to display the sum of students enrolled not only in each section, but also a
sub-total by Course Number.

First, we will insert a break on Course Number. Inserting a break will take a large table of data and
create sub-tables to make the information easier to understand.

1. Make sure you’re on the Lincoln Hall report and not the All CMN Enrollment report.

2. In the Format panel, click Report Element Data in the top row of icons and Breaks panel

in the bottom row of icons.

3. Click the Add a break menu, select Course Number, and click .
 P a g e | 57

4. We now see that our report is broken up at each change in Course Number.

Inserting Calculations
Web Intelligence calculations enable you to quickly add information to your report. Many of the
calculations only work with numeric data. The type of data determines which calculation functions are
available.

The following table shows the available calculations and data types:

Calculation Object Type Description

Counts all rows.
Count
Counts distinct rows.

Min Displays the minimum value of the selected data.

Max Displays the maximum value of the selected data.

Sum Calculates the sum of the selected data.

Average Calculates the average of the data.

Displays the selected data as a percentage of the total.
Percentage The results of the percentage are displayed in an
additional column or row of the table.
 P a g e | 58

Inserting a Sum
Select and then right-click the data (not the column header) in the Section Enrollment column, select
Footer Calculation, and select Sum.

Scrolling down the report, we can see that we now have a sum in the footer of each break, and a grand
sum at the very bottom.
 P a g e | 59

Note: Multiple calculations can be done on the same column at the same time. Each additional
calculation on a column will result in the addition of a new footer row containing the new calculation.

Click to save the changes to the document.

 P a g e | 60

Exercise 5: Report Analysis Features

Review
1. In the Format panel, which two icons do you select to manage Sorts?

2. Describe a scenario where the number in the Sorts icon changes from 2 to 1  ,
without deleting a sort?

Exercise
1. Open the Available Seats document.
2. Create a duplicate of Report 1.
3. Rename the new report Available Seats by Course.
4. In the new report, add a break on the Course Number column.
5. Add a descending sort on Section Avail Seat.
6. Add a sum calculation to the Section Enrollment and Section Available Seat columns.
7. Add a cell displaying the Page Number / Total Pages to the top right corner so that it shows up on
every page (hint: you may need to add something back we deleted previously)
The top of the report should look similar to below:
 P a g e | 61

Chapter 6: Exporting Data and Sending

In addition to saving a document in the standard file type (.wid), you can also export the data in
different formats, such as Excel, PDF, CSV, or Text. It is a good practice to save your report first as a Web
Intelligence document, and then to save your results to Excel or PDF each time you refresh the report.

Exporting to Excel (.xlxs)

1. In the File section of the Toolbar, click More and select Export. The Export to window is
displayed.

On the Content tab, there are radio buttons for Reports and Data.

Exporting a document’s Reports will export the data with all of the formatting applied, whereas
exporting the Data will export the raw data returned from the query.

We’re going to download both options, so you know what to expect when exporting data in the
future.

2. Select Reports and check the box to include All reports, and then click

3. Locate the downloaded file and open it using Excel.

We see that the worksheet – named for the Web Intelligence document – has two spreadsheets –
named for the two reports. We also see the formatting that has come over.
4. Go back to Web Intelligence. In the File section of the Toolbar, click More and select Export.
The Export to window is displayed.

5. This time, select Data and then click

6. Locate the downloaded file and open it using Excel.
 P a g e | 62

This spreadsheet is very different – no formatting at all – not even the default formatting Web
Intelligence put on tables.

Note: These two exports can be very beneficial in troubleshooting data issues with your reports.
Comparing them can often tell you whether there is a problem with the underlying query, or with the
formatting of the reports.

Sending to a BI Inbox
The BI Inbox is a document sharing system within Web Intelligence that allows users to securely send
documents to one another.

• If you’re in the document (running, editing, etc.), in the File section of the Toolbar, click More
and select Send to BI Inbox.
OR
If you’re on the Home Screen or in a folder, click More and select Send to BI Inbox.
The Send to users window is displayed.

• In the Search bar, enter the NetID of the user you want to send the document to and click
 P a g e | 63

• Check the box next to the user’s NetID, and it will be included in the Selected Items box at the
bottom of the window.

• Click to send the document to the user.

 P a g e | 64

Exercise 6: Sending a Document

Review
1. What are the two main options when exporting data (e.g., types of data to export)?

Exercise
1. Send the Available Seats document to the following BI Inbox: ________
 P a g e | 65

Appendix A: Additional Assistance

Decision Support Website
Additional resources and online help can be found on the Decision Support website. Resources include
user guides and quick tips on using both Web Intelligence and EDDIE.

AITS Service Desk

For further assistance, or to report a problem, contact the AITS Service Desk:

Email: [email protected]

Chicago: (312) 996-4806

Urbana & Springfield: (217) 333-3102

SAP Web Intelligence Online Help

Web Intelligence provides on-line assistance and information about basic product features, as well as
information to help you troubleshoot and solve common problems.

Select Help from the menu in the upper right corner of the Web Intelligence window.

This will take you to the SAP BusinessObjects Web Intelligence User’s Guide for the version installed in
the Production environment.
 P a g e | 66

Appendix B: Query Filter Operators

The following table can help select the operator needed:

Operator Description Example

Equal to Is equal to one given value [Fiscal Year]<Equal to>2005
Not Equal to Is different from, or not equal to, [Employee Status Code]<Not Equal
one given value to>T
Greater than Is greater than a given value [GL Detail Credit Amount]<Greater
than>100
Greater than or Equal to Is greater than or equal to a given [GL Detail Credit Amount]<Greater
value than>100
Less than Is less than a given value [Job Detail Annual Salary]<Less
than>20000
Less than or Equal to Is less than or equal to a given value [Total Reg Credit Hours]<Less than
or Equal to>11
Between Is greater than or equal to the first [Job Begin Date]<Between>1/1/2022
given value and less than or equal to 12:00:00 AM<and>12/31/2022
the second given value 11:59:59 PM
Not Between Is less than the first given value or [Job End Date]<Not
greater than the second given value Between>1/1/2022 12:00:00
AM<and>12/31/2022 11:59:59 PM
In list Is equal to any of a list of values [Employee Campus Address
Code]<In list>C1;C2
Not in list Is different from all of a list of values [Type of Fund]<Not in list>State
Funds;Plant Funds;Endowments
Is null Contains empty rows [Financial Person Email Addr]<Is
null>
Is not null Does not contain empty rows [Employee Campus Email Addr]<Is
not null>
Matches pattern Contains the same character(s) as [Employee Last Name]<Matches
the given pattern. Wildcard pattern>Alb%
characters are _ for a single
character and % for 0 or more
characters.
Different from pattern Does not contain the same [Financial Account Code]<Different
characters as the given pattern from pattern>9%
Both Satisfies two conditions on one [Prior Degree
object Year]<Both>2001<and>2004
Except Excludes a given value [Employee Status Code]<Except>T
 P a g e | 67

Using In list
You can type your list of values in the text field of the Type a constant text box. If you click the Operand
button, you can also select Value(s) from list to select from a list of values. The maximum number of
values allowed in a list is 256.

When entering multiple values manually, use a semi-colon (;) to separate each value.

Using wildcard characters

Conditions with the Matches pattern and Different from pattern operators are great for finding lists of
similar values, such as last names beginning with ‘Alb’ or email addresses matching pattern ‘@uic.edu.’

Wildcard Description
% The percent sign replaces several characters in a constant, or all characters in the response to a
prompt. For example, N% returns all values beginning with an N (New York, Nevada, etc.), and
435% returns 435, 435001, 435002, etc.

_ The underscore character (_) replaces a single character in a constant. For example, GR_VE
returns Grave, Grove, Greve, etc.
 P a g e | 68

Appendix C: Query Filter Operand Options

The following table can help select the operand option needed:

Operand Option Description Enter by…

Constant Values that you type. Note that Type the values with a semicolon (;)
values are case-sensitive and must between each one.
be entered exactly as they appear in
the database otherwise no data will
be returned.
Value(s) from list Values that you select from the Select Value(s) from list option.
object’s list of values. Select from the populated list of
values. Click Refresh values if list
does not automatically populate.
Prompt Values that you will select when you Edit default prompt text in text box.
run the query. Click Prompt Properties for more
options.
Object from this query Use a list generated by another Select Object from this query then
object in the same query. For select the object from the list.
example, you can pull GPA Level
codes that only match the Student
Registration Level Codes being
returned in the same query.
Results from another query Use a list generated by another Select Results from another query
query. For example, a list of UINs then select the data provider from
Results from another query from another spreadsheet. Note the list.
(Any) there is an Oracle limit of 1000
Results from another query values.
(All)