Friday, 10 January 2014

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!