Detail section in line with report, part 2

In my previous post I explained how you can display a details section within an (interactive) report. The solution relied on using an iframe to display a detail report or –form on another page and on jQuery to display or hide the details. This solution works great in some cases, but it has a few shortcomings. In this post I will discuss two of the most apparent problems.

The first big problem arises when we use session state protection. Session state protection prevents unauthorized manipulation of the URLs within your application. The session state protection creates a checksum that is appended after each page URL, without a valid checksum you can’t navigate through your application. In our setup thus far we could simply create the URL for the iframe that holds the report details. With session state protection enabled, we need to find a solution to create a valid checksum that we can use in our URL.

The apex_util package has a function, prepare_url, that takes your URL as parameter and returns you the same URL extended with a checksum. This requires us to use pl/sql, and our URL is created in a jQuery dynamic action. What we’re going to do is remove the URL from the dynamic action and instead add it as a column to the report query, using the prepare_url function.

Since we don’t need to display the URL in our, but we do need to be able to find it with JQuery, we give the report heading a simple span as title and de “Display as” attribute we set to “Standard Report Column”. In the column link section we set the “Target” attribute to “URL” and the “URL” attribute to “#DET_LINK#” – this is a substitution of the column name we gave the prepare_url column in our report query.

Now that we have our URL ready, we need to modify our dynamic action. The URL to the details section of each row can be reached with But note that the iframe needs the url domain prepended whereas the prepare_url function doesn’t . Also, now that we have the URL ready, we don’t need the variables for application id and session id, so we remove them from the dynamic action. The code no looks as follows:

With these changes we have a working report and iframe again, this time with session state protection.

The second issue I’d like to discuss is partial page refresh. Partial page refresh reloads a region of your page, without submitting or reloading the entire page. This is really handy, says when You use a modal page to update or modify the details of our your report and you want to immediate show those changes. Unfortunattely, the refresh of the region unbinds our jQuery function that opens and closes the details, so we can no longer show details until we reload the entire page. To better show this issue I’ve changed the detail report in the example application to a tabular form that lets you make some changes to the emp table.

To better display the and control the tabular form, we add a hidden item ‘READONLY’ that we will set to ‘Y’ from our report url link, so that when we open the form as an iframe the item will be ‘Y’. We add a page load dynamic action on the tabular form page that will hide and DISABLE! The page buttons and will disable the form fields.

Also we add a modal page to edit the EMP table. For this we can use the Modal Page plugin from Skillbuilders, that’s an easy and flexible plugin that lets you make a modal page in just a few clicks.

Now it is important to remember that we had session state protection enabled, so the modal page attribute “Static URL” must get an URL with a checksum. To do this, we can a hidden item, here TEMP_URL and set it’s source with a pl/sql expression. As expression we again use the apex_util.prepare_url function. We create a buttton to fire the dynamic action.

At this point we have a page with a modal page that we can use to update the EMP table and an iframe that we can use to display the employees per department. To immediately show the changes we make in the modal page, we add a refresh action on report region. We let the refresh take place after we click on the close button of the modal page.

Because the refresh of the report unbinds the dynamic action that controls the display of the report details, we need to find a way to rebind the dynamic action. At this point it might be a wise dissision to avoid a growing number of dynamic actions, and move our jQuery function to a seperate js file. In a file it is also rather easy to control the rebinding of the function after the report region refreshed.

In our js file we have two functions. One is the click function we already have, the other is a function that adds a class to each row in the dept report. That function needs to be rebinded after the region refresh. The code in our file looks like this:

We add the file to our page by uploading it in shared components > static files. In our page we can add the file in the page settings under the JavaScript section. We can reference the file library with the #APP_IMAGES# substitution.

At this point we have all we need. The dynamic action that controlled the display of the details section is now covered by the JavaScript file, so we can delete the dynamic action. By now our report is working as we expect it to and it can handle session state protection and partial page refresh.

to see a working demo watch my demo application.
Enjoy.

Show report details in line

The other day I needed to create a report which could dynamically show and hide details of a row. I have seen examples before where either a pl/sql region was used or a function returning the required details was being used. In both cases you have create a procedure or function that returns html based on a query. You have to think of a way to collect the required data and then wrap it in HTML divs, td, etc. The result is very elegant, but the setup is rather time consuming. That can be worth the while if you can reuse that code on other reports in your application.

Unfortunately, in my case I only needed to show details in one report. To make it more complex, the details that I needed to show were subject to debate. It was likely that the exact details that needed to show were going to change during the development of the application. Therefore I chose a swift and simple solution based on jQuery and iFrames.

