6
Building a Report Using Express Data

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.

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.

describes the steps that you will take to create this report.

Table 6-1 Features demonstrated in this Express example

Feature	Location
Use the Report Wizard to define the Express query and create a first draft of the report.	Section 6.2, "Creating an Express report with the Report Wizard"
Streamline the Express query by specifying dimension values.	Section 6.3, "Refining the Express query"
Add summary and calculated totals using the Data Model view.	Section 6.4, "Adding 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 6.5, "Enhancing 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.

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

6.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 Oracle9i 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 6-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.

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

6.2 Creating an Express report with the Report Wizard

You can use the Report Wizard as a great way 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.

Note: For more information, refer to "Building a standard report" in the Reports Builder online help.

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. If the Welcome page of the Report Wizard appears, then choose Use the Report Wizard, and click OK.

2. On the Layout page, select Create Paper Layout only, then click Next.

3. On the Style page, type Sales Report as the Title, and choose Matrix with Group as the report style.

4. Click Next.

5. On the Data Source Type page, choose Express Server Query, and click Next.

6. On the Data page, click Query Definition.

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

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

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

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

10. In the Express Query dialog box, CTRL-click to select Sales and Projected Sales from the Available Measures list.

11. Click the right arrow to move Sales and Projected Sales to the Selected Measures list box. The Express Query dialog box looks similar to the following figure:

    

    Text description of the illustration salessel.gif

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

13. On the Data page, click Next.

14. On the Groups page, select PRODUCT in the Available Fields list box and click the right arrow. to move this field to the Matrix Group Fields list box.

15. Select Level1, then select TIME and click the right arrow so that the Matrix Group Fields box appears as follows:

    

    Text description of the illustration mtrxgrpf.gif

16. Click Next.

17. On the Rows page, click GEOG_AREA.

18. Click the right arrow.

19. Click Next.

20. On the Columns page, choose CHANNEL in the Available Fields list box.

21. Click the right arrow.

22. Click Next.

23. On the Cells page, select PROJECTED SALES in the Available Fields list box and choose the Add One button to move this field to the Matrix Cell Fields list box.

24. Repeat this step for SALES.

25. Click Next.

26. On the Totals page, click Next.

    You will add summary totals in a later step.

27. On the Labels page, change the following labels and widths:

    SALES	Actual Sales	7
    PROJECTED_SALES	Projected Sales	7
    GEOG_AREA	Region	10
    PRODUCT	Product:	10
    TIME	Time:	10
    CHANNEL	Channel	7

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

28. Click Next.

29. On the Template page, choose Predefined template if it is not already selected, and choose Gray in the list box.

30. Click Finish. The report output automatically displays in the Paper Design view and should look similar to the following figure.

    

    Text description of the illustration expapdes.gif

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

6.3 Refining 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 Design view, choose Tools > Report Wizard.

2. On the Data page, choose 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 the List button to select the List tool from the toolbar.

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

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

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

16. Click OK.

17. In the Selector dialog box, click OK.

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

19. On the Groups page, choose TIME in the Matrix Group Fields list box. Note that using TIME as a break group is no longer necessary since the Express query will retrieve only aggregate data for 1997.

20. Click the Remove One button. PRODUCT should be the only dimension that is listed in the Matrix Group Fields list box.

21. On the Style page, change the title to 1997 Sales Report.

22. Choose Finish. Your report should look similar to the following figure:

    

    Text description of the illustration exprep1.gif

23. Save the report as xprs_920.rdf.

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

6.4 Adding 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:

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.

6.4.1 Renaming data objects

1. In the Object Navigator, double-click the Data Model button under your report's node if you are not already viewing the Data Model view.

2. Select QP_1.

3. Choose Tools > Property Inspector.

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

4. Under the General Information node, change the Name property to QP_SALES.

5. Press ENTER or click outside of the property to accept the value. Close the Property Inspector.

6. Repeat steps 2 through 5 and change the Name property of the G_PROJECTED SALES group to G_SALES_DATA.

7. Save the report as xprs_931.rdf.

6.4.2 Creating 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 icon, then click the G_Cross group.

2. Choose Tools > Property Inspector.

3. Set the following properties for projected sales:

   General Information	Name	CS_PjSalesPerChannel
   Column	Product Order	G_CHANNEL
   Summary	Source	PROJECTED_SALES
   Summary	Reset At	G_CHANNEL

4. Press ENTER or click outside of the property to accept the value.

5. Close the Property Inspector.

6. Repeat steps 1 through 4 to create a summary column for actual sales. Set the following properties:

   General Information	Name	CS_SalesPerChannel
   Column	Product Order	G_CHANNEL
   Summary	Source	SALES
   Summary	Reset At	G_CHANNEL

7. Save the report as xprs_932.rdf.

