The Business Analyst's Guide to Oracle Hyperion Interactive Reporting 11: Quickly master this extremely robust and powerful Hyperion business intelligence tool

The Query section layout is configured to easily drag-and-drop the content from a data model into the Request, Filter, and Sort lines to build the desired query. This section contains a main window that is used to configure and display the data model. The Sections window, Section Title Bar, and Status Bar facilitate working with the query and provide useful information throughout the process.

Sections window

The Sections window is located on top of the Section Catalogue and is displayed in the following screenshot. Each Query section in the document is always accompanied by a Results section. The Query section is used to model a query, and the Results section contains the data returned after the query is processed. In the following screenshot, the Query section is named Q_Sales and the Results section is named R_Sales:

Note

Each section in the document must contain a unique name. To edit the name of a section, right-click on the section in the Section Catalogue, and...

In mature environments, business analysts partner with developers or colleagues with a strong database background to create pre-built data models to query. Working with a standard pre-built query template ensures that the model has been correctly configured and will produce accurate data in a timely manner. The following screenshot is a predefined query against the Sales model:

Notice in the previous example that there are seven objects joined together in the main window. These objects are typically tables from a database or preconfigured topics of information (Interactive Reporting objects used to combine data together to make querying easier for the analyst). Each of these table's objects contain multiple elements, which represent columns of data. From this configuration, the analyst can use the elements in the tables on the screen to build a query to retrieve data from the database.

A query is built by adding and configuring items in the Request, Filter, and Sort areas of the section. Interactive Reporting provides a significant amount of flexibility for building queries, including the ability to add custom logic into the query. Once the query is configured, the query can be processed and modified as desired.

Filters provide the ability to return a subset of data in the model by a field or combination of fields from the data model. Interactive Reporting provides a very robust filtering capability, where complex filters can be added to queries through an easy-to-use interface.

A filter is easily added to the query by adding the item to the Filter line. Items are added to the filter line by using one of the following two methods:

Since the filter must be configured after it is added, only one column can be added to the Filter line at a time. Once the column has been added to the Filter line, the Filter interface appears to configure the filter values for the column.

Many options exist for sorting data throughout each section of the software. Sorting in the Query section is easily achieved by dragging-and-dropping a field from the Request line to the Sort line. Continuing with the example, the query is sorted to return the quantity of a product that is sold in descending order by dragging the Quantity Sold column to the Sort line. Once added, the Quantity Sold item appears in the Sort line with an arrow pointing upward denoting ascending order. Double-clicking on the Quantity Sold box in the Sort line switches the sort to descending order as denoted by the change in the arrow to point down as shown in the following screenshot. The sort will take place after the query has finished processing, where the data will be ordered in the Results section descending by the Quantity Sold column.

Queries are frequently processed by a user in daily interactions with the software. Multiple options exist to make query processing easy. Query processing is executed by clicking on the Process button in the Standard toolbar or by clicking on the Tools | Process Query | Current menu option. After the query has finished processing, the software will populate the Results section with the data requested in the Query section.

In addition to simple processing, a few other options exist for processing multiple Query sections in a particular order. Using the previous example, the Q_Sales section is duplicated by right-clicking on the Q_Sales name in the Section window and selecting Duplicate Section. Repeating the process will add a third identical query. Since the section names must be unique, the software renames the section by adding...

Multiple query options can be set in a Query section to modify the results returned from a query. The Query Properties window, as shown in the following screenshot, is opened by double-clicking the word Request in the Request line, or by clicking on the Query menu and selecting Query Options:

The commonly used features of this window are the Process options contained in the Process box. The following is a breakdown of each Process property:

Table and column remarks are used to provide information on specific tables and columns of interest. The remarks are setup and configured in the OCE database connection file and are customized to provide information to the user. The remarks are accessed by highlighting and right-clicking on the table or column of interest and selecting the Show Remarks menu option. A window appears with the remarks for the table or column.

If multiple remarks have been created for an object, the remarks appear with tabs to allow navigation between the different sets of information as shown in the following screenshot:

The Query section is the Interactive Reporting interface for returning data on-demand from a database. The goal of this chapter was to provide the business analyst with the tools necessary to effectively build and manage queries from a data model. The chapter began with an overview of the layout of the Query section. The chapter continued with information for building a query and configuring Request items. The chapter examined and provided examples of the different filtering options including Show Values, Custom Values, Importing from a file, and Variable filters. The chapter addressed Query sorting as well as the different processing options for executing the Query sections in the document. Finally, the chapter closed with an overview of locating metadata in the document through viewing table and column remarks.