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

    • Features: Parameters, Reports Audience: Developer Skill: Intermediate


    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 INTAS BEGINSELECT 125 * @parameter1 AS somefieldENDGO[/code]

    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})}[/code]


      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:


        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:






    User Feedback

    Recommended Comments

    There are no comments to display.

    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now

  • Create New...