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, its 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.
Open the Doxxy tool and navigate to your desired folder. We will use the “My Documents” folder in this article.
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.
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”.
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.
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.
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:
- To close a data set from an SQL statement:
- To retrieve data from an SQL statement:
- To display data conditionally:
[IF:MY_CHECK] content if MY_CHECK = 1
[ELSE:MY_CHECK] content if MY_CHECK = 0 (not mandatory if empty)
MY_CHECK is a column from an SQL statement that returns a 1 or 0
- To display images from the database:
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:
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.
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.
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).
This will be the result:
For information about generating and downloading your report from within your own APEX-application, we refer to one of our previous posts.
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!