Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Monday, April 9, 2012

Swapping out the SQL Server Table in a Jasper Report

If you'd like to select different tables in a Jasper Report, use database vendor-specific techniques.  This post shows a SQL Server example of a multi-statement UDF.

If you're building a report where you'd like the table to vary, use a multi-statement UDF -- a SQL Server-specific feature -- to decide which table to use.  For example, these two tables have equivalent schemas, but hold different data.

Data from 2 Equivalent Tables
User-Defined Function
A SQL Server multi-statement UDF (User-Defined Function) can be written which will accept a parameter (in this case 'oldFlag') and return the specified table's data.  Note that this function could also normalize the column names to a common definition.

CREATE FUNCTION dbo.GetContacts (@oldFlag AS INT)
        contactId INT,
        firstName VARCHAR(50),
        lastName VARCHAR(50)
    IF @oldFlag = 1
        INSERT @cnct SELECT contactId, firstName, lastName FROM Contact_old
        INSERT @cnct SELECT contactId, firstName, lastName FROM Contact

To test this outside of Jasper Report, make the following calls:

SELECT * FROM dbo.GetContacts(0)
SELECT * FROM dbo.GetContacts(1) -- return _old records

Jasper Report

To create the Jasper Report, create a parameter 'oldFlag' which includes a default value.  The default values is used in the query editor.

Report Query Configuration
The report parameter, which will be called when the report is run, is defined like this.

Parameter Configuration
The report itself is straightforward: a few fields are dragged onto a pair of column / details bands.

Report Config
Running the Report

Running the report displays the oldFlag prompt.  Putting in 0 and 1 will toggle the result sets.

oldFlag Prompt
Although I'd prefer something more portable, many organizations never change their RDBMS vendor.  This solution is specific to SQL Server, but other RDBMS vendors also pass results sets out of functions (ORACLE REFCURSOR), so this example may be able to be replicated for those vendors.

No comments:

Post a Comment