Learn about report queries.

In Oracle APEX, a report query is a SQL query that is used to fetch data to be displayed in a report. The data source can be a table, a SQL query, or a REST Data Source. The report query defines the columns to be included in the report, the data source, and any conditions or sorting to be applied to the data. Note that report queries must be SQL statements. Functions returning SQL statements are not supported.

You can print a report region by defining a report query as a Shared Component.

To download a report query as a formatted document, you associate it with a report layout. If no report layout is selected, APEX uses a generic layout. You can use the generic layout to test and verify a report query. When using the generic layout option, and when 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.

The following Print Server Types do not support generic report layouts:

• Oracle Document Generator Pre-built Function
• APEX Office Print

To make these reports available to 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.

Create a report query in Shared Components.

To create a report query:

1. Navigate to the Shared Components page:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components.

      The Shared Components page appears.

2. Under Files and Reports, click Report Queries.
3. Click Create.
4. In Create Report Query, specify the following:
   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. Report Layout - Select the Report Layout. If you have not yet created a Report Layout, you can specify one later.
   3. Click Next.
5. In Report Query Definition, specify the following:
   1. Output Format - Select the report output format. Valid options include:
      • Derive from Item - The output format is derived from an item at runtime.
      • PDF - Adobe Portable Document Format.
      • Word - Microsoft Word Rich Text Format.
      • Excel - Microsoft Excel format.
      • HTML
      • XML - Extensible Markup Language.

      Additional options may be available depending on the print server type. For example, AOP also supports Powerpoint, Markdown, CSV, Open Document Text, Open Document Spreadsheet, and Open Document Presentation. Document Generator supports PDF, DOCX, and XLSX formats.
   2. (Optional) Item - If using Derive from Item, select the item that determines the output format at runtime.
   3. View File As - Select how the web browser opens the report query document:
      • 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.
   4. Include Application and Session Information - Enable this option to reference session state values, such as the values of page items, in the output of your report query.
   5. Click Next.
6. In Report Query Source, select the source:

   • Local Database - Data is sourced from a local database.

   • REST Enabled SQL - Data is sourced from a remote database, where the connection is defined using REST Enabled SQL. To create or maintain REST Enabled SQL references, navigate to Shared Components, REST Enabled SQL Databases.

   • REST Source - Data is sourced from a RESTful web service defined using REST Data Sources. To create or maintain REST Data Sources, navigate to Shared Components, REST Data Sources.

   • Duality View - Data is sourced from a Duality View Source defined using Duality Views within Shared Components.

   • JSON Source - Data is sourced from a JSON Source defined using JSON Sources within Shared Components. JSON Sources include tables with JSON columns or JSON Collection Tables.

   The UI changes based on the data source you select.
7. Set the remaining options. For example, if you selected Local Database in the previous step, set the following:
   1. Source Type - Specify the source type. For example, Table.
   2. Table/View Owner - Select the owner of the table that you are building the search configuration on.
   3. Table/View Name - Select the table or view that the search configuration is based on.
8. Click Next.
9. (Optional) Click Add Query to add additional source queries.
10. Click Next.
11. Confirm displays the following information:
    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 that holds the format information.
    5. URL - Report queries can be integrated with an application by using the URL shown as the target for buttons, navigation list entries, list items, or any other type of link. Click into the URL field to copy the URL.
12. Click Create.
    The Report Query is created and saved to Shared Components.

Edit a report query in Shared Components.

1. Navigate to the Shared Components page:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components.

      The Shared Components page appears.

2. Under Files and Reports, click Report Queries.
3. Select the appropriate report query.
4. On the Edit page, edit the appropriate attributes.

   Caution:

   If you are using Document Generator or APEX Office Print, it is important to specify the Data Loop Name. The data loop name and the report query source must match.
5. Click Apply Changes.

Copy a report query in Shared Components.

1. Navigate to the Shared Components page:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components.

      The Shared Components page appears.

2. Under Files and 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.

View the Report Query Utilization report.

To view the Report Query Utilization report:

1. Navigate to the Report Query page:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components.
   4. Under Files and Reports, select Report Queries.
2. On the Report Queries page, click Utilization.
   The Utilization page displays page processes and dynamic actions that print the select Report Query in the current application.

View the Report Query History report.

To view the Report Query History report:

1. Navigate to the Report Query page:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components.
   4. Under Files and Reports, select Report Queries.
2. On the Report Queries page, click History.
   The History page displays recent modifications made to Report Queries in the current workspace.