Create your first report with Doxxy: quick and easy!

In a previous post we already told that Doxxy is a simple and easy reporting tool to generate your DOCX and PDF reports from the database. Well, it’s time to create your first report now! Follow the steps together with us and be convinced how quick and simple you can develop and generate documents with Doxxy!

This article makes use of the Oracle DEMO tables about orders which are also used in the Sample Application in APEX. Install this application in your Doxxy workspace and you can follow this guide.

New document

Open the Doxxy tool and navigate to your desired folder. We will use the “My Documents” folder in this article.

image_01

Create your document in which you want to manage all your metadata, such as SELECT statements, PL/SQL statements, templates and parameters. To do this, click on the “Create Document” button on the top right-hand side or click on the “+” icon that appears as soon as you hover over the folder in which you would like to create a document.
Choose an appropriate name for your document and enter a brief description.

image_02

After the document has been created, it will appear under the folder where it was created. By clicking on the document name in the folder tree, we can shape our document by adding “SQL Statements”, loading templates, and, if required, adding a “PL/SQL procedure” or defining input “Parameters”.

image_03

SQL Statements

First, we need data for our report. In this example, we will show you an overview of all the orders that have been placed from the DEMO_ORDERS table.

Click on the “Create SQL” button in the “SQL” tab (or click on the “+” icon that appears as soon as you hover over “SQL Statements” in the folder tree). Complete the fields.

image_04

As SQL statement you enter your query to retrieve the data that you require from the database.

Below the SQL statement you can clarify how the data should be used in your report:

  • Table: in a table
  • List: a numbered or unnumbered bulleted list
  • Paragraph: in een paragraph, as standard standalone text (in a previous version of Doxxy this used to be called: Global document)
  • Page Break: in a paragraph, where a page break is added after each record of the SELECT statement
  • Page Reset: in a paragraph, where a page break is added after each record of the SELECT statement and any page numbers are reset to 1

Enter a clear, unique code as Recordset Code. You will need to use this code in your template as reference to your statement.

If you require more data, you can select it by creating extra SQL statements and link these statements. In this way it is very easy to make master detail overviews.

Templates

Now you need to determine how your report should look like. This will be achieved by simply ‘designing’ your report in a MS Word DOCX-document (=template). This Word template allows you to use all the functionalities that Microsoft Word has to offer. You can merge data within your report by using tags:

  • To start a data set from an SQL statement:
    [BEGIN:MY_RECORDSET_CODE]
  • To close a data set from an SQL statement:
    [END:MY_RECORDSET_CODE]
  • To retrieve data from an SQL statement:
    [MY_COLUMN_NAME]
  • To display data conditionally:
    [IF:MY_CHECK] … content if MY_CHECK = 1 …
    [ELSE:MY_CHECK] … content if MY_CHECK = 0 … (not mandatory if empty)
    [ENDIF:MY_CHECK]
    MY_CHECK is a column from an SQL statement that returns a 1 or 0
  • To display images from the database:
    [IMG:MY_BLOB_COLUMN]
    MY_BLOB_COLUMN is a column from an SQL statement that returns a BLOB containing an image

This is an example of a possible template in MS Word:

image_05

Save your template with a descriptive name, e.g. orders_template.docx.
Return to your document in the Doxxy web application and upload your template via the “Templates” tab (or click on the “+” icon that appears as soon as you hover over “Templates” in the folder tree). Complete the fields.

image_06

You probably noticed two more tabs in your document, namely: the tab “Parameters” for defining one or more parameters and the tab “PL/SQL” for adding PL/SQL logic before or after the execution of a report. These additional possibilities are addressed in the manual, including a fully elaborated example.

Preview

You can review your report at any time by requesting a preview. Therefore, in Doxxy, go to the appropriate document and click on the “Preview” button in the upper-right corner (or use the “eye” icon that appears when you hover over the document).

image_07

This will be the result:

image_08

For information about generating and downloading your report from within your own APEX-application, we refer to one of our previous posts.

Conclusion

Easy, not? We hope that this quick demo convinced you that Doxxy is indeed a fairly intuitive tool for creating your documents from the Oracle database! Check it out by yourself and get a free trial now!

The Basic Principles of Doxxy: keep it simple and easy

First of all, Doxxy is a tool for developers: for APEX developers … surely, but in fact for anyone who is developing against an Oracle database and who needs a printable output. This printable document may be a .DOCX file or a PDF file.

