How to execute MS SQL Stored Procedure in TIBCO JasperReports® Server
Below I would like to share with you a step-by-step scenario that contains a detailed description of this process. Let's imagine that in our MS SQLServer database we have a stored procedure that could be created with the commands below:
CREATE PROCEDURE ireport @parameter1 INT AS BEGIN SELECT 125 * @parameter1 AS somefield END GO
The Stored Procedure above receives an Integer value as its input value and returns the same value multiplied by 125.
TIBCO JasperSoft® Studio / iReport Designer
To execute this procedure from iReport (or JasperSoft® Studio) the syntax that I quote below should be used:
Please pay your attention that the query language should be set to PLSQL.
Enable Stored Procedures In JasperReports® Server
Note Step 2.1 only applies to JasperReports® Server versions 6.2.x and prior, since beginning in 6.3.x the PLSQL executer has been added to the core library.
To work with stored procedures you should allow them in JasperReports® Server. Please enable Stored Procedures in JasperReports® Server to do this please open the configuration file, \jasperserver-pro\WEB-INF\classes\jasperreports.properties, in your favorite editor and add the line that I quote below to this file:
Please copy jasperreports-extensions-3.5.3.jar library to the folder, jasperserver-pro\WEB-INF\lib. The Application server should be restarted to apply the changes that have been done.
Add to your report Input Control that should pass the value to the desired parameter
Right click on your report and select Edit from the context menu.
Navigate to the Controls and Resources tab and add Input Control that should pass data to the parameter in your report and then to the Stored Procedure that should return data.
I have attached to the case:
- jrxml sample of the report
- exported version of the same report that I checked in the test environment (contains Input Control)
- jar library that contains PLSQL query executor and should be added to JasperReports® Server
Below are the screenshots that show the report
windows in JasperReports® Server web user interface: