Parameter input screen for Jasper Reports part 2

In my previous post I explained how you can create a parameter input form from which you can run a Jasper Report. In this post I will demonstrate how you can turn that parameter form into a modal dialog that pops up as you call it and closes once you run the report.

If you’ve followed the example in my previous post you’ll have one separate page with a dynamic pl/sql region as parameter form. To let it appear as a proper dialog, we need to us a plain template, something like “Popup” will do. For the region we’ll choose the “DIV region with ID”-template. With these templates we don’t get any of the region borders, page headers or tabs that come with other visual rich templates. For our dialog such a plain template is just what we need.

Now that the parameter form is ready, we need to go back to the page that will be calling the dialog. We’ll be calling the parameter form as an iframe. We need a HTML element to which we can append the iframe. We can create such an element in the “Page HTML Body Attribute” of the page. Here we’ll create a div to which we can bind the iframe:

Since we need to add this element to all pages that will be calling the parameter form, you should consider if you want to repeat the above step every time, or that you want to add the element to your page template.

Our next step will be the actual creation of the iframe. For this we need a dynamic action. This dynamic action needs to run when you want to start running your report, so on click of a button seems a logical choice. Make sure you set the event scope of the dynamic action to “Dynamic” to ensure you can still run the dynamic action after, let’s say a partial page refresh. The action will be a JavaScript action. The function that we are going to create will need to create an iframe with the URL of the parameter form page. We also need to pass the proper report id to the page, as the PL/SQL region will use this to evaluate what parameters need to be printed. Below function does exactly that.

By now we have a page that can append an iframe with the parameter form in it. To let it show as a modal dialog we need to add a few lines of code to the JavaScript function:

The last thing we need to do is to let the dialog close once we have run our report. This is a tricky part since we need to make sure the “close dialog” command runs after the report is opened. One way to do that is by creating a “closeIframe” function on the calling, or parent page and let the dialog call that function after the report is run. We can place the following function in our calling page under Edit Page > JavaScript > Function and Global Variable Declarations. Or again consider adding the function to your page template.

The closeIFrame function will be triggered by the parameter form page, so we need to go to that page and add some code to the dynamic action there. Place the following line of code after the window.open(url) :

That concludes the creation of the dialog popup. Following this two fold blog post you should have a full set up that lets you call a report parameter dialog from any given page. The parameters in the dialog will depend on the report id that you pass. For this you need to register your reports and their parameters, you can add them in a simple two table data model on which you can create a master-detail form. If you want to use lov’s you need to add the lov to the shared component lists of values.

Good luck!

Parameter input screen for Jasper Reports

Jasper Reports is often used as print engine in APEX applications. In this series of two blog posts I will discuss a setup to pass parameters to your Jasper report. The setup will consist of:

• a page from which the report is called,
• a parameter dialog,
• an url to the report

This current post will demonstrate how to create the parameter screen and how to call the report. The second post will explain how to turn the parameter page into a dialog that you can call from another page.

For the setup I assume you have Jasper Server installed. The same method works for JasperReports Engine as well, however the syntax of the URL is slightly different. To display the parameter dialog you’ll need jQuery and jQuery-UI, if you do not already have that (recent APEX version have both by default), you should download/install them and include the in your page template.

To create a parameter screen, Apex will have to know what report and what parameters the report expects. For this we’ll need to create a few tables and an apex page. In the tables we’ll store some information on the report file and the parameters each report takes, you can elaborate as much on this as you want, for example extend the model with roles authorization to handle the display of certain reports to specific user groups, etc. For here we’ll stick to a basic model follows:

Note that with this model we’ll also track what input type a parameter should be (date/lov/text) and -when applicable- what lov should be used for that parameter. The Apex screen to register the reports will be something in the line of a master-detail form to administrate what reports you want to provide in your application and what parameters each report has. The effort of registering all your reports in your application has an added benefit: you’ll be able to make an overview page listing all your reports. This is a nice feature for your end users.

Now that we can track what parameters a certain report has, we can look into building the parameter screen. This will be a pop up screen that displays the parameters for a certain report. For this, you’ll create a new page and in that page add a region of type ‘PL/SQL Dynamic Content’. For the region source we need a procedure that will generate the necessary parameters. When we know what report we want to run we can simply select the parameters that go with that report. For this we will create a cursor that selects the parameters from the parameter table. We can loop over that cursor, check what type of input field the parameter should be (date/lov/text) and create an input item accordingly. For example:

In this code you might notice two things:

• After the cursor for loop there is one last item added. This is a lov to select the export format of the report (‘docx/xlsx/pdf/..’).
• The cursor takes uses a parameter ‘pxx_report_id’ –where ‘xx’ should be replaced by the page number of your parameter screen. We’ll create the page item in a moment.
• - There is a hidden item generated for the report name. This will be used in a bit when we create url to run the report.

I choose to offer end users a number of output formats and let them select one. For this you need to make a lov in Apex, under Shared Components > List of Values. This also demonstrates how you should define any other parameter as lov input item: first define a lov in Apex and then use it as input parameter in ‘apex_item.select_list_from_lov’. If you have defined your parameter as lov then you need to register the lov name in the parameters table. The lov name will then be passed from the cursor to the apex_item function in the cursor for loop.

The page item ‘pxx_report_id’ is a hidden page item that is filled by the calling page when it calls the parameter input page. We’ll create the input item and also create a button ‘Get Report’, which will run the report. The button will be of type ‘Defined by Dynamic Action’.

Our dynamic action will be a JavaScript action that runs on click of the ‘Get Report’ button. Basically the function parses all parameter data after a base URL. The base URL should point to your report server. The tricky bid is of course that the number of parameters that we need to parse depends on the report that you want to run, so we’ll need a function that loops as many times as there are input items available. Our input items all have an index number (that’s what the v_inx variable in the pl/sql code is for). So we’ll just look for any input item with an index and for as many items as we find we’ll loop. In the loop we’ll look at the current input item and take the parameter name, which is stored as id, as well as the parameter value. We paste those two at the end of the URL. Once we’ve constructed the URL we can call the report with window.open(url).

By now we have a parameter page that will render parameters for any given report. It also has a button to run the report. The reports and their parameters can be registered using a master-detail page, and we can create a page of available reports. In the following post I’ll explain how you can turn the parameter page into a modal dialog and how we can actually call that dialog from another page in your application.

For now, enjoy!

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.