Why does $X not work with get roles and how to do it

[rteichman]

I have a report using the $X and LoggedInUSer Get Roles parameter. However I can't get it to run. The code is as follows (brackets and CDATA removed) :

parameter name="LoggedInUser" class="com.jaspersoft.jasperserver.api.metadata.user.domain.User"   /parameter
parameter name="UserRoles" class="java.util.Collection" isForPrompting="false"
        defaultValueExpression
            $P{LoggedInUser}.getRoles()
        /defaultValueExpression
/parameter
queryString
  SELECT
     DISTINCT CLIENTS."DISPLAYNAME" AS CLIENTS_DISPLAYNAME
FROM
     "DWH_MAIN"."CLIENTS" CLIENTS INNER JOIN "DWH_MAIN"."MP_CHANNELS" CHANNELS ON CLIENTS."CLIENTID" = CHANNELS."CLIENTID"
WHERE
     SCHEMA = 'MARKETPLACE' AND $X{IN,CLIENTS.DISPLAYNAME,LoggedInUser.getRoles()}
/queryString
 

Error Message

com.jaspersoft.jasperserver.api.JSException: Error filling report

Error Trace

com.jaspersoft.jasperserver.api.JSException: Error filling report at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$FillResultListener.reportFillError(EngineServiceImpl.java:1030) at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError(BaseFillHandle.java:211) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:135) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:733) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:695) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1474) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:874) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:754) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619) Caused by: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : SELECT DISTINCT CLIENTS."DISPLAYNAME" AS CLIENTS_DISPLAYNAME FROM "DWH_MAIN"."CLIENTS" CLIENTS INNER JOIN "DWH_MAIN"."CHANNELS" CHANNELS ON CLIENTS."CLIENTID" = CHANNELS."CLIENTID" WHERE SCHEMA = 'MARKETPLACE' AND CLIENTS."DISPLAYNAME" IN (?, ?, ?, ?) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:343) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:202) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:167) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1073) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1253) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) ... 9 more Caused by: java.sql.SQLException: Invalid column type at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8809) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8286) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9060) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9041) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:232) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:165) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:553) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementMultiParameter(JRJdbcQueryExecuter.java:427) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementMultiParameters(JRJdbcQueryExecuter.java:398) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:331) ... 16 moreError Message

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : SELECT DISTINCT CLIENTS."DISPLAYNAME" AS CLIENTS_DISPLAYNAME FROM "DWH_MAIN"."CLIENTS" CLIENTS INNER JOIN "DWH_MAIN"."CHANNELS" CHANNELS ON CLIENTS."CLIENTID" = CHANNELS."CLIENTID" WHERE SCHEMA = 'MARKETPLACE' AND CLIENTS."DISPLAYNAME" IN (?, ?, ?, ?)

Error Trace

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : SELECT DISTINCT CLIENTS."DISPLAYNAME" AS CLIENTS_DISPLAYNAME FROM "DWH_MAIN"."CLIENTS" CLIENTS INNER JOIN "DWH_MAIN"."CHANNELS" CHANNELS ON CLIENTS."CLIENTID" = CHANNELS."CLIENTID" WHERE SCHEMA = 'MARKETPLACE' AND CLIENTS."DISPLAYNAME" IN (?, ?, ?, ?) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:343) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:202) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:167) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1073) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1253) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:733) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:695) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1474) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:874) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:754) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619) Caused by: java.sql.SQLException: Invalid column type at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8809) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8286) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9060) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9041) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:232) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:165) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:553) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementMultiParameter(JRJdbcQueryExecuter.java:427) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementMultiParameters(JRJdbcQueryExecuter.java:398) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:331) ... 16 morError Message

java.sql.SQLException: Invalid column type

Error Trace

java.sql.SQLException: Invalid column type at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8809) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8286) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9060) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9041) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:232) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:165) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:553) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementMultiParameter(JRJdbcQueryExecuter.java:427) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementMultiParameters(JRJdbcQueryExecuter.java:398) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:331) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:202) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:167) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1073) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1253) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:733) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:165) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:695) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1474) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:874) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:754) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619)
 

 

[rteichman]

Sorry for the brevity of the question but my browser kept closing when I tried to post the code and the error message. The parameter values are being set, because in the report I can display the User Name and say role 1 as $P{UserRoles}.toArray()[1].getRoleName() It also seems to be working in the $X because the SQL shows the correct number of ? for the bind variables. However the report crashes. If I take the AND $X{IN...} part out, the Query and report runs fine. HELP! I have been trying to resolve this for a week now. I've searched and scoured the documentation (paid support) all to no avail.

Thanks

Rob

[rteichman]

Thanks to some help I got from a consultant, I have found the answer. I am posting it here to help others who may run accross this problem.  In order to use User Roles in a SQL query you MUST do the following, EXACTLY:

1. Create a parameter EXACTLY like this. The names, and format are sensitive and other variations don't work (ask me how I know):

2. In your SQL have the following within your WHERE clause replacing COLUMN_NAME with your column name from your schema (I have it as my only item so I am showing the WHERE keyword, but you can have it after an AND or OR etc.):

   where $X{IN, COLUMN_NAME, LoggedInUserRoles}

   This will work. All the syntax variations I have tried based on other examples did not work, like having a paramter:

   $P{LoggedInUser}.getRoles()