In the following example I will reconstruct what I’ve done, based on the DEPT and EMP tables. The result is a report on DEPT, which, as detail, shows all the employees of a department. We’ll make two pages, one for the DEPT report and one for the EMP report, which will be displayed in line in the DEPT report. Let’s start with the latter.

We need a page with a report on EMP. We only want to show the employees of the department we select in the DEPT report, so we need to add a where clause in the report query, and we need to add a page item that we can set. Here, I’ve created a hidden item calls ‘DEPTNO’.

The report query needs to be modified: we add a where clause that takes DEPTNO as a parameter

Just to make sure that our report shows up nicely as a detail, we will select page and report templates with little style features. Here I used “Popup” for page template and “No Template” for the report region. Also I’ve added a “nowrap” style element to the region header; this will ensure that the report columns will not break in two lines when the content gets to wide.

That’s all for the employee details. Now we’ll go to the DEPT page. Here too we create a page with a standard region, this time on the DEPT table.

On this page we need to create a dynamic action that will control the display and hide of the employees’ details. Here I’ve created a page load JavaScript action.

What the function does is as follows. When we click on a row, we check if we are already showing the details for that row. If we don’t we’ll create the URL of the employee page and put it in an iFrame. That iFrame we’ll wrap in a table row and paste it right after the row we clicked on. Else, if we already have the details for that row, we close the details section. The code is as follows:

And that's all. No we have a report showing all departments. When we click on a row we get a detail report of all the employees in that department. When we click the row again the detail closes. The great benefit of this setup is that we can easily change the way our detail section looks by simply modifying the report on employees.

See how it works in my sample application.
Good luck.

Understanding APEX URL synax

A good understanding of the way APEX uses URL’s helps you to easily navigate through your applications. It’s also helpful for version control and maintenance. In this post I will highlight the key features of the APEX URL syntax, complete documentation on the URL syntax can befound here

The URL consists of a static part for the domain and DAD, and a dynamic part, consisting of an number of colon delimited parameters. A typical APEX URL looks like: http://apex.oracle.com/pls/apex/f?p=42742:8:100133297155888::NO::P8_ROWID:ABSCZ%2FAE7AAAACVAAD This URL encloses information for navigation (to the coorect page) as well as identification and a bunch of other info as well. To break it down the URL consists of the following parts:

• http://apex.oracle.com - the domain/URL of the server
• pls - refers to the mod_plsql cartridge being used by the weblistener
• apex - is the database access descriptor (DAD). This is used by the HTTP server to connect to the database server
• f?p= - This is just a prefix used by APEX
• 42742 – the ID of the application being called
• 8 – the ID of the page being called
• 100133297155888 – the ID of the current session
• NO - Debug mode is set to ‘NO’
• P8_ROWID - a page item ROWID on page 8
• ABSCZ%2FAE7AAAACVAAD - the value to which P8_ROWID will be set

To link between pages in your application you can use an URL to has the following parameters

The following table describes all the parameters in the URL:

Syntax	Description
App	The application ID or the application alias
Page	The page ID or the page alias
Session	Identifies a session ID. You can reference a session ID to create hypertext links to other pages that maintain the same session state by passing the session number. You can reference the session ID using the syntax: Short substitution string: &SESSION. PL/SQL: V('SESSION') Bind variable: :APP_SESSION
Request	Sets the value of REQUEST. Each application button sets the value of REQUEST to the name of the button which enables accept processing to reference the name of the button when a user clicks it. You can reference REQUEST using the syntax: Substitution string: &REQUEST. PL/SQL: V('REQUEST') Bind variable: :REQUEST
Debug	Displays application processing details. Valid values for the DEBUG flag include: YES. LEVELn NO Setting this flag to YES displays details about application processing. Setting this flag to LEVELn (where n is between 1 and 9) controls the level of debug detail that displays. The value of YES equals LEVEL4. You can reference the Debug flag using the following syntax: Short substitution string: &DEBUG. PL/SQL: V('DEBUG') Bind variable: :DEBUG
ClearCache	Clears the cache. This sets the value of items to null. To clear cached items on a single page, specify the numeric page number. To clear cached items on multiple pages, use a comma-separated list of page numbers. Clearing a page's cache also resets any stateful processes on the page. Individual or comma-separated values can also include collection names to be reset or the keyword RP, which resets region pagination on the requested page. The keyword APP clears cache for all pages and all application-level items in the current application and removes sort preferences for the current user. The keyword SESSION achieves the same result as the APP keyword, but clears items associated with all applications that have been used in the current session.
ItemNames	Comma-delimited list of item names used to set session state with a URL.
ItemValues	List of item values used to set session state within a URL. Item values cannot include colons, but can contain commas if enclosed with backslashes. To pass a comma in an item value, enclose the characters with backslashes. For example: \123,45\
PrinterFriendly	Determines if the page is being rendered in printer friendly mode. If PrinterFriendly is set to Yes, then the page is rendered in printer friendly mode. The value of PrinterFriendly can be used in rendering conditions to remove elements such as regions from the page to optimize printed output. You can reference the printer friendly preference by using the following syntax: V('PRINTER_FRIENDLY') When referenced, the Application Express engine does not display tabs or navigation bars, and all items are displayed as text and not as form elements.

