You are previewing Oracle APEX 4.2 Reporting.
O'Reilly logo
Oracle APEX 4.2 Reporting

Book Description

Learn how to build complex reporting solutions using Oracle APEX

  • Provides an introduction to the APEX architecture and is a step-by-step guide to setting up the APEX environment on Weblogic

  • Integrations of the reports with the most popular reporting technologies and generation of exotic and typical reports alike

  • Packed with complex APEX applications to help you learn newer ways of fulfilling reporting requirements in APEX

In Detail

The biggest challenge in the reporting world is to reduce the overall project cost by picking a tool that minimizes effort and time and to ensure an enriching user experience. Oracle APEX, a 4GL technology, with its unique features such as low implementation time and flexibility, aids us in creating performance-tuned applications with minimum hassle.

"Oracle APEX 4.2 Reporting" is a unique blend of fascinating solutions and intriguing integrations which gives you a 360 degree view of the reporting solutions available to you on the market. It showcases advanced APEX solutions that will empower you to fulfill all kinds of tricky requirements of the reporting world. Live applications support the discussions in the text, enabling you to perform your own experiments on prebuilt applications to take your learning to new heights.

"Oracle APEX 4.2 Reporting" will help you build a well-founded understanding of Oracle APEX along with most of the reporting technologies used today. It is written with the idea of being as practical as possible so that you reap the benefits from day one.

Starting with a brief introduction to the architecture of APEX and installation to enable you to see the applications provided with the book in action, we then move on to the uses of Classic and Interactive reports and explore the advanced features of APEX. We will also explore the most widely used reporting solutions, which is then followed by a brief discussion on BPEL, which is like the glue that can gel any number of tools together. Oracle 4.2 APEX Reporting concludes with several ways of tuning an APEX application, as good performance is the heart of customer satisfaction.

