Calling Microsoft SQL Stored Procedures from iReport

This article provides information about calling stored procedures in iReport Designer with Microsoft SQL.

Create a Stored Procedure on Micrsoft SQL

-- =============================================
-- Author: author name
-- Create date: created
-- Description: description
-- =============================================
CREATE PROCEDURE ireport @parameter1 INT
AS BEGIN
         SELECT 125 * @parameter1 AS somefield
     END
GO

This particular dummy stored procedure simply takes a parameter and multiplies it by 125. Not very useful but a good proof of concept.

Chose plsql as the query language

Create Parameters and Simply call the Stored Procedure by Name

In this case, the name of the stored procedure is 'ireport' and it takes exactly one parameter. The parameter was defined with a default value expression of "2" which simply multiplies 125 by 2.

That's it, simply use the fields in your report and the user will be prompted via parameter, the parameters might also come from LoggedinUser/role/attribute which helps tie into security models. It is also possible to save this report in the Topics folder of JasperReports server to take advantage of the Ad-Hoc engine and stored procedures.

Note: You should disable the rows() affected message by running SET NOCOUNT ON on the SQL server

It may be necessary to add an exclamation point after the $P, for example $P!{parameter1}.