Skip Headers

Oracle Reports Building Reports 10g (9.0.4) Part Number B10602-01 |
Home | Contents | Index

# 43
Building a Report Using Oracle Express Data

#### Figure 43-1 Express report output

Text description of the illustration exprepfi.gif

The report that is described in this chapter is designed to help you learn more about the Reports 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.

Note: The Oracle Express pluggable data source does not work on any UNIX platform in the Oracle Development Suite.

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 Paper Layout view and in the Paper Design view.

#### About Express

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.

#### Example Scenario

In this example, you will build a Sales report. Think of the data that you want to extract as being contained in the volume of a cube. Each side of the cube is a list of variable data that is contained in a category (such as 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 (for example, yearly direct and indirect sales for products sold everywhere) or as narrow (for example, monthly direct sales for all televisions sold in California) as you like.

##### Table 43-1 Features demonstrated in this example

Feature | Location
--- | ---
Use the Report Wizard to define the Express query and create a first draft of the report. | Section 43.2, "Create an Express report with the Report Wizard"
Streamline the Express query by specifying dimension values. | Section 43.3, "Refine the Express query"
Add summary and calculated totals using the Data Model view. | Section 43.4, "Add summary columns and custom measures to the data model"
Add summary and calculated totals to the report layout. Enhance the look of the report. | Section 43.5, "Enhance the report layout"

##### Tips for working with Express data

Before you start building a report, be sure that you have reviewed the tips for working with Express data.

Note: For more information on tips for Express data, refer to "About working with Express data" in the Reports Builder online help.

## 43.1 Prerequisites for this example

To build the examples in this manual, you must have the example files we've provided, as well as access to an Oracle Express data source.

### 43.1.1 Example files

If you haven't already done so, you can download the files you'll need to complete this example from the Oracle Technology network (`http://otn.oracle.com/products/reports`) and install them on your machine.

#### To download and install the example files:

1. Go to the Oracle Technology Network Web site (`http://otn.oracle.com/product/reports/`).

2. Click Getting Started with Oracle Reports.

3. Click Index, then find the "Building a Report Using an Express Data Source" example.

4. Download the file Express.zip into a temporary directory on your machine (e.g., `d:\temp`).

5. Unzip the contents of the file, maintaining the directory structure, into an examples directory on your machine (e.g., `d:\orawin90\examples`).

   This zip file contains the following file:

   ##### Table 43-2 File(s) necessary for building the Express sample report

   File | Description
   --- | ---
   `Examples``\Express\result\xprs.rdf` | The final report you will have created when you finish this chapter.

### 43.1.2 Access to an Express Server

Before you start building this Express report, you must have already configured Reports Builder to run with Express Server.

Note: For more information on configuring for Express data, refer to "About configuring the Express data source" in the Reports Builder online help.

## 43.2 Create an Express report with the Report Wizard

You can use the Report Wizard to start building a report. The Report Wizard alone may give you an Express report that satisfies your requirements. If it does not, then you can use the Data Model view, the Paper Design view, and the Paper Layout view to further refine the 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. The Express query will have two measures, and each measure will be dimensioned by product, time, geographic area, and channel.

1. Launch Reports Builder (or, if already open, choose File > New > Report)

2. In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.

3. If the Welcome page displays, click Next.

4. On the Report Type page, select Create Paper Layout only, then click Next.

5. On the Style page, type Sales Report as the Title, and choose Matrix with Group as the report style, then click Next.

6. On the Data Source page, choose Express Server Query, then click Next.

7. On the Data page, click Query Definition.

   Note: If you have not already connected to an Express Server, then the Connect dialog box appears. Choose the Express Server instance that you want to access. Choose OK.

1. In the Express Query dialog box, choose Attach Database to choose the path and name of the database that you want to attach to during this session.

2. In the Attach Database dialog box, select the directory with a label such as `/oec632/.` Select`xademo.db`. This is the sample database that is provided with Express Server.

3. Click Open to attach the database to the session.

4. In the Express Query dialog box, Ctrl-click to select Sales and Projected Sales in the Available Measures list.

5. Click the right arrow (>) to move Sales and Projected Sales to the Selected Measures list. The Express Query dialog box looks similar to the following:

#### Figure 43-2 Express Query dialog box

Text description of the illustration salessel.gif

1. Click OK to accept the Express query selections. You will return to the dialog box in a later step to refine the dimension values that are associated with the Sales and Projected Sales measures.

2. On the Data page, click Next.

3. On the Groups page:

   - click Product in the Available Fields list and click the right arrow (>) to move this field to the Matrix Group Fields list.

   - click Level1, then click Time and click the right arrow (>) so that the Matrix Group Fields list appears as follows:

#### Figure 43-3 Matrix Group Fields

Text description of the illustration mtrxgrpf.gif

- click Next.

1. On the Rows page, click Geog_Area in the Available Fields list and click the right arrow (>) to move this field to the Matrix Row Fields list, then click Next.

2. On the Columns page, click Channel in the Available Fields list and click the right arrow (>) to move this field to the Matrix Column Fields list, then click Next.

3. On the Cell page, click Projected_Sales in the Available Fields list and click the right arrow (>) to move this field to the Matrix Cell Fields list.

4. Repeat this step for Sales, then click Next.

5. On the Totals page, click Next.

   You will add summary totals in a later step.

6. On the Labels page, change the labels and field widths as follows, then click Next:

   ##### Table 43-3 Field Description of Labels page

   Fields | Labels | Width
   --- | --- | ---
   Sales | `Actual Sales` | `7`
   Projected_Sales | `Projected Sales` | `7`
   Geog_Area | `Region` | `10`
   Product | `Product:` | `10`
   Time | `Time:` | `10`
   Channel | `Channel` | `7`

You 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.

- On the Template page, select Predefined template and click Gray, then click Finish to display your report output in the Paper Design view. It should look something like this:

  #### Figure 43-4 Paper Design view for the Express report

  Text description of the illustration expapdes.gif

  1. Choose File > Save As. Save the report in the directory of your choice, and name the report `xprs_910.rdf`.

     Note: It is good practice when you are designing a report to save it frequently under a different file name. If you generate an error or if you do not like some of the changes that you made, then you easily can go back to the previously saved file and make revisions from that point.

  ## 43.3 Refine the Express query

  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.

  Note: For more information, refer to "Selecting data" in the Reports Builder online help.

  In this exercise, you will specify the following dimension values in the Express Query dialog box:

  - projected and actual sales for 1997

  - geographic regions, such as Asia and the Americas

  - product divisions, such as the Accessory and Audio division

  1. In the Paper Layout view, choose Tools > Report Wizard to re-enter the Report Wizard.

  2. In the Report Wizard, on the Data page, click Query Definition.

  3. In the Edit Query dialog box, choose Selector.

  4. In the Selector dialog box, choose Time Period from the Dimensions option.

  5. Click List to select the List tool from the toolbar.

  6. In the List dialog box, choose 1997 from the Available Time Periods list.

  7. Click Select. Notice that "1997" replaces the previous selections.

  8. Click OK.

  9. In the Selector dialog box, choose Geographical Area from the Dimensions option.

  10. Click the Level button to select the Level tool from the toolbar.

  11. In the Select by Level dialog box, choose Continents/Regions in the At level(s) list.

  12. Click OK.

  13. In the Selector dialog box, choose Product from the Dimensions option.

  14. Click the Level button.

  15. In the Select by Level dialog box, choose Divisions in the At level(s) list.

  16. Click OK.

  17. In the Selector dialog box, click OK.

  18. In the Express Query dialog box, click OK.

  19. In the Report Wizard, on the Groups page, click Time in the Matrix Group Fields list and click the left arrow (<) to move this field to the Available Fields list. Note that using Time as a break group is no longer necessary since the Express query will retrieve only aggregate data for 1997. Product should be the only dimension that is listed under Matrix Group Fields.

  20. On the Style page, change the Title to `1997 Sales Report`.

  21. Click Finish to display your report output in the Paper Design view. It should look something like this:

  #### Figure 43-5 Paper Design view for the Express report

  Text description of the illustration exprep1.gif

  1. Save the report as `xprs_920.rdf`.

  2. Optionally, you can compare this report with the one that you previously saved as `xprs_910.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.

  ## 43.4 Add summary columns and custom measures to the data model

  The steps in this section will help you 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 determine this 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, examine the data model:

  #### Figure 43-6 Summary column data model

  Text description of the illustration expdtmdl.gif

  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, then 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 Edit Query dialog box.

  ### 43.4.1 Rename data objects

  1. In the Object Navigator, double-click the view icon next to the Data Model node for your report to display the Data Model view.

  2. In the Data Model view, double-click QP_1 to display the Property Inspector, and set properties:

     - If you want to modify the Express query, choose the Express Query property under the Query node.

     - Under General Information, set the Name property to QP_SALES.

  3. Repeat the above step to change the name of the G_PROJECTED SALES group to G_SALES_DATA.

  4. Save the report as `xprs_931.rdf`.

  ### 43.4.2 Create summary columns

  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.

  1. In the Data Model view, click the Summary Column tool in the tool palette, then click in the G_Cross group to create a summary column.

  2. Double-click the new summary column object (CS_1) to display the Property Inspector, and set properties:

     - Under General Information, set the Name property to CS_PjSalesPerChannel.

     - Under Column, set the Product Order property to G_CHANNEL.

     - Under Summary, set the Source property to PROJECTED_SALES, and set the Reset At property to G_CHANNEL.

  3. Repeat the above steps to create a summary column for actual sales, with the following property settings:

     - Under General Information, set the Name property to CS_SalesPerChannel.

     - Under Column, set the Product Order property to G_CHANNEL.

     - Under Summary, set the Source property to SALES, and set the Reset At property to G_CHANNEL.

  4. Save the report as `xprs_932.rdf`.

  ### 43.4.3 Create a custom measure

  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.

  1. In the Data Model view, double-click the QP_SALES query object to display the Express Query dialog box.

  2. Click Custom Measure at the bottom of the Express Query dialog box.

  3. Click New to display the Custom Measure -- New dialog box.

  4. In the Name field, type `INCREASE`.

  5. In the Description field, type `Increase`.

  6. In the Category list, click Template under Operators. Notice a list of templates appears under Choices.

  7. In the Choices list, click the left parenthesis and click Insert. A left parenthesis appears in the Expression field.

  8. In the Category list, click Measures under Express Objects.

  9. In the Choices list, click F.SALES, and click Insert.

  10. Use the following table to build the expression:

      ##### Table 43-4 Build Expressions

      Category | Sub-category | Choose | or Type:
      --- | --- | --- | ---
      Operators | Numeric | Minus Sign | -
      Express Objects | Measures | F.WHATIF | F.WHATIF
      Operators | Template | Right parenthesis | )
      Operators | Numeric | Forward slash | /
      Express Objects | Measures | F.WHATIF | F.WHATIF
      Operators | Numeric | asterisk | *

  11. Following the asterisk, type `100` in the Expression field.

  12. When you are finished, the expression should look similar to the one in the following figure:

  #### Figure 43-7 Custom Measures Expression

  Text description of the illustration expcmexp.gif

  1. Click OK. Note that "Increase" is listed in the Custom Measures text box in the Custom Measures dialog box.

  2. Click Close.

  3. In the Express Query dialog box, scroll through the Available Measures list. "Increase" now appears alphabetically. Click Increase and click the right arrow (>) to move Increase to the Selected Measures list, below Projected Sales and Sales.

  4. Click OK to return to the Data Model view.

     The group G_SALES_DATA now includes the custom measure that you just created, INCREASE.

  5. Click the Run Paper Layout button in the toolbar to view the report in the Paper Design view. Note that neither the summary columns nor the custom measure are available in the report. This occurred because you have not yet added them as fields to the report layout. You will do this in the next few exercises.

  6. Save the report as `xprs_933.rdf`.

  ## 43.5 Enhance the report layout

  The steps in this section show you how to re-arrange the report layout, add the summary and custom measure columns that you created in Section 43.3, "Refine the Express query", and format objects to further enhance the look of the report. You make these changes using the Paper Layout view and the Paper Design view.

  ### 43.5.1 Insert summary fields in the report

  1. In the Object Navigator, double-click the view icon next to the Paper Layout node to display the Paper Layout view.

  2. Position the windows to display the Object Navigator and the Paper Layout view side-by-side.

  3. In the Object Navigator, fully expand the Paper Layout node to view the nested nodes, such as the M_G_PRODUCT_GRPFR and R_G_PRODUCT nodes.

  4. In the Object Navigator, type `M_G_CROSS_GRPFR` in the Find field to locate this object. In the Paper Layout view, the master cross-matrix frame is selected.

  5. In the Paper Layout view, extend the selected frame down about 1/4 inch.

  6. In the Object Navigator, click F_CHANNEL.

  7. In the Paper Layout view, click the Select Parent Frame button in the toolbar to select the parent frame, R_G_CHANNEL.

     Note: You may need to resize the Paper Layout window to see the Select Parent Frame button, as it is located on the far right of the toolbar.

  1. Extend the frame down about 1/4 inch.

  2. Click the Field tool in the tool palette.

  3. Click and drag a rectangle in the area directly under the F_PROJECTED_SALES field to insert a field object.

  4. Double-click the new field object to display the Property Inspector, and set properties:

     - Under General Information, set the Name property to F_PjSalesPerChannel.

     - Under Field, set the Source property to the CS_PjSalesPerChannel.

  5. Change the format of the field as follows:

     - Click the Fill Color tool in the tool palette and change fill color to light yellow.

     - Click the Text Color tool and change the text color to dark brown.

     - Click the Line Color tool and surround the field with dark brown border lines.

       Note: You can turn Snap to Grid on or off as desired to help you arrange objects in the layout. Chose View > Snap to Grid. A check mark indicates that the option is on.

     1. Repeat the steps above to create another field object, placed directly under F_SALES.

     2. Double-click the new field object to display the Property Inspector, and set properties:

        - Under General Information, set the Name property to F_SalesPerChannel.

        - Under Field, set the Source property to the CS_SalesPerChannel.

          Note: The fill and text colors, as well as the border lines, match the first field that you created, F_PjSalesPerChannel.

        1. Click the Text tool in the tool palette, then click and drag a rectangle to fill the space directly under F_GEO_AREA to create a new boilerplate text object.

        2. In the new boilerplate text object, type Totals:.

        3. Click the Align Center button in the toolbar and make format changes to match the summary fields that you created above.

        4. Click the Paper Design button in the toolbar to view the changes in the Paper Design view.

        5. Save the report as `xprs_941.rdf`.

        ### 43.5.2 Insert the custom measure field into the report

        In this section, you will add a column to display the custom measure that you created in Section 43.4.3, "Create 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 the report and 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, then the report will fail to run.

        1. In the Paper Design view, click the Paper Layout button in the toolbar to display the Paper Layout view. Position the Paper Layout view and Object Navigator side-by-side.

        2. In the Object Navigator, Ctrl-click M_G_PRODUCT_GRPFR and R_G_PRODUCT.

           Tip: M_G_PRODUCT_GRPFR is the underlying master group. It is hidden directly under R_G_PRODUCT. In the Paper Layout view, it may look like only one group is selected when, in fact, both frames are selected.

     3. In the Paper Layout view, expand the width of the selected frames to about 4 3/4 inches.

        Note: Click the Flex On button in the toolbar to turn Flex mode on, or click the Flex Off button if you are unable to resize or move an object.

     1. In the Object Navigator, click M_G_CROSS_GRPFR.

     2. In the Paper Layout view, expand the width of the selected frame to about 4 3/4 inches.

     3. Click the F_GEOG_AREA frame object, then click the Select Parent Frame button in the toolbar to select the parent frame, R_G_GEOG_AREA. Expand the width of the selected frame to about 4 3/4 inches.

     4. Click F_CHANNEL and click the Select Parent Frame button in the toolbar to select the parent frame, R_G_CHANNEL.

     5. Expand the width of the selected frame to about 4 3/4 inches.

     6. Click F_CHANNEL again and expand the width of the object to about 4 3/4 inches.

     7. Click the Field tool in the tool palette, then click and drag a rectangle to the right of the F_SALES object to create a new field object.

     8. Double-click the new field object to display the Property Inspector, and set properties:

        - Under General Information, set the Name property to F_Increase.

        - Under Field, set the Source property to INCREASE.

     9. Click the Run Paper Layout button in the toolbar to run the report. You should see an error message that indicates that F_Increase references INCREASE at a frequency below its group. You are unable to run the report.

        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 that it needs to calculate the value.

     10. Click OK to close the error message.

     11. Click the Paper Layout button in the toolbar to display the Paper Layout view.

     12. In the Paper Layout view, click the F_INCREASE field and delete it.

     13. Click F_SALES, then click the Select Parent Frame button in the toolbar to select the parent frame, R_G_PROJECTED_SALES.

     14. Expand the width of the selected frame to about 4 3/4 inches.

     15. Repeat steps 10 through 13 to create the field object.

     16. With the F_Increase object selected, locate F_INCREASE in the Object Navigator to ensure that it has the same frequency as F_PROJECTED_SALES and F_SALES.

     17. Change the format of the F_INCREASE field as follows:

         - Click the Fill Color tool in the tool palette and change fill color to light yellow.

         - Click the Text Color tool and change the text color to dark brown.

         - Click the Line Color tool and surround the field with dark brown border lines.

         - Click the Bold button in the toolbar to make the text darker and more noticeable.

     18. Click the Text tool in the tool palette, then click and drag a rectangle above F_INCREASE to add a new boilerplate text object for the column title.

     19. In the new boilerplate text object, type `Increase`.

     20. Arrange the text object in the column and change the format to match the field to its left, Actual Sales.

         Note: You may want to turn off Snap to Grid (View > Snap to Grid). in order to extend the text object to cover the entire field. Ensure that the text object is selected when you apply formatting, or it will not take effect.

     1. Click the Run Paper Layout button in the toolbar to view your report in the Paper Design view.

     2. Save the report as `xprs_942.rdf`.

     ### 43.5.3 Sort dimension values

     Suppose you want to change the sorting order of the distribution channels in the 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 that the hierarchy is predefined in the database to place "All Channels" first, with "Indirect" placed last.

     1. In the Data Model view, double-click the query object, QP_SALES to display the Express Query dialog box.

     2. In the Express Query dialog box, click Selector.

     3. In the Dimensions list, click Distribution Channel and click the Sort button.

     4. In the Sort Selection dialog box, select the following values:

        ##### Table 43-5 Values of Sort Selection dialog box

        Criteria | Selection
        --- | ---
        based on | hierarchy
        in order | bottom to top
        in hierarchy | Standard

     1. Click OK in the Sort Selection dialog box.

     2. Click OK in the Selector dialog box.

     3. Click OK in the Express Query dialog box.

     4. Click the Run Paper Layout button in the toolbar to view the report in the Paper Design view.

     5. Save the report as `xprs_943.rdf`.

     ### 43.5.4 Make format changes in the Paper Design view

     1. In the Paper Design view, Shift-click the columns under Projected Sales and Actual Sales, and the Projected Sales total and the Sales total fields.

     2. Click the Currency button in the toolbar to change the format mask to currency.

     3. Click the Align Right button to right justify the values.

     4. Click the Add Decimal Place button twice to insert two decimal places.

     5. Under Increase, click the column.

     6. Click the Percent button to change the format mask to percentage.

     7. Click the Align Center button to center the values.

        The report should now look similar to the following:

     #### Figure 43-8 Paper design output

     Text description of the illustration exprepfi.gif

     1. Save the report as `xprs_944.rdf`.

     ## 43.6 Summary

     Congratulations! You have finished the Express sample report. You now know how to:

     - use the Report Wizard to define a data model and layout.

     - make changes to the Express query by restricting the dimension values.

     - use the Data Model view to add summary and custom measures columns to the report.

     - use the Paper Layout view to insert fields and re-arrange the layout.

     - use the Paper Design view to enhance the look of the report.

     For more information on any of the wizards, views, or properties used in this example, refer to the Reports Builder Online Help, which you can access in two ways:

     - From the Oracle Technology Network (`http://otn.oracle.com/products/reports/`), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.

     - From Reports Builder, choose Help > Help Contents.

       Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (`http://otn.oracle.com/docs/products/reports/`). Instructions for replacing your help file are included in the readme.txt in the download file.

| Copyright © 2002, 2003 Oracle Corporation. All Rights Reserved. |
Home | Contents | Index