6.4.3 Creating 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 open the Express Query dialog box.

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

3. Choose New to open the Custom Measure -- New dialog box.

4. In the Name box, type INCREASE.

5. In the Description box, type Increase.

6. Choose Template under Operators in the Category box. Notice a list of templates appears under Choices.

7. Select the left parenthesis and choose Insert. A left parenthesis appears in the Expression box.

8. Choose Measures under Express Objects in the Category box.

9. Select F.SALES, and choose Insert.

10. Use the following table to build the expression:

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

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

    

    Text description of the illustration expcmexp.gif

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

14. Click Close.

15. In the Express Query dialog box, scroll through the Available Measures box. "Increase" now appears alphabetically. Choose Increase and choose the Add One button. "Increase" appears in the Selected Measures box, below Projected Sales and Sales.

16. Click OK to return to the Data Model.

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

17. Click the Run Paper Layout button 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.

18. Save the report as xprs_933.rdf.

6.5 Enhancing 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 6.3, "Refining 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.

6.5.1 Inserting summary fields in the report

1. In the Object Navigator, double-click the Paper Layout icon under the report's node to display the Paper Layout view.

2. Arrange the workspace to display the Object Navigator and the Paper Layout view side-by-side. Expand the Paper Layout, Main Section, Body, and the remaining nested nodes, such as the M_G_PRODUCT_GRPFR and R_G_PRODUCT nodes.

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

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

5. In the Object Navigator, choose F_CHANNEL.

6. Click the Select Parent Frame button 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.

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

8. Click the Field button.

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

10. Choose Tools > Property Inspector.

11. In the Property Inspector, set the Source property under the Field node to the value CS_PjSalesPerChannel.

12. Select the object in the Object Navigator and rename it to F_PjSalesPerChannel.

13. Arrange this field and change the format as follows:
    • Click the Fill Color button to change fill color to light yellow.

    • Click the Text Color button to change the text color to dark brown.

    • Click the Line Color button to 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. Select View > Snap to Grid. A check mark indicates that the option is on.

14. Repeat steps 8 and 9 except place the new object directly under F_SALES.

15. In the Property Inspector, set the Source property under the Field node to the value CS_SalesPerChannel.

16. Select the object in the Object Navigator and rename it to F_SalesPerChannel.

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

17. Click the Text button.

18. Click and drag a rectangle to fill the space directly under F_GEO_AREA.

19. In the rectangle, type Totals:.

20. Align the text object to center by clicking the Align Center button and make format changes to match the summary fields that you created.

21. Click the Paper Design icon to view the changes in the Paper Design view.

22. Save the report as xprs_941.rdf.

6.5.2 Inserting 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 6.4.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 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 icon to display the Paper Layout view. Ensure that the Paper Layout view and Object Navigator are placed 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 to turn Flex mode on, or click the Flex Off button to turn Flex mode off if you are unable to resize or move an object.

4. In the Object Navigator, click M_G_CROSS_GRPFR.

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

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

7. Choose F_CHANNEL and click the Select Parent Frame button to select the parent frame, R_G_CHANNEL.

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

9. Choose F_CHANNEL again and expand the width of the object to about 4 3/4 inches.

10. Click the Field button.

11. Click and drag a box to the right of the F_SALES object.

12. Choose Tools > Property Inspector.

13. Set the following properties:

    General Information	Name	F_Increase
    Field	Source	INCREASE

14. Click the Run Paper Layout icon 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.

15. Click OK to close the error message.

16. Click the Paper Layout icon to display the Paper Layout view.

17. Select the F_INCREASE field and delete it.

18. Choose F_SALES, then choose the Select Parent Frame button to select the parent frame, which is called R_G_PROJECTED_SALES.

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

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

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

22. Change the format of the F_Increase field as follows:
    • Click the Fill Color button to change fill color to light yellow.

    • Click the Text Color button to change the text color to dark brown.

    • Click the Line Color button to surround the field with dark brown border lines.

    • Click the Bold button to make the text darker and more noticeable.

23. Click the Text button.

24. Click and drag a rectangle above F_Increase to add the column title.

25. In the rectangle, type Increase.

26. 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 on the View menu 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.

27. Click the Run Paper Layout icon to view your report.

28. Save the report as xprs_942.rdf.

6.5.3 Sorting 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, QE_SALES.

2. Choose Selector in the Express Query dialog box.

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

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

   based on	hierarchy
   in order	bottom to top
   in hierarchy	Standard

5. Click OK in the Sort Selection dialog box.

6. Click OK in the Selector dialog box.

7. Click OK in the Express Query dialog box.

8. Click the Run Paper Layout icon to view the report.

9. Save the report as xprs_943.rdf.

6.5.4 Making 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 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:

   

   Text description of the illustration exprepfi.gif

8. Save the report as xprs_944.rdf.

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