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)
1 Answer:
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:
-
Create a parameter EXACTLY like this. The names, and format are sensitive and other variations don't work (ask me how I know):
-
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()
