Oracle APEX 23 Course For Beginners

Oracle APEX 23 Course For Beginners
Oracle APEX 23 Course For Beginners

Thursday, 13 September 2018

Generating High-Fidelity/PDF Reports in Oracle APEX Using JasperReports


After setting up JasperReportsIntegration and Apache Tomcat  you can design reports in Jasper Studio 6.1 and deploy and integrate them with Oracle APEX. Here’s a list of steps you need to perform to accomplish the task:

  • Create a new data source in Jasper Studio 6.1.
  • Create your new report with Jasper Studio, compile and save it on your computer.
  • Copy the compiled file (.jasper) to appropriate location.
  • Integrate the report with Oracle APEX and call it from your APEX application via an API.  

Create Database Connection
This exercise assumes that you have downloaded and installed Jasper Studio using its .exe file and have also downloaded ojdbc6.jar file that you need in step 4. Launch the software and execute the following steps to create connection to your XE database, which resides on your local machine.

  1. Select File | New | Data Adapter from the main menu. The first screen of the Data Adapter Wizard will appear. On this screen you will see a folder named MyReports. This is the folder where your data adapter's XML file and reports will be stored. On my PC, this folder is created under: D:\JaspersoftWorkspace. The XML file contains jdbc data adapter class, name, driver, username, password and other connection information.

  1. Enter a meaningful name for the XML file (for example, EFC_DATAADAPTER.xml) in the File name box, and click Next.

  1. On the second wizard screen, select Database JDBC Connection and click Next.


  1. On Database JDBC Connection page, enter a suitable name for the connection (A). Select oracle.jdbc.driver.OracleDriver from the JDBC Driver list (B). Then, enter JDBC Url (C). Enter your database username and password in corresponding boxes. Click the Driver Classpath tab (D), and click the Add button (E). In the Open dialog box, select the ojdbc6.jar file (F) and click Open. All these instructions are marked in Figure-2.

  1. On the Database JDBC Connection wizard page, click the Test button. If everything is okay, you will see a dialog box displaying Successful. Click OK followed by Finish to complete the connection process. The new connection will appear under Data Adapters in the Repository Explorer in the left pane.


Create Monthly Review Report
Here are the steps to create a report in Jasper Studio.

Specify Data Source
Execute the following set of steps to specify report’s data source and other preliminary parameters:

  1. Select File | New | Jasper Report from the main studio menu.

  1. On the Report Templates wizard screen, select Blank A4 and click Next.

  1. On Report file screen, enter Monthly_Report.jrxml for the File name, and click Next.

  1. On Data Source screen, select the database connection you created in the previous section from the Data Adapter list. Enter the following query in the Texts window, and click Next.

select o.order_id, to_char(o.order_timestamp,'Month yyyy')
          order_month,
          o.order_timestamp order_date,
          c.cust_last_name || ', ' || c.cust_first_name customer_name,
          c.cust_state, o.user_name sales_rep,
          (select count(*) from demo_order_items oi where
            oi.order_id = o.order_id)
          order_items,
          o.order_total
from demo_orders o, demo_customers c 
where o.customer_id = c.customer_id
order by order_month




  1. On Fields screen, click the button symbolized with >> to select all fields, and click Next.

  1. On Group By screen, move ORDER_MONTH column to the right pane to group the report on this column. Click Next.

  1. Click Finish to complete the process.

  1. In the middle pane, click the DataSet and Query editor dialog icon. Double-click oracle.sql.TIMESTAMPLTZ entry under Class Type and change it to java.sql.Timestamp (A) using the drop-down list. Click OK.

NOTE: While executing the instructions provided in step 8, make sure that the data adapter (B) you created in Create Database Connection section is selected.



Designing Report
The monthly review report will be designed in Jasper Studio according to the following figure:



  1. From the Palette pane (top-right), drag Static Text element and drop it on the Title pane. A Static Text element will be placed in the main report design area. Double-click this element and type ABC CORPORATION (A). In the properties pane (bottom-right), click the Static Text tab. Click the center icon in Text Alignment section to centralize the report title. Use the Font section to set a larger font and make the title bold. Repeat this step to add the address title (B).


  1. Click and drag the line between Page Header and Title bands (C) downwards to expand the Title area. From the Palette pane, drag Chart icon and drop it under the two report titles. On the first Chart wizard screen, select Bar3D Chart and click Next. Click the ellipses button in the Series drop-down list (a). In Chart Dataset Series dialog box, select the default SERIES 1 name expression and click Delete to remove this default series entry. Then, click Add. In the Expression Editor, delete the default SERIES 2 expression, and then in the middle pane, double-click the ORDER_MONTH field to select this field for chart series (b), and click Finish. Click OK to dismiss the Chart Dataset Series dialog box. Using the corresponding ellipsis buttons, select order_total for Value (c), order_month for Label (d), and order_month for Category (e), as shown in Figure-8. Click Finish to complete the process. Adjust the chart to fit in the design area.


  1. In the Design area, right-click the Page Header band (D – Figure 6), and select Delete from the context menu to remove this band. Also remove Column Footer band. We removed these bands because they are not needed in this report.

  1. In the Outline pane to your left, expand the Fields node. Drag ORDER_MONTH field and place it in the ORDER_MONTH Group Header 1 band (E – Figure 6).



  1. Drag ORDER_ID field and drop it in the Detail 1 band (F). As you drop this field in the band, its header (G) is placed automatically in the Column Header band. Double-click the header text in Column Header 1, and provide a meaningful name to the column. Drag and drop other fields, as shown in Figure-6, and change their titles.

  1. From the Fields node, drag the ORDER_TOTAL field and drop it in the ORDER_MONTH Group Footer 1 band (H). The TextField Wizard dialog box pops up listing several built-in functions. Select the Average function and click Finish. This will show average order total for each group.

  1. Drag ORDER_ITEMS field and place it in front of order total (I). Select the SUM function for this field. Use Line tool from the Palette pane to drawn lines for these group totals.

  1. From the Composite Elements pane on the right side, drag Page X of Y element and drop it in the Page Footer band (J) to display page number.

  1. Drag the line (K) under the Summary band downward to expand its area. From the Basic Elements pane, drag Crosstab (pivot table) element and drop it in the Summary band (L). The first screen of Crosstab wizard pops up. Keep the Main Dataset selected in the second option, which uses the existing dataset for creating a crosstab. Click Next. In the Columns screen, select ORDER_MONTH and click the button iconized with a right-arrow (>) symbol to place this field in the right pane. Click Next. On the Rows screen, move CUST_STATE and CUSTOMER_NAME fields to the right pane and click Next. On the Measure screen, move ORDER_TOTAL to the right pane, select Sum in the Calculation column and click Next. On the final Layout screen, accept all the default settings and click Finish. In the Design section, double-click the crosstab. This will open the crosstab in a new tab, where you can modify it. Use lines between columns to adjust column width and click a field and modify its properties in the Properties pane. Once you have done the modifications, click the Main Report tab to move back. Click the Preview tab at the bottom to view the report. After the review, click the Design tab to execute the following steps in which you will add parameters to your report.   

  1. In the Outline pane, expand the Parameters pane. Right-click the Parameters node, and select Create Parameter. A new parameter will be added. In the Properties pane, enter FROM_DATE for the parameter name. Right-click the Parameters node and create another parameter named TO_DATE – see Figure-10.


  1. Click the DataSet and Query editor dialog icon – see Figure-5. Add the following clause after the existing WHERE clause:

and o.order_timestamp between to_date($P{FROM_DATE},'DD-MM-YYYY') and to_date($P{TO_DATE}, 'DD-MM-YYYY')

You can drag and drop the two date parameters (as shown in the following figure) to place them in their proper locations in the query. Click OK after modifying the query.


  1. Click the Preview tab. Enter 01-01-2018 and 31-12-2018 for FROM_DATE and TO_DATE parameters in the Input Parameters dialog box, and click Run the report icon (the green arrow icon at the bottom). In my case, the report displayed records from April, May, and June 2018. If you don't see the report, provide a larger date range.


  1. Click the Save icon or press Ctrl+S to save your report. Switch back to the Design view and click Compile Report. A compiled version of the report named Monthly_Review.jasper will be created in the folder where the actual report file Monthly_Review.jrxml is saved. The report is ready for upload so close Jasper Studio.



Copy the Compiled Report
After creating and compiling your report in Jasper Studio, your next move is to copy the compiled version of your report (Monthly_Review.jasper) to C:\Program Files\Apache Software Foundation\Tomcat 8.5\webapps\JasperReportsIntegration\WEB-INF\reports folder.

Integrating Report with Oracle APEX
In these final set of steps, you will learn how to integrate your report with Oracle APEX.

  1. Create a new page where you will provide date parameters for your report. In the App Builder interface, click Create Page. Select the Blank Page option and click Next.

  1. Set the remaining parameters for the page as follows:
Property
Value
Page Number
23
Name
Monthly Review Report
Page Mode
Normal

  1. In page designer, create a new region. Set the Title of this new region to Monthly Review Report and Type to Static Content.

  1. Add the following two page items to the new region:
Property
Value
Value
Name
P23_FROM_DATE
P23_TO_DATE
Type
Date Picker
Date Picker
Label
From Date:
To Date:

  1. Add a button with the following properties to the region:
Property
Value
Button Name
PRINT
Label
Print
Region
Monthly Review Report
Button Position
Copy
Button Template
Text with Icon
Hot
Yes
Icon
fa-file-pdf-o
Action
Submit Page

Click the Processing tab, right-click the Processing node and select Create Process. Set the following attributes for this new process, which is being added to connect your Jasper Report Sever and pass report parameters.
Property
Value
Name
Print Report
Type
PL/SQL Code
PL/SQL CODE
declare
  l_additional_parameters varchar2(32767);
begin
  xlib_jasperreports.set_report_url('http://localhost:8888/JasperReportsIntegration/report');

-- construct additional parameter list
  l_additional_parameters := 'FROM_DATE=' || apex_util.url_encode(:P23_FROM_DATE);
  l_additional_parameters := l_additional_parameters || '&TO_DATE=' || apex_util.url_encode(:P23_TO_DATE);

-- if needed, you can add more parameters, like:
-- l_additional_parameters :=
                                l_additional_parameters || '&CUST_STATE=' || apex_util.url_encode(:P23_CUST_STATE);

  xlib_jasperreports.show_report (p_rep_name => 'Monthly_Review',
                                  p_rep_format => 'pdf',
                                  p_data_source => 'default',
                                  p_rep_locale => 'en-US',
                                  p_out_filename => 'Monthly_Review.PDF',
                                  p_additional_params => l_additional_parameters);
-- stop rendering of the current APEX page
  apex_application.g_unrecoverable_error := true;
end;
Point
Processing
When Button Pressed
PRINT

Run the report parameters page and select 01-01-2018 and 31-12-2018 for From and To dates and click the Print button. You will encounter an error – “SyntaxError: Unexpected token % in JSON at position 0”. Switch back to the design area and click the root node – Page 23: Monthly Review Report. In the properties pane, scroll down to the Advanced section and set Reload on Submit to Always. Click Save and reload the page. Click Print. This time the report will be downloaded to your PC as a PDF.








at

Display Data Dynamically In A Gauge Chart

In this tutorial, we will learn how to display customer's ordered data in a gauge chart dynamically. As you choose a customer name from ...