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.
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’.
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!