Table of Contents

  1. Oracle APEX 4.2 Reporting
    1. Table of Contents
    2. Oracle APEX 4.2 Reporting
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
        3. Instant Updates on New Packt Books
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    8. 1. Know Your Horse Before You Ride It
      1. What is really new in the new avatar of APEX?
      2. APEX configurations
        1. APEX configuration using DAD
          1. Internal mod_plsql configuration
          2. External mod_plsql configuration
        2. APEX Listener configuration
      3. Installing APEX engine and Listener
        1. Installing the APEX engine in the database
        2. Creating a WebLogic domain and starting the servers
        3. Setting the APEX Listener
          1. Configuring and deploying APEX Listener on the WebLogic domain
      4. Creating APEX workspace
      5. Understanding the APEX URL
        1. The protocol
        2. The hostname and port number
        3. The DAD name and web context
        4. The f procedure
        5. The application ID in the URL
        6. The page number in the URL
        7. Session management
          1. The zero session ID
        8. Request handling
        9. Debugging
          1. Error handling
          2. TKPROF
        10. Cache management
        11. Passing values and navigation
        12. Making APEX printer friendly
      6. Decoding the APEX page submissions
      7. APEX behind the scenes
      8. Other web interfaces
      9. A pessimist's view of APEX
        1. Cap of 200 page items
        2. SQL Injection
        3. Cross-site scripting
      10. Summary
    9. 2. Conventional Reporting in APEX
      1. Creating database objects and installing the reference application
      2. Implementing external table authentication
      3. Displaying environment properties using the USERENV namespace
      4. Displaying the CGI environment variables
      5. Implementing a classic report search functionality
      6. Enabling sorting and CSV download
      7. Implementing group reports
        1. Using the Break Formatting section in classic reports
        2. Using JavaScript to modify a report
        3. Using grouping sets and HTML formatting in a query
      8. Additional methods of formatting
        1. Highlighting the searched text in a report
        2. Editing a template for additional formatting
      9. Implementing a matrix report
        1. Understanding the with clause
        2. Understanding the Oracle 11g's pivot operator
        3. Understanding string aggregation
      10. Data highlighting using Dynamic Actions and jQuery
      11. Advanced formatting using APEX templates
      12. Understanding dynamic query region in APEX
      13. Implementing hierarchical reports
        1. Hierarchical reports with drilldown
        2. Creating dynamic messages using substitution variables
        3. Creating dynamic breadcrumbs for drill ups
        4. Creating a report with hierarchical query
        5. Creating a hierarchical report using the recursive with clause
        6. Creating a tree based on hierarchical data
      14. Understanding methods to upload files in APEX
        1. Using DA to set page items and side effects of PPR
          1. A workaround to the side effects of PPR
        2. Using substitution variables for labels
        3. Auto feeding one APEX item based on another
        4. The method to upload files using WWV_FLOW_FILES
      15. Understanding download methods
        1. Download using APEX format mask
        2. Download using developer-defined stored function
        3. Download using APEX_UTIL.GET_BLOB_FILE_SRC
        4. Download using p process
      16. Implementing soft deletion with AJAX and APEX templates
      17. DML operations and report filtering using DA, JavaScript, and page processes
        1. Filtering reports and logging values using Dynamic Actions
        2. AJAX – a two-way communication between page process and JavaScript
      18. Implementing a tabular form
        1. Creating a tabular form
        2. Changing item type on user event
      19. Implementing a master detail report and displaying complex types
        1. Displaying complex types and varray in a report
      20. Implementing time series reports
        1. Creating aggregation on a partitioned time dimension region
        2. Time series analysis with analytical functions and time dimension
          1. Using the query partition clause for data densification
        3. Creating aggregations using the model clause
      21. Implementing data-level security
        1. Using VPD
        2. Using query filters
      22. Summary
    10. 3. In the APEX Mansion – Interactive Reports
      1. About the reference application
      2. Important features of the interactive report
        1. The Search functionality
        2. Using the Select Columns feature of the Actions menu
        3. Using the filtering feature of the Actions menu
          1. Linking the interactive reports
        4. Using the Rows per Page feature of the Actions menu
          1. Customizing the number of rows in an IR
        5. Using the formatting options of the Actions menu
          1. Using Control Break
          2. Using the Compute option
          3. Using Chart in an IR
        6. Using the Flashback feature of the Actions menu
        7. Using the Save Report feature of the Actions menu
        8. Using the Reset and Download features of the Actions menu
          1. Configuring the Email functionality
          2. Downloading in CSV and HTML formats
          3. Downloading in PDF format
        9. Using the Subscription feature of the Actions menu
      3. Other configurable attributes of an IR
        1. Using the Link Column section of the Report Attributes page
        2. Using the Icon View section of the Report Attributes page
        3. Using the Detail View section of the Report Attributes page
        4. Using the Advanced section of the Report Attributes page
        5. Using the Column Group section of the Report Attributes page
      4. Using Dynamic Actions (DA) to add custom functions in the Actions menu
      5. Using CSS in IR
        1. Formatting a column using another column
        2. Using CSS in the page header to format APEX data
          1. Changing the font color of alternate rows in APEX
        3. Using a user-defined CSS class in APEX
        4. Conditionally highlighting a column in IR using CSS and jQuery
        5. Formatting an IR using a region query
      6. Understanding the process of any customizations in IR
      7. Using APEX views to create a classic report on saved IRs
        1. Capturing report ID using JavaScript
      8. Creating multiple IR on the same page
      9. Authorizing user groups to view report columns
      10. Creating Interactive Report Dashboard
      11. Understanding Dynamic Interactive Reports
        1. Using native PL/SQL table function approach and conditional columns
          1. Using parallel-enabled table functions
        2. Unterstanding interface table function approach
          1. Understanding the ODCITableDescribe function
          2. Understanding the ODCITablePrepare function
          3. Understanding the ODCITableStart function
          4. Understanding the ODCITableFetch function
          5. Understanding the ODCITableClose function
        3. Understanding the collection approach
      12. Summary
    11. 4. The Fairy Tale Begins – Advanced Reporting
      1. About the reference application for this chapter
      2. Using LDAP authentication
        1. Process to get the necessary DN from JXplorer
      3. Creating sparkline reports
      4. Creating a report with slider
      5. Creating HTML charts
        1. Creating a bar chart using APEX's HTML chart functionality
          1. Creating an APEX HTML chart
          2. Displaying the top N and the bottom N rows in an HTML chart
          3. Understanding the APEX HTML bar chart behind the scenes
        2. Understanding self-generated HTML charts
        3. Creating a report from XMLTYPE
      6. Creating Google visualization charts
      7. Creating Flash charts
        1. Customizing an XML chart
        2. Understanding Anychart options using a doughnut chart
        3. Discussion on scatter and 3D stacked charts
        4. Discussion on a gauge chart
        5. Discussion on a gantt chart
        6. Discussion on a candlestick chart
        7. Creating Flash image maps
      8. Creating a calendar
      9. Creating a report with images
      10. Creating a dialog box
      11. Creating a context menu
      12. Creating a wizard and using hierarchical queries and regular expressions
        1. Setting different validations for different parts of a page process
      13. Summary
    12. 5. Flight to Space Station: Advanced APEX
      1. Creating HTML image maps
        1. Server-side image maps
        2. Client-side image maps
      2. Creating PL/SQL Server Pages (PSP)
      3. Understanding and using loadjava utility
      4. Creating funnel charts using FusionCharts
      5. Creating tag cloud
      6. Creating plugins
        1. Creating item and page process plugin
        2. Creating DA plugin
      7. Creating websheet application
        1. Understanding datagrid
        2. Understanding reports
        3. Understanding the features of administration and view drop-downs
        4. Understanding Websheet Help menu
        5. Sharing websheets using ACL
      8. Configuring the mail
      9. Downloading APEX application without web server
      10. Understanding Oracle OLAP cubes
      11. Understanding Oracle's advanced queuing
      12. Understanding other APEX features
        1. Understanding locking and unlocking of pages and team development
        2. Understanding database object dependencies report
        3. Understanding advisor
        4. Understanding shortcuts
        5. Understanding data loading wizard
        6. Understanding application express views
      13. Understanding background jobs in APEX
      14. Knowing about important functions from the API
        1. Knowing about the APEX API
        2. Knowing about the database API
      15. Summary
    13. 6. Using PL/SQL Reporting Packages, Jasper, and Eclipse BIRT
      1. APEX reporting using PL/PDF and PL_FPDF
        1. Creating PDF using PL/PDF
        2. Creating PDF using PL_FPDF
      2. Process to create RTF, XLS, CSV, and HTML documents
        1. RTF, CSV, and HTML printing
        2. XLS document generation
      3. APEX reporting using fop and Cocoon
        1. APEX reporting using fop.war and WebLogic
        2. APEX reporting using Apache Cocoon and WebLogic
      4. Creating Jasper reports
        1. Using Jasper in APEX
      5. Creating reports using Eclipse BIRT and integrating with APEX
        1. Running the BIRT report on WebLogic
      6. Summary
    14. 7. Integrating APEX with OBIEE
      1. Understanding Oracle fusion management architecture
      2. Understanding OBIEE
        1. Understanding the BI Server component
        2. Understanding the BI Presentation Server
          1. Understanding Dashboards, analysis (answers), and filters
          2. Understanding KPIs and KPI watchlist
          3. Understanding Actions
          4. Understanding OBIEE Mapviewer
          5. Understanding strategy management
          6. Configuring the Gmail SMTP server in OBIEE
          7. Understanding agents
      3. Integrating OBIEE with APEX
        1. Integration using web services
        2. Integration with APEX using Go URL
          1. Integration using iFrames in APEX
      4. Understanding BI Publisher
        1. Creating and mailing reports in BI Publisher
          1. Creating data model in BI Publisher
          2. Using the BI Publisher's MS Word plugin to make rtf templates
          3. Assembling the rtf template and data model together
          4. Scheduling reports and e-mail delivery in BI Publisher
          5. Creating barcode reports in BI Publisher
          6. Dynamic BI Publisher
      5. Integrating BI Publisher with APEX
        1. Using convert servlet in Instance Settings
        2. Integrating APEX with BI Publisher using web services
        3. BI Publisher guest folder
      6. Summary
    15. 8. All About Web Services and Integrations
      1. Understanding reports on web services and collections
        1. Understanding native XML DB web services
          1. Setting XML DB web services
          2. Creating a report on native XML DB web services
          3. Using XMLTable to parse a web service response
          4. Using a native web service to return a collection of rows
        2. Querying XML DB using a web service
        3. Implementing and using RESTful web services using Resource Templates
          1. Configuring RESTful web services using Resource Templates
          2. Creating RESTful web services using Resource Templates
          3. Parsing JSON objects and using MAKE_REST_REQUEST
        4. Creating and using RESTful PL/SQL web service
      2. Understanding and implementing BPEL
        1. What is BPEL
        2. Installing BPEL
        3. Developing a BPEL Composite
        4. Deploying BPEL code
        5. Testing the BPEL code
        6. Implementing BPEL Human Workflows
          1. Configuring the General section
          2. Configuring the Assignment section
          3. Understanding Human workflows and Worklist
      3. Integration with SAP crystal reports
      4. Migrating from MS Access to APEX
      5. Migrating from Oracle Forms and Reports
      6. Integration with Google API
      7. Integration with Oracle R Enterprise
      8. Summary
    16. 9. Performance Analysis
      1. Tuning pointers for development
        1. Using v()
        2. Using the page and region caching
        3. Understanding the weighted page performance of APEX
        4. Using the #TIMING# substitution string
        5. Choosing a pagination scheme
        6. Tuning the like comparisons
        7. Using the bind variables
        8. Using materialized views
        9. Using bulk operations
        10. Using sequences
        11. Understanding indexes
          1. Understanding the bitmap index
          2. Understanding the reverse key index
          3. Ordering columns in an index
        12. Using the with clause
        13. Understanding partitioning strategies
        14. Understanding anonymous blocks versus stored procedures
        15. Using star transformation
        16. Understanding clustering of tables
        17. Understanding parallelism
        18. Using code inlining
        19. Understanding short circuit evaluation
        20. Understanding PLSQL_CODE_TYPE
        21. Using PL/SQL datatypes
        22. Using HTML in SQL
        23. Implementing XML PIVOT queries
        24. Understanding denormalization
        25. Understanding secure files, deduplication, and large object storage
      2. Discussions on HTML, JavaScript, and client-side tools
        1. Understanding image storage
        2. Using browser-specific tools
        3. Using minified JavaScript
        4. Compressing HTML for better performance
        5. APEX Listener parameters
      3. Discussions on database tools for performance tuning
        1. Using PL/SQL hierarchical profiler and DBMS_PROFILER
        2. Understanding Data Guard
        3. Understanding SGA result cache
        4. Understanding SQL work areas
        5. Using data compression
        6. Understanding segment space problems
        7. Understanding the Database Resource Manager
        8. Understanding the SQL Access Advisor and SQL Tuning Advisor
        9. Understanding temporary tablespace
        10. Understanding SQL monitoring
        11. Understanding DB_FILE_MULTIBLOCK_READ_COUNT
        12. Understanding extended statistics
        13. Understanding SQL Performance Analyzer
        14. Understanding SQL Tuning Advisor
        15. Understanding pinning in keep pool
        16. Understanding Automatic Database Diagnostic Monitor (ADDM)
        17. Understanding Automatic Workload Repository (AWR)
        18. Understanding index-organized tables
        19. Understanding row chaining and row migration
        20. Understanding the clustering factor
        21. Understanding the Active Session History report
      4. Summary
    17. A. Appendix
      1. Steps to create PACKT_SCHEMA
      2. SQL Injection
        1. Dynamic SQL with concatenated variables
        2. Assumption of formats and implicit conversions
      3. Cross-site scripting
      4. Database and web interaction in DAD and Listener configurations
        1. The DAD configuration
        2. The Listener configuration
      5. APEX behind the scenes
        1. Enabling auditing on the APEX_PUBLIC_USER schema
        2. Switching off the auditing
    18. Index