How to execute MS SQL Stored Procedure in TIBCO JasperReports®​​​​​​​ Server

Question:

How to execute MS SQL Stored Procedure in TIBCO JasperReports^® Server

Answer:

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.

1. TIBCO JasperSoft^® Studio / iReport Designer

   To execute this procedure from iReport (or JasperSoft^® Studio) the syntax that I quote below should be used:

   {call ireport($P{parameter1})}
   

   

   Please pay your attention that the query language should be set to PLSQL.

2. JasperReports^® Server

   1. 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-proWEB-INFclassesjasperreports.properties, in your favorite editor and add the line that I quote below to this file:

      net.sf.jasperreports.query.executer.factory.plsql=com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory

      Please copy jasperreports-extensions-3.5.3.jar library to the folder, jasperserver-proWEB-INFlib.  The Application server should be restarted to apply the changes that have been done.

   2. 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:

jasperreports-extensions-3.5.3.jar

case_63455.zip

report1_0.jrxml