This post explains the basic fundaments of Doxxy, keeping in mind that the tools has to be simple and easy.

How does it work?

Doxxy consists of two major components:

  1. a front-end application (UI), used for defining the metadata that describes the documents. The actual UI is written in APEX and is available in all 4.x versions. We are also working on a Eclipse-RCP version of the UI.
  2. a PL/SQL engine, which contains all the intelligence and generates the desired output


1. The APEX UI

The main objective of the APEX UI is to give the developer an easy interface to configure his document. There are different screens where you can enter the metadata of your ‘printable’ report. This metadata consists of:
- one or more SQL statements
- minimal 1 Word-template (DOCX)
- and optionally input parameters and/or a PL/SQL block with pre -or after processing logic

Following screenshot gives you a general idea of how the UI looks like:

2. The PL/SQL Engine

The PL/SQL Engine retrieves the metadata which is defined via the UI as input for a given document. It executes the SQL statement(s), binds them with the parameter(s) and gets the specified DOCX-template.
Thereafter, this template is unzipped. As you may know, a DOCX-document is an amalgam of different XML-files. The engine replace all data tags in the document.xml file by the corresponding business data.
At the end all XML-files are packed again in a DOCX-output-document, which is returned as a BLOB variable to the calling program.

Following picture gives a schematic overview of the concept:

ppt_web_architecture

So, summarizing:

Doxxy is a RAD-tool for generating operational reports. With its intuitive APEX UI, you easily configure your documents by adding DOCX-templates and SQL-queries. The engine is written in PL/SQL, which makes installation, integration and maintenance as simple as a walk in the park. The tool comes as a packaged application for APEX 4.x.

Follow @doxxy on twitter
www.doxxy.eu

Why we started building Doxxy …

We, at iAdvise, are early adopters and believers of Oracle Application Express (APEX), especially for the RAD way of working and the no-extra cost factor of the tool: APEX makes it possible to build on time and within budget your database application for the web.

But an application is more then just building pages for CRUD operations. Very often you also need to generate well looking documents as output. And this is standard not possible with APEX. A pity!  Within the Oracle world, BI Publisher is by far the best solution. But from the pricing point of view, it does not really fit in the ‘cheap’ philosophy of APEX. In the meanwhile, Oracle announced a new PDF Printing possibilities in APEX 5.0, using APEX Listener 2.0’s FOP support, but this solution did not yet convince us …

So, two years ago, we decided to build our own tool in the same RAD philosophy as Oracle Application Express: quick & easy to learn, easy to use, cheap, solid, easy to integrate in APEX (or more general, in an Oracle context), DOCX and PDF output, …

The idea and main objectives for our own “document generator for Oracle” was trying to bring some of the better features of other tools together in one solution:

  1. Separation of data retrieval and document layout.
    The layout is template driven: your template is a MS Word document (.docx). This leaves the possibility open to involve business users in the design and build of the desired output.
  2. Simple, easy and light.
    We want the solution to be as much as possible in the database, in PL/SQL, with no extra software on a middle-tier
  3. Metadata driven and nice user interface to manage those data.
    Since we work with Oracle as a database server and APEX as application builder … this was obvious!
  4. Integration in your application via a PL/SQL API.
    This application may be developed in the technology of your choice. We use it initially in APEX projects, but it can be integrated in any tool that can do a PL/SQL call (Oracle Forms, any java solution, Formspider, …)

So, with Doxxy, we proceed on the APEX philosophy, there where APEX it self stops: easy and quick generation of operational documents.

Not convinced? Just give it a try, and you will notice that this words are not idle.

How to download a Doxxy report from within APEX?

Doxxy provides a PL/SQL API which encapsulates all processing of the report generation. You pass the necessary parameters, and the API returns a BLOB which contains the desired output (DOCX or PDF). With this BLOB you can do whatever you want: you can store it in the database or on a file system, you can print it, you can mail it, download it, …

This blogpost explains how you can implement the call to the API and the download process in Oracle Application Express.

1. Code to download your report

First you need to compose the code to generate and download your Doxxy BLOB file from your web application:

DECLARE
   l_bind_variables BL$DG4O_INTERFACE.bind_variables_t;
   l_doc BLOB;
