Reports Quieries and Reports Layouts in Oracle APEX

Report Queries N  Report Layouts in Oracle APEX

Report Queries :- A report query is a printable document, which can be integrated with an application using buttons, list items, branches or any other navigational components that allow for using URLs as targets.

A report query is based on a standard SQL query. It can be downloaded as a PDF document, a Word document (RTF based), an Excel Spreadsheet (HTML based) or as an HTML file. The layout of a report query is customizable using RTF templates.

Report Layout :-   Use Report Layouts in conjunction with a report region or report query to render data in a printer-friendly format, such as PDF, Word or Excel. A report layout can be designed using the Template Builder Word plug-in and uploaded as a file of type RTF or XSL-FO. Report regions use a generic XSL-FO layout, which is customizable.

Report Query concept is used not only for the External 3^rd party tool printing but it can be even used to execute the report and send the report as the attachment to the email  when called from the  process or the scheduler job.

Creating Report Query :-

Steps :-

• Oracle Apex Development environment login with developer access .
• Application Builder.
• Database application.
• Choose any existing application or create new one.
• Click on application for edit mode of it  à shared components.
• Go to Reports Region of the Shared components

• Click on the Reports Layout Link

• Click on the Create Button.

• Choose the option of the header naming for the columns . Currently you can go with generic column .

• Click on the Next icon  and name the layout as emplayout  or anything. This layout is nothing but the actual look and feel of the report which will be displayed during the report printing . modifiying the heading or the color attributes in case you want to .
• Click on the create icon.

• The Report Layout is created and you can see the following report layout listed in your shared components.

Creating the Report Query:-

• This is the place where you will be writing your actual query.
• Shared components  à click on the Report Queries icon -> create Icon.

• Specify the report query name , output format and view file as attachment in case you want to send this as attachment in your email notification and click next

• Specify the Query along with the bind variable in case you need to specify, and then click on the set bind variable button .

• Now you can test the report by passing the values of your bind variable and see the output.

Once you enter value click on test and see the output and the click on Apply Changes Button.

You will be taken back to the same screen as shown above.

• Click on the next icon and specify the source layout as name of the layout which you have created or use any file that contains the layout and upload it else default it to use the generic layout,.
• Click on next icon.

• Using this url you can call your report created in the shared components from anywhere , I mean button , navigation , redirect , list .

• Click on the test report icon, to check whether the report is being printed or not.

In a case its giving the error as above then, you need to do the following.

"ORA-20001: The printing engine could not be reached because either the URL specified is incorrect or a proxy URL needs to be specified."

This is because of the grants. See the details mentioned below:-

“ORA-20001: The printing engine could not be reached  top

By default, the ability to interact with network services is disabled in Oracle Database 11g release 1 (11.1). Therefore, if running Oracle Application Express with Oracle Database 11g release 1 (11.1), use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the FLOWS_030100 database user. Failing to grant these privileges results in issues with PDF/report printing, specifically, you will get the following error message:

ORA-20001: The printing engine could not be reached because either

The URL specified is incorrect or a proxy URL needs to be specified.

Follow the steps mentioned in the Oracle Application Express Installation Guide, Enabling Network Services in Oracle Database 11g."

Once done your report is ready to be used with any third party tool or email package .