Oracle Reports Building Reports Release 6i A73172-01 |
|
The report described in this chapter is designed to help you learn more about the Report Builder features for Express data. You will build an Express report that summarizes the yearly projected and actual sales for each region and sales channel in a product division.
To build this report, you will use the Report Wizard to create the initial data model and report layout. You will make refinements to the data model and to the Express query. Finally, you will enhance the look of the report in the Layout Model view and Live Previewer.
Express delivers on-line analytical processing (OLAP) using a multidimensional data model. This model is optimized for the analysis of trends or patterns of intersecting corporate data -- such as sales, marketing, or financial variables.
The figure on the next page illustrates the 1997 Sales report that you will build. Think of the data you wish to extract as being contained in the volume of a cube. Each side of the cube is a list of variable data contained in a category (i.e., Product). This category and its list of values together is called a dimension. You will select portions of each dimension and analyze them for their interaction with other dimensions. This analysis is called a measure.
An example measure for a sales analysis might select data from dimensions for time, product, geographic division, and channel. With Express, you can create a query to report on information that is as broad (e.g., yearly direct and indirect sales for products sold everywhere) or as narrow (e.g., monthly direct sales for all televisions sold in California) as you like.
Table 7-1, "Features demonstrated in this sample report of Express data" describes the steps you will take to create this report.
The xprs.rdf
file contains the report that you will create after finishing the tasks in this chapter. You may want to refer to this file while you are working. This file is located in your ORACLE _HOME\TOOLS\DOC60\US\RBBR60
directory.
Feature | Location |
---|---|
Use the Report Wizard to define the Express query and create a first draft of the report. |
Section 7.1, "Creating an Express report with the Report Wizard" |
Streamline the Express query by specifying dimension values. |
|
Add summary and calculated totals using the Data Model view. |
Section 7.3, "Adding summary columns and custom measures to your data model" |
Add summary and calculated totals to the report layout. Enhance the look of the report. |
Before you start building this Express report, you must have already configured Report Builder to run with Express Server, R6.2 and Oracle8 Server for Windows NT with Object Option, R8.0.5, or later. Refer to the Getting Started manual for more information on how to do this.
To get started, open Report Builder. If the Welcome dialog box appears, click Use the Report Wizard and click OK. If not, choose File->New->Report. Click Use the Report Wizard and click OK.
At some point before you generate the report you will need to log on to the Oracle8 database. Choose File->Connect to connect to the database. Enter the appropriate logon information. See Section 1.3, "Obtaining database access before you start" for details.
In addition, you will at some point also need to connect to Express Server. Choose File->Express
The Report Wizard is a great way to start building a report. The Report Wizard alone may give you an Express report that satisfies your requirements. If it doesn't, you can use the Data Model view, the Live Previewer, and the Layout Model view to further refine your report. For this report, you will start with the Report Wizard. The steps in this section will help you to create the initial report.
|
The report that you create in this exercise will present the monthly regional and channel projected and actual sales for each product division. Your Express query will have two measures, and each measure will be dimensioned by product, time, geographic area, and channel.
Sales Report
as the Title, and click Matrix with Group as the report style.
Tip: If you are unsure about what to do on any page of the wizard, click Help.
Tip: If you haven't already connected to Express Server, the Connect dialog box appears. Choose the Express Server instance that you want to access. Click OK.
/oec62/
. Select xademo.db. This is the sample database that is provided with Express Server.
Tip: If you haven't already connected to an Oracle8 database, the Connect dialog box appears. Enter a User Name, Password, and Database. Click OK.
Field | Label | Width |
---|---|---|
SALES |
Actual Sales |
7 |
PROJECTED_SALES |
Projected Sales |
7 |
GEOG_AREA |
Region |
10 |
PRODUCT |
Product: |
10 |
TIME |
Time: |
10 |
CHANNEL |
Channel |
7 |
Tip: You will change the width of labels at this point because in a later step you will add a new layout column. This will cause columns to wrap to the next page at their current default width of 10 points each.
xprs_710.rdf
.
Tip: It is good practice when you are designing your report to save it frequently under a different file name. If you generate an error or if you don't like some of the changes that you made, you easily can go back to the previously saved file and make revisions from that point.
The steps in this section will help you refine the Express query. So far you have developed a useful report that shows the monthly projected and actual sales for each region and channel in a product category. But you are really interested in the yearly projected and actual sales results for each channel and region in a product division. You can achieve this by restricting the dimension values that you want to view.
|
In this exercise, you will specify the following dimension values in the Express Query dialog box:
xprs_720.rdf
.
xprs_710.rdf
.
Notice the projected and actual sales. In the new report, each cell represents the yearly sales for a region and channel in a product division for 1997, while the previous report displays sales data for a region and channel in a product division for each month.
The steps in this section will help your refine the data model to include summary totals for each channel in a product division. Additionally, you are curious about how accurately you predicted the actual sales. You can find this out by creating a custom measure that calculates the percent of sales above projected sales.
First, you will create the summary column using the Summary tool in the Data Model view.
Next, you will create the custom measure using the Custom Measure tool in the Express Query dialog box.
Before you begin, take a look at the data model:
In the Data Model view you may notice additional columns, such as
S_GEOG_AREA, or S_CHANNEL. These are dimension sorting columns. They are visible only in the data model and are the index used to sort dimensions by logical order, as opposed to alpha-numeric order. If you move a column to a new group, you must also move the associated sort column into that group as well.
|
In a later step, you will sort dimension values using the Sort tool in the Express Query dialog box.
Tip: If you want to modify your Express query, click the Express Query property under the Query node.
xprs_731.rdf
.
In this exercise, you will add two summary columns to the G_Cross group. Each summary column will calculate the projected and actual sales totals for each channel (all channels, direct, and indirect) in a product division.
Node | Property | Value |
---|---|---|
General Information |
Name |
CS_PjSalesPerChannel |
Column |
Product Order |
G_CHANNEL |
Summary |
Source |
PROJECTED_SALES |
|
Reset At |
G_CHANNEL |
Node | Property | Value |
---|---|---|
General Information |
Name |
CS_SalesPerChannel |
Column |
Product Order |
G_CHANNEL |
Summary |
Source |
SALES |
|
Reset At |
G_CHANNEL |
xprs_732.rdf
.
In this exercise, you will create a custom measure that will calculate the percent of actual sales above projected sales for each region and in each product division. To do this you will use the Custom Measure tool within the Express Query dialog box to build the new measure called Increase.
INCREASE
.
Increase
.
100
in the Expression box.
xprs_733.rdf
.
The steps in this section will show you how to re-arrange the report layout, add the summary and custom measure columns that you created in Section 7.2, "Refining the Express query", and format objects to further enhance the look of your report. You make these changes using the Layout Model view and Live Previewer.
M_G_CROSS_GRPFR
in the Find field to locate this object. Note that the search occurs as you type, so you will most likely be taken to the object before you finish typing the entire name. In the Layout Model view, the master cross-matrix frame is selected as illustrated in the following figure:
Tip: You may need to resize your Layout Model window to see the button, as it is located on the far right of the toolbar.
Node | Property | Value |
---|---|---|
General Information |
Name |
F_PjSalesPerChannel |
Field |
Source |
CS_PjSalesPerChannel |
Tip: You can turn Snap to Grid on or off as desired to help you arrange objects in the layout. Select View->Snap to Grid. A check mark indicates that the option is on.
Node | Property | Value |
---|---|---|
General Information |
Name |
F_SalesPerChannel |
Field |
Source |
CS_SalesPerChannel |
Tip: Note that the fill and text colors, as well as the border lines, match the field you just created, F_PjSalesPerChannel.
Totals:
.
xprs_741.rdf
.
You will add a column to display the custom measure you created in Section 7.3.3, "Creating a custom measure" by inserting a field object in the report layout.
To do this, you will add a new column to the layout of your report and then insert the field object into the column.
Tip: The new field object also must have the same frequency as F_PROJECTED_SALES and F_SALES. If the field object is not at the same frequency, the report will fail to run.
Tip: M_G_PRODUCT_GRPFR is the underlying master group. It is hidden directly under R_G_PRODUCT. In the Layout Model view, it may look like only one group is selected when, in fact, both frames are selected.
Tip: Click to turn Flex mode on, or click to turn Flex mode off if you are unable to resize or move an object.
Node | Property | Value |
---|---|---|
General Information |
Name |
F_Increase |
Field |
Source |
INCREASE |
To understand why this error occurred, look for F_INCREASE in the Object Navigator. It is probably placed at a higher level (and lower frequency) than R_G_PROJECTED_SALES. Recall that the column INCREASE calculates the percent of actual sales above projected sales. In order to run this report, F_INCREASE must have the same frequency as F_PROJECTED_SALES and F_SALES to reference the data it needs to calculate the value.
Increase
.
Tip: You may wish to turn off Snap to Grid on the View pull-down menu in order to extend the text object to cover the entire field. Make sure the text object is selected when you apply formatting, or it will not take effect.
Your layout model should resemble the following image:
xprs_742.rdf
.
Suppose you wish to change the sorting order of the distribution channels in your report. In this exercise, you will change the sorting criteria for the Channel dimension by using the Selector in the Express Query dialog box. Instead of listing the order by the default channel hierarchy (top to bottom), you will display data from the lowest to the highest channel in the hierarchy. Note: the hierarchy is predefined in the database to place "All Channels" first, with "Indirect" placed last.
Criteria | Selection |
---|---|
based on |
hierarchy |
in order |
bottom to top |
in hierarchy |
Standard |
xprs_743.rdf
.
xprs_744.rdf
.
Congratulations! You have finished the Express sample report. You now know how to:
For more information about Express, see the online help:
|
|
Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|