The application- and page aliases, as well as the substitution strings for session, request and debug, are very handy for linking between pages, say when you want to use a button to redirect to another page in your application. They play a role in maintenance too.

The session ID is unique for each session, so if you put your session hardcoded in the redirecting URL, navigation will only work for your current session, the URL will be useless as soon as your session has expired. Using application and page aliases are helpful for example when you want to move your application from development to production. The application ID you used in the development environment might not be available in your production environment . So if all redirecting URL’s in your application have the application ID hardcoded, redirection will no longer work. Using an application alias solves this problem. The page alias is mostly helpful for creating understandable navigation. You can let your users navigate to a page called ‘home’, instead of page ‘1’.

To make your page even more independent of hardcoded values, you can consider replacing the ‘P_’ part of your page items by ‘_’ .doing so enables you to move your page within your application without losing navigation logic.

Using jQuery for Drag and Drop

Recently I wrote a blog on how to use drag and drop (DaD) in your APEX application. Unfortunately the javascript I used didn’t work in all browsers. I found a solution using jQuery UI. I’ve modified the code in my example application and now DaD seems to be working on all major browsers. First thing that I’ve noticed was that the draggable table cells needed to be in the same region as the droppable form cell. So I’ve made one region with both the form on the emp table and the report on the jobs table.

Then, to make the table cells draggable I had to change the classes added to the table cells. The page load dynamic action now looks as follows:

The job form cell (P8_JOB) had some classes added to it, but in this jQuery setup that was no longer necessary. I’ve removed them from the ‘HTML Form Element Attributes’. The last step was to add the draggable and droppable functions in the page header. Two jQuery libraries had to be added:

• http://code.jquery.com/jquery-1.9.1.js
• http://code.jquery.com/ui/1.10.3/jquery-ui.js

Then all table cells with class “.job ” are being made draggable and Th P8_JOB page item is made droppable to receive a draggable element, and the P8_JOB item is given the value of the job cell that is dragged into it.

The result is more robust then my previous example, since it is now supported by all major browsers (assuming that your browser is up to date). This browser compatibility can be quite a problem since when working with HTML5 and/or JavaScript. It seems jQuery is better supported then conventional JavaScript. This website can be quite helpful to find out if the HTML5 you’re trying to use is supported by your browser (kudos to Elie for bringing the side under my attention).
You can check the working example here. Again, have fun.

Applying Drag and Drop in an APEX Form

Today I was asked if you can use drag and drop functionality in APEX. I figured APEX shouldn’t be the problem, but I had to look into the exact implementation. In this blog I will share my findings with you.

Drag and Drop (DaD) is a HTML5 feature, like other HMTL5 features there’s nothing in APEX preventing you from using them. However, your browser must be HTML5 compatible and, in this case, support DaD. For a listing of compatible browsers check here. DaD consists of triggering- and receiving elements. An element that is dragged can trigger the following events (thanks to netmagazine):

• dragstart: triggered when dragging a draggable element
• drag: triggered by moving the draggable element
• dragend: triggered by dropping the draggable element

Elements that receive draggable elements can trigger the following events:

• dragcenter: triggered when a draggable object is dragged over an element
• dragleave: triggered when a draggable object is dragged outside of an element
• dragover: triggered when a draggable object is move inside an element
• drop: triggered by dropping a draggable object.

In the following example I will show a form on the EMP table in which you can update the JOB column by dragging a job role into the JOB form field. The first step is to create a form; I chose to make a form on report. On the form page I’ve added a standard report with the various job roles that can be selected. For this example I created a table “JOBS”, which holds the various job roles.

If we want to be able to drag the various job roles, we need to make them “draggable”. Also, for further handling, I want each job/table cell to have an ID. I add them with a dynamic action that fires on page load.

