18.9 Managing Report Output

18.9.1.1 About Report Queries

You can print a report region by defining a report query as a Shared Component. A report query identifies the data to be extracted. Unlike SQL statements contained in regions, report queries contain SQL statements that are validated when you save the query. Note that report queries must be SQL statements, not functions returning SQL statements.

You can associate a report query with a report layout and download it as a formatted document. If no report layout is selected, a generic layout is used. The generic layout is intended to be used to test and verify a report query. When using the generic layout option and multiple source queries are defined, only the first result set is included in the print document. The reports can include session state of the current application.

To make these reports available to end users, you then integrate them with an application. For example, you can associate a report query with a button, list item, branch, or other navigational component that enables you to use URLs as targets. Selecting that item then initiates the printing process.

18.9.1.2 Creating a Report Query

Note that the availability of the report query options depends on how your service administrator configures report printing for your instance. All options described in these steps may not be available to you.

To create a report query:

1. Navigate to the Shared Components page:

   1. On the Workspace home page, click Application Builder.

   2. Select an application.

   3. On the Application home page, click Shared Components.

      The Shared Components page appears.

2. Under Reports, click Report Queries.

3. Click Create.

4. For Query, specify the following information:

   1. Report Query Name - Enter a name to identify the report query. When referencing a report query as a link target, the report query name is part of the request string.

   2. Output Format - Select the report output format. Valid options include:

      • PDF - Adobe Portable Document Format.

      • Word - Microsoft Word Rich Text Format.

      • Excel - Microsoft Excel format. Note that this is not a true .xls file because the content is HTML-based.

      • HTML

      • XML - Extensible Markup Language.

   3. Item - Select the item to hold the format value. You can use this item to determine the output format at runtime.

   4. View File As - Select how the web browser opens the report query document. Options include:

      • Attachment - Displays a File Open/Save dialog box when the user clicks the report query download button.

      • Inline - Displays the report query document inside the Browser window.

   5. Session State - Select this check box to reference session state values, such as the values of page items in the XML structure of your report.

   6. Click Next.

5. For SQL Query, enter a SQL statement directly or click Query Builder to build a SQL statement by clicking and pointing.

   To reference applications and page items in the SQL statement, reference them as bind variables. Click Set Bind Variables to be able to enter in values for each bind variable and test the query to ensure the expected output is returned.

6. For Download Definition, specify the following:

   1. Data Source for Report Layout - Use this option to download a report definition export file.

      Tip:

      The report query definition is what a developer would use as the source for tools like BI Publisher Desktop or Altova Stylevision to create the report layout. Some tools accept both an XML export of the data as well as an XML scheme definitions as source, other tools may only accept one or the other. If the source query does not return data at design time, then XML schema provides a way to export the definition without data.

      Select XML Data or XML Schema and click Download.

      XML Schema creates XSD file (that is, a XML schema definition of your XML structure.)

   2. Add Query - Click this button to add another query to the report query. Adding another query enables the production of complex reports with more than one output component (for example, a chart and a report based on two different queries).

   3. Create Report Query - Click this button to exit the wizard early. The Report Layout Source defaults to use the generic report layout.

   4. Click Next.

7. For Upload Report Layout, specify the following:

   1. Report Layout Source - Specify how the report layout is derived. If you choose the report based file layout then the layout name and report layout file must be provided.

   2. Layout Name - Enter a layout name (optional). The default is the report query name.

   3. Report Layout File - Click Browse and select a Rich Text Format (RTF).

   4. Click Next.

8. For Confirm:

   1. Query Name - Identifies the query.

   2. Report Layout - Identifies the report layout you selected.

   3. Output Format - Identifies the format for this report query.

   4. Derive from Item - Identifies the item to hold the format information.

   5. URL - To integrate this report with your application, use the displayed URL as the target for a button, list item, link, or other navigational component. End users can click a button, for example, to start the printing process.

   6. Test Report - Click this to preview your report. If you have chosen to use a generic report layout then the Test Report does not produce an output.

   7. Click Finish.

      The Report Query is created and saved to Shared Components.

18.9.1.3 Editing a Report Query

To edit a report query:

1. Navigate to the Shared Components page:

   1. On the Workspace home page, click Application Builder.

   2. Select an application.

   3. On the Application home page, click Shared Components.

      The Shared Components page appears.

2. Under Reports, click Report Queries.

3. Select the appropriate report query.

4. On the Edit page, edit the appropriate attributes.

5. Click Apply Changes.

18.9.1.4 Copying a Report Query

To copy a report query:

