Creating a report query

The report in the previous recipe was rather simple. You see a table with headers and the data and that's it! Nothing more, nothing less. But we want a nice, good looking report with headers and footers. Fortunately, APEX offers a way to design reports with a custom layout using Microsoft Word and Oracle BI Publisher. The first step is to define the query the report should be based on. We will do that in this recipe.

Getting ready

Make sure you have access to the APP_CUSTOMERS table.

How to do it...

  1. Go to Shared Components.
  2. In the report section, click the Report queries link.
  3. Click the Create button.
  4. In the Report Query Name field, enter a name for the query. Enter rq_customers. Click Next.
  5. In the SQL query text area, enter the following query:
    Select cust_first_name
, cust_last_name
, cust_street_address1
, cust_postal_code || ' ' || cust_city "city"
, cust_state
From app_customers

    [1346_09_02.sql]

  6. Click Next.
  7. In the next step, we must download the XML definition of this query. We will use that in the next recipe. Select XML data and click the Download button. Select Save file and click OK. Remember the location where you saved the file. We need the file when we want to create a layout in Microsoft Word.
    How to do it...
  8. Go back to the APEX application builder and click Create report query.
  9. At this point, you can test the report by clicking the Test Report button. You will see the same simple layout.
  10. Click the Finish button.
  11. The report query is ready now. We can use the definition in the next recipe.

How it works...

Oracle APEX creates an XML file from the query. In Microsoft Word, using the BI Publisher Desktop add-in, we can use this XML definition to create a custom layout.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.235.8