|
Oracle® Reports Building Reports
10g Release 2 (10.1.2) B13895-01 |
|
 Previous |
 Next |
|
Oracle® Reports Building Reports
10g Release 2 (10.1.2) B13895-01 |
|
|
Previous |
Next |
In this chapter, you will learn about reports that include data from an Oracle Express pluggable data source (PDS), which is provided with Oracle Reports. By following the steps in this chapter, you can generate the report output shown in Figure 47-1.
|
Note: The Oracle Express PDS is deprecated in Oracle Reports 10g Release 2 (10.1.2). For information, refer to the A Guide to Functional Changes Between Oracle Reports 6i and 10g, available on the Oracle Technology Network Oracle Reports 10g page (http://www.oracle.com/technology/products/reports/index.html). Additionally, the Oracle Express PDS does not work on any UNIX platform in the Oracle Developer Suite.
|
Concepts
Oracle 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 Oracle 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.
You will build a report that summarizes the yearly projected and actual sales for each region and sales channel in a product division. 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.
As you build this example report, you will:
Define the Express query using the Report Wizard to create the initial report.
Refine the Express query by specifiying dimension values.
To see a sample report that uses an Oracle Express PDS, open the examples folder named Express, then open result\xprs.rdf. For details on how to open it, see "Accessing the Example Reports" in the Preface. The example files used in this chapter are listed and described in Table 47-1.
Table 47-1 Example report files
| File | Description |
|---|---|
|
|
The final report you will have created when you finish this chapter. |
|
Note: For more information on tips for Express data, refer to "About working with Express data" in the Oracle Reports online Help. |
To build the example in this chapter, you must have the example files we have provided (see "Example Scenario", above), as well as access to an Oracle Express data source.
Before you start building this Express report, you must have already configured Reports Builder to run with Express Server.
|
Note: For more information, refer to "About configuring the Express data source" in the Oracle Reports online Help. |
The steps in this section will help you to create the initial report, which 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.
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.
If the Welcome page displays, click Next.
On the Report Type page, select Create Paper Layout only, then click Next.
On the Style page, type Sales Report as the Title, and choose Matrix with Group as the report style, then click Next.
On the Data Source page, choose Express Server Query, then click Next.
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. Click OK. |
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.
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.
Click Open to attach the database to the session.
In the Express Query dialog box, Ctrl-click to select Sales and Projected Sales in the Available Measures list.
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:
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.
On the Data page, click Next.
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:
click Next.
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.
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.
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.
Repeat this step for Sales, then click Next.
On the Totals page, click Next.
You will add summary totals in a later step.
On the Labels page, change the labels and field widths as follows, then click Next:
Table 47-2 Field Description of Labels page
| Fields | Labels | Width |
|---|---|---|
|
Projected_Sales |
|
|
|
Sales |
|
|
|
Geog_Area |
|
|
|
Product |
|
|
|
Time |
|
|
|
Channel |
|
|
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 47-4 Paper Design view for the Express report
Save the report as 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. |
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 Oracle Reports 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
To refine the Express query:
In the Paper Layout view, choose Tools > Report Wizard to re-enter the Report Wizard.
In the Report Wizard, on the Data page, click Query Definition.
In the Express Query dialog box, click Selector.
In the Selector dialog box, choose Time Period from the Dimensions option.
Click the List button to select the List tool from the toolbar.
In the List dialog box, choose 1997 from the Available Time Periods list.
Click Select. Notice that "1997" replaces the previous selections.
Click OK.
In the Selector dialog box, choose Geographical Area from the Dimensions option.
Click the Level button to select the Level tool from the toolbar.
In the Select by Level dialog box, choose Continents/Regions in the at level(s) list.
Click OK.
In the Selector dialog box, choose Product from the Dimensions option.
Click the Level button.
In the Select by Level dialog box, choose Divisions in the at level(s) list.
Click OK.
In the Selector dialog box, click OK.
In the Express Query dialog box, click OK.
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.
On the Style page, change the Title to 1997 Sales Report.
Click Finish to display your report output in the Paper Design view. It should look something like this:
Figure 47-5 Paper Design view for the Express report
Save the report as xprs_920.rdf.
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.
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:
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.
In the Object Navigator, double-click the view icon next to the Data Model node for your report to display the Data Model view.
In the Data Model view, double-click QP_1 to display the Property Inspector, and set the following properties:
To modify the Query definition, click the Query Definition property value field.
Under General Information, set the Name property to QP_SALES.
Repeat the above step to change the name of the G_PROJECTED SALES group to G_SALES_DATA.
Save the report as xprs_931.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.
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.
Double-click the new summary column object (CS_1) to display the Property Inspector, and set the following 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.
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.
Save the report as xprs_932.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.
In the Data Model view, double-click the QP_SALES query object to display the Express Query dialog box.
Click Custom Measure at the bottom of the Express Query dialog box.
Click New to display the Custom Measure -- New dialog box.
In the Name field, type INCREASE.
In the Description field, type Increase.
In the Category list, click Template under Operators. Notice a list of templates appears under Choices.
In the Choices list, click the left parenthesis and click Insert. A left parenthesis appears in the Expression field.
In the Category list, click Measures under Express Objects.
In the Choices list, click F.SALES, and click Insert.
Use the following table to build the expression:
Following the asterisk, type 100 in the Expression field.
When you are finished, the expression should look similar to the one in the following figure:
Click OK. Note that "Increase" is listed in the Custom Measures text box in the Custom Measures dialog box.
Click Close.
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.
Click OK to return to the Data Model view.
The group G_SALES_DATA now includes the custom measure that you just created, Increase.
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.
Save the report as xprs_933.rdf.
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 47.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.
In the Object Navigator, double-click the view icon next to the Paper Layout node to display the Paper Layout view.
Position the windows to display the Object Navigator and the Paper Layout view side-by-side.
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 under Paper Layout > Main Section > Body.
|
Note: If you find M_G_TIME_GRPFR and R_G_TIME as the frame and repeating frame under Body, you may rename them to M_G_PRODUCT_GRPFR and R_G_PRODUCT respectively, using the Property Inspector. |
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.
In the Paper Layout view, extend the selected frame down about 0.25 inches (0.5 centimeters).
In the Object Navigator, click F_CHANNEL.
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. |
Extend the frame down about 0.25 inches (0.5 centimeters).
Click the Field tool in the tool palette.
Click and drag a rectangle in the area directly under the F_PROJECTED_SALES field to insert a field object.
Double-click the new field object to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to F_PjSalesPerChannel.
Under Field, set the Source property to the CS_PjSalesPerChannel.
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. |
Repeat the steps above to create another field object, placed directly under F_SALES.
Double-click the new field object to display the Property Inspector, and set the following 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. |
Click the Text tool in the tool palette, then click and drag a rectangle to fill the space directly under F_GEOG_AREA to create a new boilerplate text object.
In the new boilerplate text object, type Totals:.
Click the Align Center button in the toolbar and make format changes to match the summary fields that you created above.
Click the Paper Design button in the toolbar to view the changes in the Paper Design view.
Save the report as xprs_941.rdf.
In this section, you will add a column to display the custom measure that you created in Section 47.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. |
To insert the custom measure field into the report:
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.
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. |
In the Paper Layout view, expand the width of the selected frames to about 4.75 inches (12 centimeters).
|
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. |
In the Object Navigator, click M_G_CROSS_GRPFR.
In the Paper Layout view, expand the width of the selected frame to about 4.75 inches (12 centimeters).
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.75 inches (12 centimeters).
Click F_CHANNEL and click the Select Parent Frame button in the toolbar to select the parent frame, R_G_CHANNEL.
Expand the width of the selected frame to about 4.75 inches (12 centimeters).
Click F_CHANNEL again and expand the width of the object to about 4.75 inches (12 centimeters).
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.
Double-click the new field object to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to F_Increase.
Under Field, set the Source property to INCREASE.
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. 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.
Click OK to close the error message.
Click the Paper Layout button in the toolbar to display the Paper Layout view.
In the Paper Layout view, click the F_INCREASE field and delete it.
Click F_SALES, then click the Select Parent Frame button in the toolbar to select the parent frame, R_G_PROJECTED_SALES.
Expand the width of the selected frame to about 4.75 inches (12 centimeters).
Repeat steps 10 through 11 to create the field object.
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.
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.
In the Object Navigator, find and select the frame M_G_PROJECTED_SALES_HDR.
In the Paper Layout view, expand the width of the selected frame to about 4.75 inches (12 centimeters).
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.
In the new boilerplate text object, type Increase.
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) 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. |
Click the Run Paper Layout button in the toolbar to view your report in the Paper Design view.
Save the report as xprs_942.rdf.
In this example, you will 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.
To sort the dimension values in the report:
In the Data Model view, double-click the query object, QP_SALES to display the Express Query dialog box.
In the Express Query dialog box, click Selector.
In the Dimensions list, choose Distribution Channel and click the Sort button.
In the Sort Selection dialog box, select the following values:
Click OK in the Sort Selection dialog box.
Click OK in the Selector dialog box.
Click OK in the Express Query dialog box.
Click the Run Paper Layout button in the toolbar to view the report in the Paper Design view.
Save the report as xprs_943.rdf.
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.
Click the Currency button in the toolbar to change the format mask to currency.
Click the Align Right button to right justify the values.
Click the Add Decimal Place button twice to insert two decimal places.
Under Increase, click the column.
Click the Percent button to change the format mask to percentage.
Click the Align Center button to center the values.
The report should now look similar to the following:
Save the report as xprs_944.rdf.
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 Oracle Reports online Help, which is available in Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 3.1.1, "Using the Oracle Reports online Help".