1. Navigate to the Shared Components page:

   1. On the Workspace home page, click Application Builder.

   2. Select an application.

   3. On the Application home page, click Shared Components.

      The Shared Components page appears.

2. Under Reports, click Report Queries.

3. On the Report Queries page, click Copy.

4. On the Copy Report Query, select the query you want to copy, enter a name for the report query, and click Copy.

   The copy appears in the query list.

18.9.2.1 About Report Layouts

To format either a classic report region or report query, you associate it with a report layout. Using report layouts renders the data in a printer-friendly format. If you do not select a report layout, a default XSL-FO layout is used. The default XSL-FO layout is always used for rendering Interactive Report regions.

When creating and using report layouts, you can:

• Take advantage of the default layouts for report regions and generic layouts for report queries provided with Oracle Application Express.

• Utilize the built-in XSL-FO-based layouts for report regions by copying and customizing the code. You can edit several attributes for report regions that control page size, fonts, colors, and so on.

• Create RTF or XSL-FO report layouts to customize the report look and feel. To use RTF report layouts, your Oracle Application Express service administrator must select the Advanced setting for your site. See "Configuring Report Printing" in Oracle Application Express Administration Guide.

18.9.2.2 About Report Layout Options

You can create a report layout based on one of these options:

• Generic Columns - A generic report layout works with most query result sets. With this layout, the number of columns is automatically adjusted when generating the printable document.

  Many report layout attributes can be defined declaratively for report regions using the built-in XSL-FO default layout. This step allows for creating customizable copies of the built-in default XSL-FO layout, if additional control over the report layout is needed.

• Named Columns - A named column report layout is a query-specific report layout designed to work with a defined list of columns in the query result set. This type of layout is used for custom-designed layouts when precise control of the positioning of page items and query columns is required.

Note that the availability of the Report Layout options depends on how your site administrator configured the report printing settings at your site. All options described in these steps may not be available to you.

18.9.2.3 Creating a Report Layout

To create a report layout:

1. Navigate to the Shared Components page:

   1. On the Workspace home page, click Application Builder.

   2. Select an application.

   3. On the Application home page, click Shared Components.

      The Shared Components page appears.

2. Under Reports, click Report Layouts.

3. Click Create.

   The Create Report Layout wizard appears.

4. For Report Layout Type, select an option and click Next:

   • Generic Columns (XSL-FO) - Uses the default template to populate the report. In the next step, you can customize the default code.

   • Named Columns (XSL-FO) - Requires that you upload an XSL-FO or RTF file in the next step.

5. For Layout Source, review and edit the appropriate information. The options that appear on this page depend on the layout type you select:

   • If you selected Generic Columns:

   1. Report Layout Name - Enter a name to identify the report layout when associating it with a report query or report region.

   2. Report Layout - The report layout is the XSL-FO based definition of the page formatting. All attributes defining page size, orientation, fonts, styles, and so on, are defined in this section.

   3. Report Column Heading - Defines the look of each cell in the report heading row.

   4. Report Column - Defines the look of each cell for all report rows.

   5. Report Column Width - This width is computed at runtime or can be derived from the report column definition of a report region.

   • If you selected Named Columns:

   1. Layout Name - Enter a name to identify the report layout when associating it with a report query or report region.

   2. Report Layout File - Upload the file containing the report layout.

6. Click Create Layout.

18.9.2.4 Editing a Report Layout

You can edit a generic column report layout directly in Application Express. However, to edit a named column report layout, you must download the current file, edit it, and then upload it again.

To edit a report layout:

1. Navigate to the Shared Components page:

   1. On the Workspace home page, click Application Builder.

   2. Select an application.

   3. On the Application home page, click Shared Components.

      The Shared Components page appears.

2. Under Reports, click Report Layouts.

3. On the Report Layouts page, select the layout you want to edit.

4. For generic column layouts, edit the layout directly on the Edit Report Layout page and click Apply Changes.

5. For named column layouts, click Download and save the file to your computer.

   Edit the file and then upload the updated version as a new report layout.

18.9.2.5 Copying a Report Layout

You can copy a report layout to edit and save.

To copy a report layout:

1. Navigate to the Shared Components page:

   1. On the Workspace home page, click Application Builder.

   2. Select an application.

   3. On the Application home page, click Shared Components.

      The Shared Components page appears.

2. Under Reports, click Report Layouts.

3. On the Report Layouts page, click Copy.

4. On the Copy Report Layout page, select the layout you want to copy, enter a name for the copy, and click Copy.

   The copy appears in the layout list.

5. On Copy Report Layout:

   1. Copy Report Layout - Select the layout you want to copy.

   2. Report Layout Name - Enter a name for the copy.

   3. Click Copy.