7
Building a Report Using Express Data

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.

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.

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.

Table 7-1 Features demonstrated in this sample report of Express data

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.	Section 7.2, "Refining the Express query"
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.	Section 7.4, "Enhancing the report layout"

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

7.1 Creating an Express report with the Report Wizard

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.

For online help on this task, choose Help->Report Builder Help Topics On the Index page, type... report wizard, about Then click Display to view help topic... Report Wizard: Welcome page

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.

1. If the Welcome page of the Report Wizard appears, click Next.

2. On the Style page, type 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.

3. Click Next.

4. On the Type page, click Express query, and click Next. If the Type page does not appear, ensure that you have properly configured the Report Builder to run with Express data.

5. On the Data page, click Express Query.

   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.

6. In the Express Query dialog box, click Attach Database to choose the path and name of the database that you want to attach to your session.

7. In the Attach Database dialog box, select the directory labeled /oec62/. Select xademo.db. This is the sample database that is provided with Express Server.

8. Click Open to attach the database to your session.

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

10. Click to move Sales and Projected Sales to the Selected Measures list box. The Express Query dialog will look similar to the one below:
    

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

12. On the Data page, click Next.

    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.

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

14. Repeat this step for TIME so that the Matrix Group Fields box appears as follows:
    

15. Click Next.

16. On the Rows page, click GEOG_AREA.

17. Click .

18. Click Next.

19. On the Columns page, click CHANNEL in the Available Fields list box.

20. Click .

21. Click Next.

22. On the Cells page, select PROJECTED SALES in the Available Fields list box and click to move this field to the Matrix Cell Fields list box.

23. Repeat this step for SALES.

24. On the Totals page, click Next. You will add summary totals in a later step.

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

    Table 7-2 Labels

    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.

26. On the Template page, click Predefined template if it is not already selected, and click Cyan Grid Landscape in the list box.

27. Click Finish. The report output automatically displays in the Live Previewer and should look similar to the following figure.

28. Choose File->Save As. Save the report in the directory of your choice, and name the report 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.

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

For online help on this task, choose Help->Report Builder Help Topics On the Index page, type... selecting data In the Topics Found dialog box, select... selecting data. Then click Display to view help topic... Selecting data

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 Live Previewer, choose Tools->Report Wizard.

2. On the Data page, click Express Query.

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

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

5. Click 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, click Geographical Area from the Dimensions option.

10. Click 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, click Product from the Dimensions option.

14. Click .

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, click TIME in the Matrix Groups 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 . 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. Click Finish. Your report should look similar to the following figure:
    

23. Save the report as xprs_720.rdf.

24. If you want, compare this report with the one that you previously saved as 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.

7.3 Adding summary columns and custom measures to your data model

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.

For online help on this task, choose Help->Report Builder Help Topics On the Index page, type... dimension sorting In the Topics Found dialog box, select... new feature Then click Display to view help topic... Dimension sorting

In a later step, you will sort dimension values using the Sort tool in the Express Query dialog box.

7.3.1 Renaming data objects

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

2. Click QE_1.

3. Select Tools->Property Palette.

   Tip: If you want to modify your Express query, click the Express Query property under the Query node.

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

5. Press Enter or click outside of the property to accept the value. Close the Property Palette.

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

7. Save your report as xprs_731.rdf.

7.3.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 , then click the G_Cross group.

2. Select Tools->Property Palette.

3. Set the following properties for projected sales:

   Table 7-3 Summary Column properties for Projected Sales

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

4. Press Enter or click outside of the property to accept the value. Close the Property Palette.

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

   Table 7-4 Summary Column properties for Actual Sales

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

6. Save your report as xprs_732.rdf.

7.3.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 QE_Sales query object to open the Express Query dialog box.

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

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

4. In the Name box type INCREASE.

5. In the Description box type Increase.

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

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

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

9. Select F.SALES and then click Insert.

10. Use the following table to build the expression:

    Table 7-5 Categories and Choices for custom measure Increase

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

12. When you are finished, your expression should look like the following figure:
    

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. Click Increase and then click . 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 you just created, INCREASE.

17. Click to view the report in Live Previewer. 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 your report as xprs_733.rdf.

7.4 Enhancing the report layout

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.

7.4.1 Inserting summary fields in the report

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