The function in the dynamic action loops trough each table row of report_jobs – the ID of the JOBS report table- and adds a the following items

• a class “job”;
• an attribute “draggable” set to “true”, this will make it possible to drag the table cell around the screen;
• an attribute “ondragstart” , which will trigger a function “dragJob”;
• an ID to uniquely identify each table cell. Here the id will be set to the job role in that cell.

Next we need to tell the JOB item in the form that it can receive draggable items. To do so I’ve added the following attributes:

So now we have a report table with jobs, each job we can drag around the screen, and we have a page item that can receive a draggable element. Now to actually set the page item with the value of the dragged job, we need two functions. For simplicity I’ve added them to the page HTML header.

The first function is started as soon as you start dragging an element. The element’s id is set to the dataTransfer.

The second function is started when you drop the element in the page item and will assign the value of the dragged table cell to the page item.

And that’s about all there’s to it. To view a working example, please visit my demo application: In the interactive report click on the edit link of an employee and then in the form try to set the change the job of an employee by dragging a job from the jobs report. So as you can see, with a few simple steps you can create DaD functionality to your APEX application. Please bear in mind that your browser needs to be HTML5 compatible and that this example is simplified as an example. However, when used properly your application can benefit from DaD by making it more intuitive and interactive.
Good luck!

Integrate JasperReports in your application and manage security

In a previous blog I explained why I think JasperReports is such a great report engine to use alongside your APEX application. In this post I would like to explain how you can integrate JasperReports in your APEX application and how you can manage a security. Since the installation of reports library is well documented by JasperReports self and since the exact install instructions depend on your setup, I will not go into explaining the installation. Instead I will take as a starting point a situation where you have a server with one or more APEX applications and a JasperReports engine.

With JasperReports you can call a report over the URL, the URL would typically look something like:

As you can see all details regarding the report you’re requesting are passed over the URL, including output format and data source. If your report would require additional parameters (for example for the where-clause of your report query), they could simply be added by extending the URL with &=:

Now to call a report from your APEX application, you could add a button with a link to the report URL. Unfortunately, this would not be really flexible. Another problem would be that your URL is visible for everyone who has access to your application.

A more dynamic way would be to create a procedure that builds the url for you, based on parameters that you can define and manipulate on your APEX page. You can base your procedure on the HTTP-UTIL package. Fortunately, such a package already exists. It is a free to use integration package, made by Dietmar Aust from Opal Consulting. With that package installed in your database, you can create the report URL with a page process. With the process calling the report, the URL is no longer visible on your page; therefore your report localities are not immediately disclosed.

So now we have a way to hide the URL, but the report can still be reached once someone somehow manages to create the URL himself. As explained on the Opal Consulting website, you can easily create an extra security layer by using a firewall. That way only the APEX engine can call the report engine and your report can no longer be called over the URL directly.

A last security step that you can add is verification on session id. In APEX active sessions are stored in a table (WWV_FLOW_SESSION$). The table self cannot be queried directly, instead you can use the ‘APEX_WORKSPACE_SESSIONS’ view. By adding a where clause to your report query, you can validate whether the calling apex session is an active one.

As I’ve shown in this blog, you can secure your application’s reports with a few simple steps. Hiding the calling URLs is a first and in my opinion essential step if you want to secure your reports. If you have the possibility, firewalling the report engine is strongly recommended. If that is somehow not an option, or if you want to take security a step further, you can consider validation on session id to prevent unauthorized report calls.

Apex and JasperReports: printing reports

APEX is a great tool for web application development. It’s fast, and easy to learn, on top it’s highly flexible. That being said, APEX has no native method for printing reports from your application. Instead you can connect a print server to let you handle your report printing.

The reason that the APEX development team didn’t create a build in print server seems to be that it is beyond the focus of the APEX project. Besides, Oracle already has BI publisher as print server. Another reason could be that building a native print server would likely require the use of Java. That would be a problem with Oracle XE that does not support Java in the database. So the choice of supporting other print servers instead of building a native one makes sense. It does of course; raise the question what tool to use for printing reports?

Oracle has its own reporting tool: BI publisher. With the help of a browser interface or an IDE you can easily build documents and reports, schedule mailings, etc. The downside is that it’s not license free. Fortunately APEX is distributed with another print server: Apache FOP (Formatting Objects Processor). FOP is a print formatter that uses XSL –FO, which are XML like templates that determine the layout and output of the report. The data is delivered in a XML document. Unfortunately, building the XSL and XML files is a complex and time consuming process, which will likely result in high development costs.

