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)
RETURNS @cnct TABLE
    (
        contactId INT,
        firstName VARCHAR(50),
        lastName VARCHAR(50)
    )
AS
BEGIN
    IF @oldFlag = 1
        INSERT @cnct SELECT contactId, firstName, lastName FROM Contact_old
    ELSE
        INSERT @cnct SELECT contactId, firstName, lastName FROM Contact
       
    RETURN
END

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

Results

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.