2. Arrange your workspace to display the Object Navigator and the Layout Model view side-by-side. Expand the Layout Model, Main Section, Body, and the remaining nested nodes, such as the MG_PRODUCT_GRPFR and R_G_PRODUCT nodes. Your workspace should look similar to the following figure:

3. In the Object Navigator, type 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:
   

4. Extend the selected frame down about 1/4 inch as illustrated in the following figure:
   

5. Click F_CHANNEL in the Object Navigator.

6. Click to select the parent frame, R_G_CHANNEL, 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.

7. Extend the frame down about 1/4 inch as illustrated in the following figure:
   

8. Click .

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

10. Tools->Property Palette.

11. Set the following properties:

    Table 7-6 Projected Sales per Channel field properties

    Node	Property	Value
    General Information	Name	F_PjSalesPerChannel
    Field	Source	CS_PjSalesPerChannel

12. Arrange this field and change the format as follows:
    • Click to change fill color to light yellow.

    • Click to change the text color to dark brown.

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

    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.

13. Repeat steps 8 through 11 except place the new object directly under F_SALES. Set the following properties:

    Table 7-7 Sales per Channel field properties

    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.

14. Click .

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

16. Type Totals:.

17. Align the text object to center by clicking and make format changes to match the summary fields you created.

18. Click to view the changes in the Live Previewer. Your report should look similar to the one displayed here:
    

19. Save the report as xprs_741.rdf.

7.4.2 Inserting the custom measure field into the report

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.

1. In Live Previewer, click to display the Layout Model view. Ensure that the Layout Model 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 Layout Model view, it may look like only one group is selected when, in fact, both frames are selected.

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

   Tip: Click to turn Flex mode on, or click 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 Layout Model, expand the width of the selected frame to about 4 3/4 inches. It should look similar to the figure below:
   

6. Click the F_GEOG_AREA object, then click to select the parent frame, R_G_GEOG_AREA:
   

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

8. Click F_CHANNEL and click to select the parent frame, R_G_CHANNEL.

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

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

11. Click .

12. Click and drag a box to the right of the F_SALES object. It should look similar to the figure below:
    

13. Tools->Property Palette.

14. Set the following properties:

    Table 7-8 Increase field properties

    Node	Property	Value
    General Information	Name	F_Increase
    Field	Source	INCREASE

15. Click to run the report. You should see an error indicating that F_Increase references INCREASE at a frequency below its group. You are unable to run your 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 it needs to calculate the value.

16. Click OK to close the error message.

17. Click to display the Layout Model view.

18. Select the field F_INCREASE and delete it.

19. Click F_SALES and then click to select the parent frame, R_G_PROJECTED_SALES.

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

21. Repeat steps 11 through 14 to create the field object. Your layout model should resemble the following figure:
    

22. 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. The Object Navigator should look similar to the figure below:

23. Change the format of the F_Increase field as follows:
    • Click to change fill color to light yellow.

    • Click to change the text color to dark brown.

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

    • Click to make the text darker and more noticeable.

24. Click .

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

26. Type Increase.

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

    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:
    

28. Click . The report should look similar to the figure below:
    

29. Save the report as xprs_742.rdf.

7.4.3 Sorting dimension values

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.

1. In the Data Model view, double-click the query object, QE_SALES.

2. Click Selector in the Express Query dialog box.

3. In the Dimensions list, select Distribution Channel and then click .

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

   Table 7-9 Sort Selections

   Criteria	Selection
   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 report should look similar to the figure below. Note that Indirect is first while All Channels is last in the order:
   

9. Save the report as xprs_743.rdf.

7.4.4 Making format changes in Live Previewer

1. In Live Previewer, shift-click the columns under Projected Sales and Actual Sales, and the Projected Sales total and the Sales total fields.

2. Click to change the format mask to currency.

3. Click to right justify the values.

4. Click twice to insert two decimal places

5. Click the column under Increase.

6. Click to change the format mask to percentage.

7. Click to center the values.

8. The report should now look similar to the figure below:
   

9. Save the report as xprs_744.rdf.

7.5 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 Layout Model view to insert fields and re-arrange the layout.

• Use the Live Previewer to enhance the look of the report.

For more information about Express, see the online help:

For online help on this topic, choose Help->Report Builder Help Topics On the Index page, type... Express, working with Then click Display to view help topic... Working with Express