An alternative reporting tool is JasperReports, an open source reporting engine by Jaspersoft. The print server takes data from virtually any data source and translates them into high quality, printable reports. You can easily build reports with an IDE of choice: iReport -a Netbeans-based designer, or Jaspersoft Studio –an Eclipse based designer. Best of all: it is cost free! That is, there is a very functional community edition that lets you build any type of printable report you can imagine. You call a report with an URL that contains your data source, and – if your report query requires so – the parameters to build your query. Basically in the same manner as APEX calls an application page (although the syntax is rather different).

The fact that it is easy to use and allows for rapid development, along with the fact that the print request is comparable with the way APEX transmits page calls, makes JasperReports the ideal reporting tool to incorporate in your APEX application. An added benefit is that the print server along with the IDE are cost free (community edition). In an upcoming blog I will go further into incorporating JasperReports into your APEX application and building a secure interface.

To get started with JasperReports, please visit the Jaspersoft website; there you can also download iReport or Jaspersoft Studio. To download JasperReports, go here. If you want additional information on JasperReports, iReport, or if you’re looking for installation instructions, you can look here.

Manipulate multiple tables from one tabular form

A tabular form enables you to process a number of rows in one go. On page submit all changed records of the tabular form will be inserted, updated or deleted from the subjected table.

Up until now there is a restriction, though: you can put only one tabular form on a page (apparently Apex 5.0 will let you use more than one tabular form on a single page). The reason for this restriction has to do with the way Apex processes tabular forms. There are however, various workarounds to this restriction. One such workaround is to use one tabular form to manipulate more than one table. In this post I will demonstrate how to create such a tabular form.

We will manipulate more than on table, for this we need to create a view that contains all the columns of the tables that we want to manipulate. This itself is straight forward and enables us to query records from multiple tables. However, it doesn’t let us perform inserts, updates or deletes. For that we need “instead-of-triggers” . Instead of triggers enable us to do inserts, updates and deletes on a view, i.e. to perform dml on the underlying tables.

Now we have multiple tables, joined in one view, which we can manipulate with one tabular form. Unfortunately, all columns of the view are visible in the tabular form, while they don’t belong to the same tables. To make sure that when running the page you only see the right –the for that time appropriate- columns, we need to give the columns a conditional display. You can set the conditional display with ‘edit page item’ > ‘conditions’. Which columns should appear at what time is of course entirely dependent of what your page should be showing at any given time.

This is all you need for a simple workaround to manipulate more than one table from a single tabular form. Note however that tabular forms rely heavily on rowids. This can be a problem, especially with views. To secure your page from processing, you could remove the Apex-generated page processes and replace them with your own procedures.

Good luck!

Custom multi row processing using tabular form

In this post I will explain how you can perform a custom function or procedure on multiple rows in a tabular form. In APEX, a tabular form lets you do multiple inserts or deletes using the ‘MULTI_ROW_INSERT’- and ‘MULTI_ROW_DELETE’-buttons. You can also do updates on multiple records, but you need to alter each record individually. Doing the same update routine on a number of records, would require you to set each new value by hand, before doing the submit. Wouldn’t it be great if you can do an update on multiple selected rows in one page submit? In the following example I will show you how you can update the salary of a number of selected employees. The example consists of a tabular form on the standard ‘EMP’ table.

On the tabular form page I’ve put a hidden item to store the primary key values of the selected rows, in this case ‘EMPNO’.

To fill the page item with the primary key values of the selected rows, I use a dynamic action. The dynamic action will fire on every change in the tabular form region. This way every time you check or uncheck a checkbox, the dynamic action will evaluate which rows are selected.

Using jQuery, I fetch the selected values. Note that I concatenate  all the values to one string delimited by a colon.

In my example I perform the update routine as a page submit, so I need two more things:

• a region button to submit the page
• an after submit page process. 

The button is in this case a standard button that performs a page submit, here I’ve called the button ‘RAISE_SAL’. Last, in the after submit process, I will do the actual update of the employee salaries. The selected employees will get a standard raise of ten percent. In the page process I use the ‘APEX_UTIL.STRING_TO_TABLE’ function to put the concatenated EMPNO’s into an array.  In the loop the ‘SAL’-column of the EMP table is updated for all the EMPNO’s from the array.

And that wraps it up. Now you have a tabular form with which you can update multiple rows with the same update routine in one go. To see a running version of the above example, click here. Enjoy!