How to use Multi-Select with iReport with Stored Procedure in Report Query?

1
votes

I'm using iReport 5.0 attempting to develop reports for SQL Server 2008R2 back end.

Enabling the user to mix/match selections from a list (multi-select) is important. The multi-select works well if the query in the iReport is SQL or has a view as source -- e.g. http://community.jaspersoft.com/questions/529890/how-do-multi-select-ireport-36

In this case, the WHERE $X{IN,<colname>,<iReport parameter>} works fine to enable muti-select: http://community.jaspersoft.com/questions/519185/using-x-query-parameter-type-not-supporte

However, I'd like to pass all the inputs to an SQL Server stored procedure in the iReport Query. In this case, the single valued inputs all work fine -- but I have been unable to figure out how to use muti-select -- the $X{} does not seem to work and otherwise -- since the iReport multi-select parameter must be defined as java.util.Collection -- I get the "Parameter type not supported in query".

Can anyone point out what I am missing, or suggest a workaround?

1
stored-proceduresjasper-reportsireportmulti-select

1 Answers

1
votes

OK -- I have come a bit further on this and can show what worked for me: To get array SP parameters to work (ie query using a WHERE IN() clause where all the comma-separated elements of the input parameter go into the IN()):

1 – Removed names of parameters from call in iReport – possibly confusing the issue …

2 – Make iReport PARAMETER: CLINSTAGEGROUP_MULTI Type: java.util.Collection

3 – On the Jasperserver at: /usr/local/jasperreports-server-cp-4.5.0/apache-tomcat/webapps/jasperserver/WEB-INF/classes/esapi/security-config.properties Turned off security.validation.sql.on :

security.validation.sql.on=false

This gets rid of 6626 error when install & run the iReport with the array/collection parameter.

4 – Download & install fn_Split() on the SQL Server: http://sqlmag.com/stored-procedures/treat-yourself-fnsplit

5 – Install iReport .jrxml file on Jasperserver

6 – Create Jasperserver INPUT objects – a query to get the list of clinstagegroup (s), and a INPUT CONTROL of type Multi-Select Query (with parameter name set to CLINSTAGEGROUP_MULTI – so that it recognizes the iReport parameter of the same name).

7 – Set up stored procedure on SQL Server: The CLINSTAGEGROUP_MULTI parameter is passed into the SQL Server stored procedure and becomes @ClinStageGroup_Multi varchar(4000)

Example query to return resultset to iReport:

SELECT a,b,c, ... n

FROM VW_SpecimenAccessions

WHERE ClinstageGroup

IN(SELECT [value] FROM dbo.fn_split(@ClinStageGroup_Multi, ','))

8 – Can now switch the clinstagegroup (multi)selections on the jasperserver report and bring back appropriate resultsets on the screen.

9 – Over that hurdle – now on to the next …