Enhancing Document Generation with OCI and Oracle APEX 24.2

Oracle Cloud Infrastructure (OCI) Document Generator function continues to push the boundaries of report generation. With the recent updates, it now supports MS Excel templates, unlocking a broader range of possibilities for seamless report generation. Since the Document Generator Function has excellent enhancements, we have made enhancements to Oracle APEX 24.2 to accommodate this. With APEX 24.2, we can create Templates using both MS Word and MS Excel and generate PDF, Excel, and Word documents.

Key Updates to OCI Document Generator Function

1. MS Excel Template Support:
   • The Document Generator now accommodates MS Excel templates, enabling users to effortlessly create complex, multi-sheet reports.
2. Enhanced MS Word Template Performance:
   • Faster Processing: Large vertical loops now process faster, ensuring smooth performance for extensive documents.
   • Automatic Tag Replacement: Unmatched tags are automatically replaced with empty strings, streamlining document cleanup.

Oracle APEX 24.2: Enhancements to the Document Generator Function Integration:

Oracle APEX 24.2 has been enhanced to fully integrate the updates to the Document Generator Function, making it easier than ever to design and generate professional documents. Here’s what’s new:  

Template Creation

1. MS Word and MS Excel Support: You can now create templates using both Word and Excel, which gives you flexibility in structuring your reports.
2. Multiple Output Formats: Generate documents in PDF, Excel, or Word formats, ensuring compatibility with your workflow needs.

Report Layouts and Queries

1. Configuring Report Layouts:

   To print reports in Excel or PDF format, you can use Excel templates with APEX. For example, the Document Generator Service can process an Excel template like employees.xlsx to generate the desired output. Before using this template in Classic Reports, Interactive Reports, or through a button click, it must first be uploaded to the Report Layout.

   

   

   Example:
   An Excel template can have multiple sheets, such as one for Employees and another for Departments. Each sheet loops through its respective data using a defined Data Loop Name.

   Follow the detailed steps outlined in this Document to create a new Report Layout. With these steps, your Excel template with multiple sheets will integrate seamlessly with Oracle APEX.

   

2. Creating Report Queries:
   In Oracle APEX, a report query is a Data Source SQL query used to fetch and display data in a report. The Data Source can be a table, a SQL query, a REST Data Source, or a Property Graph. The report query specifies the columns, data source, and any conditions or sorting. Report query can also include bind variables to retrieve values from the session, allowing for dynamic and context-sensitive data retrieval.
   
   With APEX 24.2, using the document generator Function, we can now set the report's Output Format as PDF, Word, or Excel based on the Template.
   Steps for Creating a Report Query:
   • Define Data Source: Choose from options like a local database table, REST Data Source, or other supported formats.
   • Set Output Format: Match your format (PDF, Word, Excel) with the associated template.

     

   • Match Loop Names: After creating a Report Query, to ensure smooth integration, the Data Loop Name in the Source Queries of the Report query must match the loop name specified in the Excel template (e.g., employees for the Employees sheet and departments for the Departments sheet). Optionally, while creating the report query, you can also make sure that the Report Query source name matches with the loop name specified in the Excel Template. By aligning loop names and query sources with template definitions, you ensure seamless integration and data mapping, whether your output is Word, Excel, or PDF.

     

      

     

   • To learn more, follow the detailed steps outlined in this Document to create a new Report Query.
3. Test Report Query:
   To preview the report we just created, open the Report query and click Test Report. If you choose to use a generic report layout, the Test Report will not produce an output.

   

   
   Sample Output:
      

   

   

   

Integration Options:
Report Layouts and Queries can now be seamlessly integrated into:

• Classic and Interactive Reports
• Declarative Page Processes
• Dynamic Actions
• PL/SQL APIs

These enhancements simplify how you can call, generate, and use reports in Oracle APEX.  

Check out this blog post to learn more about creating a Document Generator Function and Configuring it as a Print Server in Oracle APEX.

Summary

The OCI Document Generator and Oracle APEX 24.2 updates make report creation easier and more versatile. With support for MS Excel templates alongside existing MS Word capabilities, users can now create detailed, multi-format reports in PDF, Excel, and Word with seamless integration into APEX applications.