BEGIN
----------------------------------------------------------
-- Generate report
----------------------------------------------------------
   l_bind_variables('P_ORDER_ID').number_value := 1;
   l_doc := BL$DG4O_INTERFACE.generate_document(p_document => '/My Folder/Orders Document'
                                               ,p_document_template_name => 'Orders Template'
                                               ,p_bind_variables => l_bind_variables
                                               ,p_application_key => ''
                                               ,p_user_id => ''
                                               ,p_format => 'DOCX');
----------------------------------------------------------
-- Download report
----------------------------------------------------------
   -- Initialize
   htp.flush;
   htp.init;
   -- Set up HTTP header
   owa_util.mime_header('application/octet-stream',false);
   -- Set the size so the browser knows how much to download
   htp.p('Content-length:'||dbms_lob.getlength(l_doc));
   -- The filename will be used by the browser if the users does a 'Save as'
   htp.p('Content-Disposition:attachment;filename="Orders.docx"');
   -- Close headers
   owa_util.http_header_close;
   -- Download the blob
   wpg_docload.download_file(l_doc);
   /*apex_application.stop_apex_engine;*/
END;

You see two main parts in the PL/SQL code: 1) the generation of the report with the call to the Doxxy API and 2) the specifc code for downloading the BLOB.

Tips

Generate report
More information about the BL$DG4O_INTERFACE.generate_document API can be found in the Doxxy user manual.
Use the “Show API Call” button in the “Generate Preview” screen in the Doxxy UI to generate this PL/SQL code. You can copy and paste this code into your APEX application.

generate_preview

Download report
Use apex_application.stop_apex_engine (from version APEX 4.1) when downloading your report within a Before Header process. The engine will just download your BLOB and will then stop the page immediately from rendering, so your application will not go to this download page and the user will never see it.
We will not use it in the following example, since we will download the report within an On Submit process.

2. APEX download button + branch

There are multiple ways to add download functionality on your web application. In this section we will explain how you can create a download button with a branch containing the PL/SQL procedure to download your BLOB file.

Create button
First create a button (for example “Download”) in the region of your choice (Page Rendering part).

create_button

Fill in the fields. For “Action” choose “Submit Page”.

submit_page_action

Create a branch
Next create a branch (Page Processing part).

create_branche

Fill in the next fields. For “Branch Type” choose “Branch to PL/SQL Procedure”.

branch_to_plsql_procedure

Enter your PL/SQL procedure.
You can also create a stored procedure in the database and enter a call to this procedure here.

plsql_procedure_to_call

Continue the wizard. Select your button in the “When Button Pressed” select list.

You are ready now!

page_overview

Run your page and press the Download-button to test your download process. Your Orders.docx report will be downloaded.

sample_application

Doxxy 1.2: Document generation for Oracle made easy.

Last week, we released Doxxy 1.2, our reporting solutions for Oracle.

Doxxy is a RAD-tool for generating operational reports. With its intuitive APEX UI, you easily configure your documents by adding DOCX-templates and SQL-queries. The engine is written in PL/SQL, which makes installation, integration and maintenance very straight forward. The tool comes as a packaged application for APEX 4.x.


What is new?

First of all, Doxxy is a tool for developers: for APEX developers … surely, but in fact for anyone who is developing against an Oracle database and who needs a printable output. Until now, the reporting engine generated a .DOCX file as printable document. In version 1.2 there is an extra option available which makes it possible to have a PDF-document as output.

Other new features we added to the product are:

  1. Possibility to add some PL/SQL logic at the beginning or at the end of the generation process.
    Possible use-cases can be:
    a) set an Oracle context with a language indicator at the beginning of a report, or preparing your data in temporary tables to make the querying more easy.
    b) At the end you may use it for updating a print-status or – flag on given records.
  2. Performance optimalization for documents with a lot of content or with a lot of IF-statements
  3. The export –and import mechanism is XML based. It is now also possible to export/import multiple documents from a given folder in one run.
  4. Easy search-box to quickly find a document in the object tree
  5. Template visualization and validation: when you do an upload of a template, the system does some basic validations on the ‘formal’ content of the template, especially on the names of the tags.
    From within the Doxxy-UI you may also visualize the formal structure of your templates. Errors are visually emphasised in red.
  6. Simplified mechanism to include images (coming from a BLOB-column) into the report-output.
  7. Extra page to maintain your doxxy-specific private synonyms.

Give Doxxy a try and request a free trial,

Follow @doxxyNews on twitter.
Website: www.doxxy.eu.