Report Layouts and Report Queries in Oracle APEX 4.2

Objective:

To create PDF, Excel, RTF, HTML reports using report Layouts and report Queries in Oracle APEX 4.2.

Solution:

Step 1: Go to Shared Components ==> Reports

Fig 1: Reports

Step 2: Create report query using below SQL Query.

<<SQL QUERY >>

<< begins >>

SELECT ROWNUM AS sno,

  isin,

  noofrecords,

  fundid,

  quantity,

  sd

FROM

  (SELECT fd.fundidentificationnumber AS isin,

    COUNT(atd.transactionnumber)      AS noofrecords,

    atd.fundid,

    SUM(atd.unitsconfirmed)      AS quantity,

    TO_CHAR(SYSDATE,'DD-MON-YY') AS sd

  FROM smf_apexrps.unitholderaddinfotbl uai,

    smf_apexrps.consolidatedtxntbl atd,

    smf_apexrps.funddemographicstbl fd,

    smf_apexrps.groupdefinitiontbl gd

  WHERE uai.unitholderid = atd.unitholderid

  AND atd.fundid         = gd.fundid

  AND atd.fundid         = fd.fundid

  AND fd.latestrule      = 1

  AND uai.otherinfo29   IS NOT NULL

  AND uai.otherinfo30   IS NOT NULL

  AND uai.otherinfo30 NOT LIKE 'I%'

  AND atd.datealloted =:p128_date_allotted

  AND gd.groupid      =:p128_groupid

  GROUP BY fd.fundidentificationnumber,

    atd.fundid

  )

<< END >>

Fig 2: Report Query

Fig 3: Setting Bind Variables

Step 3: Create RTF template using BI Publisher. Sample template given below for reference.

Date: SD

To, 

Central Depository Services (India) Ltd.

P J Tower 16th Floor

Dalal Street, Fort

 Mumbai 400 001

We hereby certify that names of the allot tees of the Mutual Fund units of Sundaram Mutual Fund issued consequent to New Fund Offer have been verified with the names provided by CDSL, based on the list of the DP Id and Client Ids of the allot tees provided by us. We hereby certify that the credits are being affected only to those accounts which are in “active” status and where the names and order of names as per the application matched with that of the names and order of the names as provided by CDSL. The details of the Corporate Action are given under:

S NO	ISIN	NO OF RECORDS	QUANTITY
F SNO	ISIN	NOOFRECORDS	E

Yours Faithfully,
For XXXYYYZZZ Fund Services Ltd



Authorized Signatory

Step 4: Upload RTF Template, which we created.

Fig 4: Load Template

Step 5: Tag report layout with corresponding report query.

Edit report query attributes then set report layout as “COB” (which we created)

Fig 5: Report Layout Tagging

Step 6: Create report generation screen.

Fig 6: Report Screen

Step 7: Call the report.

Create Button. By two way we can download the report, 

1)  Button Action = "Download Printable Report Query"

Fig 7: Report Calling

2) Button Action = "Redirect URL"

f?p=&app_id.:0:&session.:print_report=unitholder_details 

(OR)

http://yyyy.xxx.home:8080/ords/f?p=&app_id.:0:&session.:print_report=unitholder_details

Fig 8: Report Calling

Step 8: Download the report.

Fig 9: Download Report

Conclusion: Using report queries and report layout, we can create WORD, PDF, EXCEL, HTML reports by creating custom templates in Oracle APEX 4.2.

Related Posts:

• Report Generation from BI through APEX 4.2 (Theory)