I have finally finished writing my book on Oracle APEX reporting. The book
| Although the book primarily projects Oracle APEX as a reporting solution, it also speaks about the integration of APEX with a number of other technologies such as Apache FOP, Apache Cocoon, PL_PDF, PL_FPDF, iReports & Jasper, Eclipse BIRT, BI Publisher 11G, Oracle Business Intelligence 11G, BPEL, Oracle forms, Google API and Oracle R Enterprise. The the text of the book is coupled with an APEX application with every chapter. The application of every chapter contains code to give you a live demo of the subjects discussed in the chapter. The following is a brief overview of the contents of the book |  |
Chapter 1: Know Your Horse Before You Ride It
This chapter lays the foundation of understanding the tool. A deep understanding of this chapter is vital to know the framework of APEX. This chapter talks about the anatomy of APEX and enables the reader to set up an environment.
What is really new in the new avatar of APEX?
APEX configurations
APEX configuration using DAD
Internal mod_plsql configuration
External mod_plsql configuration
APEX Listener configuration
Installing the APEX engine and Listener
Installing the APEX engine in the database
Creating a WebLogic domain and starting the servers
Setting the APEX Listener
Configuring and deploying APEX Listener on the WebLogic domain
Creating APEX workspace
Understanding the APEX URL
The protocol
Host name and port number
DAD name and web context
The f procedure
Application ID in the URL
Page number in the URL
Session ID in the URL and Session Management
The zero session ID
Request handling
Debugging
Error handling
TKPROF
Cache management
Passing values and navigation
Making APEX printer friendly
Decoding APEX page submissions
APEX behind the scenes
Other web interfaces
A pessimist’s view of APEX
Cap of 200 page items
SQL injection
Cross site scripting
SummaryChapter 2: Reports as We All Know Them
This chapter is dedicated to ethnic reporting. The reports introduced in this chapter exist as an out of the box feature of most reporting technologies. The chapter presents ways and means to implement these known reporting requirements in APEX.
Creating database objects and installing the reference application
Implementing external table authentication
Displaying environment properties using the USERENV namespace
Displaying the CGI environment variables
Implementing classic report search functionality
Enabling sorting and CSV download
Implementing group reports
Using Break Formatting in classic reports
Using JavaScript to modify report
Using grouping sets and HTML formatting in a query
Additional methods of formatting
Highlighting the searched text in a report
Editing a template for additional formatting
Implementing matrix Report
Understanding with clause
Understanding Oracle 11g’s pivot operator
Understanding String Aggregation techniques
Data highlighting using Dynamic Actions and JQuery
Advanced formatting using APEX templates
Understanding dynamic query region in APEX
Implementing hierarchical reports
Hierarchical reports with drilldown
Creating dynamic messages using the substitution variables
Creating dynamic breadcrumbs for drill ups
Creating hierarchical report with Connect By clause
Creating hierarchical report using recursive with clause
Creating a tree on hierarchical data
Understanding methods to upload files in APEX
Using DA to set page items and side effects of PPR
Work-around to the side effects of PPR
Using substitution variables for labels
Auto feeding one APEX item based on another
Method to upload files using WWV_FLOW_FILES
Understanding download methods
Download using APEX format mask
Download using developer defined stored function
Download using APEX_UTIL.GET_BLOB_FILE_SRC
Download using the p process
Implementing soft deletion with AJAX and APEX templates
DML operations and report filtering using DA, JavaScript and page processes
Filtering report and logging values using Dynamic Actions
AJAX: Two way communication between page process and JavaScript
Implementing a tabular form
Creating a tabular form
Changing item type on user event
Implementing master detail report and displaying complex types
Displaying complex types and varray in a report
Implementing time series reports
Creating aggregation on partitioned time dimension region
Time series analysis with analytical functions and time dimension
Using query partition clause for data densification
Creating aggregations using model clause
Implementing data-level security
Using VPD
Using query filters
SummaryLove to talk? Here are interactive reports for you. These reports are exclusive to APEX. These reports let a user do a lot more with a report. The possible tasks include highlighting, filtering, coloring, selecting columns, creating aggregates etc. These reports are to a client what a box of crayons and a drawing book are to a child.
About the reference application
Important features of Interactive Reports
Search functionality
Using the Select Columns feature of the Actions Menu
Using the Filtering feature of the Actions Menu
Linking Interactive Rreports
Using the Rows per Page feature of the Actions Menu
Customizing the number of rows in an IR
Using the Formatting options of the Actions Menu
Using Control Break
Using the Compute option
Using Charts in an IR
Using the Flashback feature of the Actions Menu
Using the Save Report feature of the Actions Menu
Using the Reset and Download features of the Actions Menu
Configuring eE-mail functionality
Downloading in CSV and HTML formats
Downloading in PDF format
Using the Subscription feature of the Actions Menu
Other configurable attributes of an IR
Using the Link Column section onf the Report Attributes page
Using the Column Group section on the Report Attributes page
Using the Icon View section onf the Report Attributes page
Using the Detail View section onf the Report Attributes page
Using the Advanced section onf the Report Attributes page
Using Dynamic Actions (DA) to add custom functions in Actions Menu
Using CSS in IR
Implementing conditional formatting using CSS
Using APEX region attributes with CSS for formatting
Implementing conditional formatting using JQuery and CSS
Altering APEX theme attributes for formatting
Changing the font color of alternate rows in APEX
Understanding the Process of any customizations in IR
Using APEX views to create Classic report on saved IR Reports
Capturing report id using JavaScript
Creating Multiple IR on the same page
Authorizing user groups to view report columns
Creating Interactive Report Dashboard
Understanding Dynamic Interactive Reports
Using Native PL/SQL Table Function approach and conditional columns
Using Parallel Enabled Table functions
Understanding Interface Table function approach
Understanding ODCITableDescribe Function
Understanding ODCITablePrepare Function
Understanding ODCITableStart Function
Understanding ODCITableFetch Function
Understanding ODCITableClose Function
Understanding the Collection Approach
SummaryThis chapter brings a different flavor to reporting and the clients love this flavor. This chapter deals with images and all kinds of animations along with advanced reporting features. This chapter helps you engineer secret drilldown tunnels and magical mystical paths. This chapter is really the difference between an average reporting experience and a user friendly, visually soothing, sensually appealing, smooth reporting experience.
The reference application
Using LDAP authentication
Process to get the necessary DN from JXplorer
Creating sparkline reports
Creating reports with slider
Creating an APEX HTML chart
Displaying the top N and the bottom N rows in HTML chart
Understanding APEX HTML bar chart behind the scenes
Creating personalized HTML charts
Creating report from XMLTYPE
Creating Google Visualization charts
Creating Flash charts
Customizing chart XML
Understanding AnyChart options using the doughnut chart
Discussion on scatter and 3-D stacked charts
Discussion on gauge chart
Discussion on gantt chart
Discussion on candlestick chart
Creating flash image maps
Creating calendar
Creating report with images
Creating dialog box
Creating context menu
Creating wizard and using hierarchical queries with regular expression
Setting different validations for different parts of a page processes
SummaryChapter 5: Flight to Space Station: Advanced APEX
This chapter opens a new dimension to conventional reporting. This chapter presents advanced reporting methods and also shows the use the newest features of APEX 4.2 to standardize report developments. The world can exist without these but then merely existing is not fun.
Creating HTML image maps
Server side Image maps
Client side image maps
Creating PL/SQL Server Pages (PSP)
Understanding and using loadjava utility
Creating funnel charts using FusionCharts
Creating tag cloud
Creating plugins
Creating item and page process plugin
Creating DA plugin
Creating websheet application
Understanding datagrid
Understanding reports
Understanding the features of administration and view dropdowns
Understanding Websheet Help menu
Sharing websheets using ACL
Configuring mail
Downloading APEX application without web server
Understanding Oracle OLAP cubes
Understanding Oracle's advanced queuing
Understanding other APEX features
Understanding locking and unlocking of pages and team development
Understanding database object dependencies report
Understanding advisor
Understanding shortcuts
Understanding data loading wizard
Understanding application express views
Understanding background jobs in APEX
Knowing about important functions from the API
Knowing about the APEX API
Knowing about the database API
Summary
This chapter introduces us to an array of technologies
which can be used with APEX. These technologies include PL/PDF, PL_FPDF,
Eclipse BIRT and Jasper Reports.
APEX reporting using PL/PDF and PL_FPDF
Creating PDF using PL/PDF
Creating PDF using PL_FPDF
Process to create RTF, XLS, CSV, and HTML documents
RTF, CSV and HTML printing
XLS document generation
APEX reporting using fop and Ccocoon
APEX reporting using fop.war and WebLlogic
APEX reporting using Apache Cocoon and WebLlogic
Creating Jasper reports
Using Jasper in APEX
Creating reports using Eclipse BIRT and integrating with APEX
Running the BIRT report on WebLogic
SummaryBI Publisher has been the most popular reporting engine and is integrated with most enterprise solutions. It has been everybody’s sweetheart because people do not have to shell out a lot of cash for it. It gives pretty reports, development environment (MS word can be used for creating templates) is familiar and Oracle is coupling it with everything else in the oracle world. OBIEE, on the other hand, is the grand old lady of BI world. It has been facilitating business examination from the beginning of modern analytic culture. This chapter presents the use of both these technologies, shows the requirement which can be easily implemented in these technologies and also talks about the integration of these technologies with APEX.
Understanding Oracle Fusion Management architecture
Understanding OBIEE
Understanding The BI server
Understanding The BI presentation
Understanding Dashboards, Analysis (Answers) and Filters
Understanding KPIs and KPI watchlist
Understanding Actions
Understanding OBIEE Mapviewer
Understanding Strategy management
Configuring gmail smtp server
Understanding Agents
Integrating OBIEE with APEX
Integration using web services
Integration using GoURL
Understanding BI Publisher
Creating and mailing reports in BI Publisher
Creating Data model in BI Publisher
Using the BIP’s MS word plugin to make rtf templates
Assembling the RTF template and then data model together
Scheduling reports and Email delivery in BI Publisher
Creating Bar code reports in BI Publisher
Dynamic BIP
Integrating BI Publisher with APEX
Using Convert servlet in Instance Settings
Using Web services
BIP Guest folder
SummaryI adore this workshop. Here we combine the best of every tool to make an unbeatable machine. Some of the highlights of this chapter include BPEL implementations with Human workflows and their use in APEX, integration with technologies such as Oracle R & Google API, understanding various ways to create web services in APEX and converting Oracle forms to APEX.
Understanding Reports on Webservices and Collections
Understanding Native XMLDB web services
Setting XMLDB Webservices
Creating report on Native XMLDB webservices
Using XMLTable to parsing web service response
Using native webservice to return a collection of rows
Querying XMLDB using web service
Implementing and using RESTful web services using Resource Templates
Configuring RESTful webservices using Resource Templates
Creating RESTful webservices using Resource Templates
Parsing JSON objects and the using MAKE_REST_REQUEST
Creating and using RESTful PLSQL web service
Understanding BPEL
What is BPEL
Installing BPEL
Developing a BPEL Composite
Deploying BPEL code
Testing the BPEL code
Understanding BPEL Human Workflows
Understanding Tthe General section
Understanding Tthe Assignment section
Integration with SAP Crystal reports
Migrating from MS Access to APEX
Migrating from Oracle Forms and Reports
Integration with Google API
Integration with Oracle R Enterprise
SummaryChapter 9: Performance analysis
One can never have enough of this. This chapter is focused on understanding various ways to fine tune APEX applications for a good user experience. The chapter talks ways to tune and debug client side code such as JavaScript. It also talks about a number of pointers that can help in the development of better APEX applications and finally it talks about the database tools that can help us understand and fix performance bottlenecks.
Tuning pointers for Development
Using v()
Using Page and region caching
Understanding Weighted page performance of APEX
Using the #TIMING# substitution string
Choosing Pagination scheme
Tuning the like comparisons
Using bind variables
Using Materialized views
Using Bulk operations
Using Sequences
Understanding Indexes
Understanding Bitmap index
Understanding Reverse key index
Ordering columns in Index
Using WITH clause
Understanding Partitioning Strategies
Understanding Anonymous blocks vs stored procedures
Using Star transformation
Understanding Clustering of tables
Understanding Parallelism
Using Code inlining
Understanding Short circuit evaluation
Understanding PLSQL_CODE_TYPE
Using PLSQL datatypes
Using HTML in SQL
Implementing XML Pivot queries
Understanding Denormalization
Understanding Secure files, deduplication and large object storage
Discussions on HTML, JavaScript and Client side tools
Understanding Image storage
Using Browser specific tools
Using Minified Javascript
Compressing HTML for better performance
APEX Listener parameters
Discussions on Database tools for performance tuning
Using PL/SQL Hierarchical Profiler and DBMS_PROFILER
Understanding Data guard
Understanding SGA Result cache
Understanding SQL Work areas
Using data compression
Understanding Segment space problems
Understanding Database Resource Manager`
Understanding SQL Access Advisor
Understanding Temporary tablespace
Understanding SQL Monitoring
Understanding DB_FILE_MULTIBLOCK_READ_COUNT
Understanding Extended statistics
Understanding SQL Performance Analyzer
Understanding SQL Tuning Advisor
Understanding Pinning in Keep Pool
Understanding Automatic Database Diagnostic Monitor (ADDM)
Understanding Automatic Workload Repository (AWR)
Understanding Index Organized Tables
Understanding Row chaining and row migration
Understanding Clustering factor
Understanding Active Session History report
SummaryBlog updated on 29th Aug 2013
9 comments:
Hi Vishal,
Is your book available in India ???
It will be available everywhere around the world once it is published. The book can be pre ordered at http://www.packtpub.com/oracle-apex-4-2-reporting/book
The book is expected to be published in the next month
Vishal
Congrats Man!...
Very comprehensive list! Looking forward to reading it when it comes out.
Thanks Martin. The book is out. It is now available at http://www.packtpub.com/oracle-apex-4-2-reporting/book
-Vishal
Hi!
Did I missed something but I didn't find how to pass parameters to Jasper Reports with proper security...I mean no way for URL tampering or any kind of modifications to sent parameters to Jasper.
Hope I'll get an answer ..
Brg
Damir
Hi Damir,
On the question of Authentication:
The following code is a part of the book.It shows the use of utl_http.set_authentication function to pass the credentials that can be used for authentication. The book also demonstrates the method to pass credentials from the URL itself using other examples
DECLARE
l_http_req UTL_HTTP.req; l_http_resp UTL_HTTP.resp;
l_web_serv_end_pt_url VARCHAR2 (2000) := 'http://localhost:8444/jasperserver-pro/rest_v2/reports/reports/samples/AllAccounts.pdf';
l_blob BLOB; l_raw RAW(32767);
BEGIN
l_http_req := UTL_HTTP.begin_request (l_web_serv_end_pt_url, 'GET', 'HTTP/1.1');
DBMS_LOB.createtemporary(l_blob, FALSE);
utl_http.set_authentication(r => l_http_req, username => 'jasperadmin', password => 'jasperadmin', scheme => 'Basic', for_proxy => FALSE);
l_http_resp := UTL_HTTP.get_response (l_http_req);
BEGIN
LOOP
UTL_HTTP.read_raw(l_http_resp, l_raw, 32767);
DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_resp);
END;
owa_util.mime_header('application/pdf',false); htp.p('Content-length: ' || dbms_lob.getlength(l_blob));
owa_util.http_header_close; wpg_docload.download_file(l_blob);
end;
On the question of passing arguements:
The following syntax can be used to pass arguements to restful webservice.
http://:/jasperserver[-pro]/rest//path/to/object?arg1=value1&arg2=value2&..
The book does not mention this syntax of passing arguements but has a link to http://community.jaspersoft.com/sites/default/files/docs/jasperreports-server-web-services-guide.pdf
The above documentation talks in detail about this syntax
Other methods
Apart from RESTful webservice, SOAP web service of Jasper can also be used in APEX.
The book has a link to http://www.opal-consulting.de/apex/f?p=20090928:4 as well. This link also gives a method to link Jasper to APEX
Regards,
Vishal
Visham,
THX for reply but...
"http://:/jasperserver[-pro]/rest//path/to/object?arg1=value1&arg2=value2&.."
is exactly what I want to avoid because user may change arguments and make URL tampering very easy ...
And this was mine initial ask ... how to avoid that?
Brg
Damir
Hi Damir,
Jasper has SOAP web services as well. You can try those. The following guide talks in detail about these webservices.
http://community.jaspersoft.com/sites/default/files/docs/jasperreports-server-web-services-guide.pdf
Regards,
Vishal
Post a Comment