JasperServer and iReport have a very useful feature: Input Controls. They can be used to ask the user for some sort of input. The user’s response can be used for almost anything, from printing pretty reports, to controlling security, or filtering data. The only problem is that users may want to enter a comma-separated list of values (CSV), which is tricky when these values must be used to filter data. However, this problem is easy to overcome by playing a bit with the report’s SQL query.In this example, we will use a string input control called InputMachineName. This will be used to filter only specific computers in a report. The assumption is that users may want to enter the machine name(s) in one of many ways:
- As a single machine name (e.g., PC1)
- As a wild card (e.g., PC* or PC%2)
- As a CSV list (e.g., PC1,PC2,PC3)
The first step is to modify the report’s query and declare a temporary NVARCHAR variable. We’ll call it @InputMachineName. We can make it any size, but 2KB should be more than enough for the largest CSV lists.
DECLARE @InputMachineName NVARCHAR(2048);
Next, we need to remove any spaces in case the user puts a space after each comma:
SET @InputMachineName = REPLACE($P{InputMachineName},' ','');
To allow the user to use common wildcards (as opposed to SQL wildcards), we can also convert * to %, and ? to _:
SET @InputMachineName = REPLACE(@InputMachineName,'*','%');\ SET @InputMachineName = REPLACE(@InputMachineName,'?','_');
The query we will use is only an example. It simply selects each computer and IP address from a workstation inventory table.
SELECT MachineName, IPAddress\ FROM Workstations
Finally, we add the magic trick to the WHERE clause. In order to support wild card and single-value searches, we use a simple LIKE statement. This will match the user’s query to a value in the database:
WHERE MachineName LIKE @InputMachineName
Next, we add a second LIKE statement but we reverse the operands. This will cause a search of the database values in the user’s query, which is the opposite of the above. To do this, however, we have to add commas and wild cards:
OR (','+@InputMachineName+',') LIKE ('%,'+ MachineName+',%')
In the end, the query would like this:
DECLARE @InputMachineName NVARCHAR(2048);
SET @InputMachineName = REPLACE($P{InputMachineName},' ',''); --remove spaces
SET @InputMachineName = REPLACE(@InputMachineName,'*','%'); --convert wild cards
SET @InputMachineName = REPLACE(@InputMachineName,'?','_'); --convert wild cards
SELECT MachineName, IPAddress
FROM Workstations
WHERE
(
MachineName LIKE @InputMachineName –-for wild card or single-values
OR (','+@InputMachineName+',') LIKE ('%,'+MachineName+',%') --for CSV
);That’s it! Users can now search for computers in a variety of ways (wild cards, CSV, single values) and JasperServer will not complain about it.