Development Workflow

The report development is an iterative process consisting of the steps described below. It’s recommended to start with a simple version of your report and add to it more regions and features one by one, generating the test report after each modification.

Think about report structure, general layout, and data sources. Consider the issues below:
- What fields and blocks does the report include?
- How is information grouped?
- Are you going to use repeating elements like tables or lists?
- Are you going to use images or photos? If so, will there be one image per report or several?
- What fields are optional or mandatory?
- Do you need to print the report in different languages?
- Where will the data be taken from? What Oracle tables are you going to use?
- Create and test views if necessary.
Create a new report definition.
1. Open the Report Designer page and click the New toolbar button. If you are a new developer, familiarize yourself with Report Designer interface.
2. Enter data in fields Report Code (unique report identifier), Report Description (optional description), and Filename for downloading (files generated and downloaded by end-users will have this name)
3. Click the Save toolbar button.
4. Each report definition must have at least one CustomXML report component. On CustomXML tab supply data about:
  1. Component name may be any unique identifier e.g. CustomXml
  2. Generation Way defines how CustomXML file is prepared, e.g. by Nodes and Attributes data
  3. Path to CustomXML file in zipped OOXML is place in the archive where MS Word stores the datafile. Enter default value customXml/item1.xml (if you have only 1 CustomXML component) or other path in the zipped archive when you know where MS Word writes the file (and you have 2 or more CustomXML components)
  4. In case Generation Way is by Dynamic SQL, fill field Dynamic SQL statement. See field context help for code structure and example. If you specified Generation Way as By Nodes and Attributes data then leave this field empty.
  5. Click the Save toolbar button.
  After completing these steps, you may have something like the example below:
Create CustomXML Tree. Map database records and fields to CustomXML nodes and attributes. This is one of the most important steps in report development. See CustomXML Tree recommendations for principles, constraints, and requirements.
1. Click Add Node button in the Node Details section.
2. Enter Node Name which is just a unique node name.
3. Select Parent Node from nodes saved earlier or leave this field empty for root (or first) node.
4. Enter the Data Source field where the data for the node is read from. It may be an Oracle table, view, or sql statement. In the latter case make sure that the sql statement is valid by running it in tools like sqlplus.
5. Enter optional Condition field used to narrow selection from datasource by values of entered report parameters or values from parent nodes.
6. Click the Save toolbar button.
7. Add Node Attributes for the created node. They will be included as xml tag attributes in result CustomXML file in format attribute_name="value":
  1. In section Node Attributes add new a row if necessary (by clicking on the sandwich sign).
  2. In the field Datasource Column enter either a column name that presents in the node datasource or a valid function from it (e.g. SUBSTR, UPPER, SYSDATE)
  3. Attribute name is mandatory when sql function is specified in Datasource Column. If it is skipped and Datasource Column contains just a name (or alias) of the column specified in the node Data Source, then Datasource Column name will be used as xml attribute name in result CustomXML file.
  4. In optional Format Mask field you may enter conversion masks for date or numeric fields, e.g. DD/MM/YYYY. You can use any mask allowed by Oracle.
  5. Click the Save toolbar button.
8. Repeat steps above for all nodes and their attributes.
9. Click Prepare Empty Custom XML toolbar button and make sure that the text of generated xml is as expected. Validate it if necessary.
After completing all these steps, you may have CustomXML Tree like this example:
Map CustomXML nodes and attributes to Microsoft Word Content Controls and add the created report template to the report definition.
1. Click the toolbar button Prepare Empty Custom XML, download, and save the file.
2. Open MS Word and create a boilerplate. Add texts, tables, images, and logos that are constant or independent of database data (or report parameters).
3. On MS Word Developer tab activate XML Mapping Pane:
  If the Developer tab isn't displayed by default, but you can add it to the ribbon. On the File tab, go to Options > Customize Ribbon. Under Customize the Ribbon and under Main Tabs, select the Developer check box.
4. Click the drop-down list Custom XML Part, choose (Add new part…)
  and open the Empty Custom XML file generated before. On the drop-down list Custom XML Part, choose part named “(no name space)”. Turn on Design Mode on the Developer tab to see visual effects of your further actions:
5. To map a single database record to Content Controls, place the cursor to the desired position in the document. Then on XML Mapping Pane select the corresponding XML attribute, right-click on it, and select from the popup menu Insert Content Control > Plain Text. See an example in the picture below:
  When you’re done, a special indicator appears at the position of Content Control, whose properties you can now edit. Repeat these actions for the rest of the single record fields.
6. To map several database records to MS Word table, first create the table with one empty row and an optional header. Then select the whole empty row (by clicking on the left of it), on XML Mapping pane select container (parent) node which has nested (child) nodes corresponding to separate database records, right-click on container (parent) node and select from popup menu Insert Content Control > Repeating:
  Then again select the whole empty Word table row, on XML Mapping pane select child node containing attributes corresponding to database record fields, right-click on it, and from the popup menu select Map to Selected Content Control.
  Notice, that nothing visually changes, though Word makes some internal binding. Now map attributes of child node to columns of the word table the same way as for mapping single database record (see the point above). As a result, you have something like:
7. Save Word document as .docx file. Add a new report template component to your report definition (on the Report Templates tab in Report Designer). Upload .docx file into report template component:
  If you have only 1 report template, leave the field Prerequisite empty.
Now it’s a good time to test your report. Click Generate & Download toolbar button, enter report parameters (e.g. parameter 1 = 90), then click Download report. The result may look like:
The first iteration of the development of your report is complete. Now you may add more report templates to enable localized versions of your report, add images into the report or make other improvements.

To download generated report file from your Apex application you:

Should have page 5556 (or a similar one) in your application

add Javascript call as a handler (of type Execute JavaScript Code) for button click event:

apex.navigation.redirect( 'f?p=&APP_ID.:5556:&APP_SESSION.::::P5556_CATEGORY,P5556_REPORT_CODE,P5556_PARAMETER_1,P5556_PARAMETER_2,P5556_PARAMETER_3,P5556_PARAMETER_4,P5556_PARAMETER_5:REPORT,' + $v('PXXX_REPORT_CODE') + ',' + $v('PXXX_PARAMETER_1') + ',' + $v('PXXX_PARAMETER_2') + ',' + $v('PXXX_PARAMETER_3') + ',' + $v('PXXX_PARAMETER_4') + ',' + $v('PXXX_PARAMETER_5') );

where PXXX_YYY – items on your page containing Report Code and parameters values. If you report has fewer than 5 parameters, you don’t have to list unnecessary ones. The items on target 5556 page have the following meanings:

Item Name	Description of possible value
P5556_CATEGORY	Constant REPORT which defines that you are going to download report file (.docx)
P5556_REPORT_CODE	Report Code that must be generated and download
P5556_PARAMETER_1 … P5556_PARAMETER_5	Values of report parameters

To get the generated report inside your PL/SQL code you should call function Report_Designer_Pkg.Generate_Report which the returns result of BLOB type and take up to 6 IN parameters:

Parameter Name	IN \| OUT	Data type	Description
p_report_code	IN	VARCHAR2	Report Code to generate
p_param_1	IN	VARCHAR2	Optional parameter 1 of the report
p_param_2	IN	VARCHAR2	Optional parameter 2 of the report
p_param_3	IN	VARCHAR2	Optional parameter 3 of the report
p_param_4	IN	VARCHAR2	Optional parameter 4 of the report
p_param_5	IN	VARCHAR2	Optional parameter 5 of the report

Write a comment or question:

Contents of Documentation

Report development workflow

Comments

Breadcrumb

Development workflow

Contents of Documentation

Report development workflow

Comments