Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Exploring Query Properties

Objective

After completing this lesson, you will be able to Work with query properties.

The Query Panel

The default Query Panel display consists of the following panels:

Toolbar
Data Outline
Result Objects
Query Filters
Data Preview

The Query Panel: Toolbar

The toolbar buttons for the panels are found in the left corner of the Query Panel.

The Toolbar contains the following options:

Option	Description
Add Query	Allows users to add a new query to a document.
Data Outline panel	Allows users to show or hide the data outline, which contains the Master Perspective and Display by Navigation Path views.
Filters panel	Allows users to show or hide the Query Filters panel.
Data Preview panel	Allows users to show or hide the Data Preview panel.
Scope of Analysis panel	Allows users to show or hide the Scope of Analysis panel.
Add a combined query	Allows users to combine data retrieved from more than one query, using one of the following operators: Minus, Union, Intersection.
Query Properties	Allows users to display the query properties.
View Script	Allows users to display the Query Script viewer dialog box.
Run	Allows users to run the query you built and retrieve the data from the database. The results of the query appears in a report in the Web Intelligence.
Apply Changes and Close	Allows users to close the Query Panel.

The Query Panel: Results Objects

The Results Objects Panel contains the following options:

Option	Description
Add Quick Filter	It allows you to apply a filter on a selected object in the Result Objects panel.
Remove	It allows you to remove a selected object from the Result Objects panel.
Remove All	It allows you to remove all objects from the Result Objects panel.

The Query Panel: Query Filters

The Query Filters Panel contains the following options:

Option	Description
Add a subquery	Allows users to run a subquery to restrict the data that the query returns.
Add a database ranking	Allows users to use the database ranking feature to retrieve only the top or bottom values from the database. (NOTE: Only if supported by the database driver).
Remove	Allows users to remove the selected object from the Query Filters panel.
Remove All	Allows users to remove all objects from the Query Filters panel.
Add nested filter	Nested query filters allow you to create more complex filter conditions than is possible when you combine filters at the same level. When you nest filters, you set the order in which they are evaluated.

The Query Panel: Query Properties

The Query Properties contains the following options:

Option	Select to
Limits - Max rows retrieved	Select a maximum numbers of rows for retrieval. If you use this option, you also need to set a maximum number of rows to be retrieved.
Limits - Max retrieval time	Set a time limit, in seconds, after which the data retrieval process stops.
Sample - Sample result set	Return a sample result set. To use fixed sampling, click Fixed. Sampling is not available in BEx queries. The Fixed option is visible, but not activated if this option is not supported by your data source.
Data - Refreshable	Allow the refresh a specific query.
Data - Retrieve duplicate rows	Include duplicate rows. 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. This option is not available in BEx queries, or if it is not supported by the underlying database.
Data - Retrieve empty rows	Include empty rows in the result.
Data - Delete trailing blanks	Delete trailing blanks from data providers' values.
Enable query stripping	Allow the application to remove objects from queries if they're not used in the report.
Context - Reset contexts on refresh	Reset the contexts when refreshing the document or query. If you use this option, you need to select a context when you refresh a query.
Security - Allow other users to edit all queries	Allow users with query editing rights to edit queries you have created.

Contexts in a Query

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 the universe by the universe designer.

You can combine any objects 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 multiple contexts in a query and it is not possible to determine the best context that fits all the other objects in the query, you are prompted to choose the context to apply.

When you create a query or refresh a document, you may be asked to choose a context before the query can run. Contexts are set up in a universe to avoid ambiguous queries.

You need to choose a context each time you run the query if the Reset contexts on refresh query property is selected in the query properties. You will also need to choose a contexts if you select the Clear contexts option in the query properties.

If prompts have been set for the document, then the Prompts dialog box appears. Otherwise the document content reflects the context you have selected.

You can refresh contexts every time you refresh a query in the query panel.

The Script Viewer

When you build a query, Web Intelligence automatically generates appropriate script (such as SQL) to retrieve the data from the relevant database. You can view and edit this SQL, and copy it to another application if you have the rights to do so.

If in the query panel you modify the script and you remove an object and add a different object to the Results Object pane, the script is regenerated.

Create a Query and Choose a Context

ExerciseStart Exercise

Continue to quiz