Using user attributes in input control

[jphayes]

I am attempting to create an input control that will use a user attribute to retrieve information contained in field testvalue2 for the control for a specific user contained in field testvalue under you guessed it testtable.

I created an attribute under the user named TestAttribute with values AA, BB, CC

I then created a control with the generic aaa as the name and put the following query that I have seen referenced in multiple places:

SELECT testvalue2
FROM testtable
WHERE testvalue IN ($P!{LoggedInUserAttribute_TestAttribute})

I then added the control to a report, do not care about linking to the report at this time because I just want to see the control return the values.

When I run this I get this very cryptic error that really does not tell me much

Error Message

Error executing SQL statement for: aaa. (Error UID: 31135638-dc65-4844-b3b1-afee62741ac0) (Error UID: 40bddaaf-9486-493e-a14c-73efc1915216)

Error Trace

com.jaspersoft.jasperserver.api.ErrorDescriptorException: Error executing SQL statement for: aaa. (Error UID: 31135638-dc65-4844-b3b1-afee62741ac0) at com.jaspersoft.jasperserver.remote.services.ReportExecution.getFinalReportUnitResult(ReportExecution.java:173) at com.jaspersoft.jasperserver.remote.services.impl.RunReportServiceImpl$2.run(RunReportServiceImpl.java:496) at com.jaspersoft.jasperserver.api.logging.util.LoggableExecutorService$1.run(LoggableExecutorService.java:84) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:829)

P.S.
I have validated the LoggedInUserAttribute_TestAttribute is valid as I can access it through a report I setup for testing.

[anish.rai]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[hltgdr]

Hello,

I have 1 question and 1 request.

1. In which environment are you running this query?

SELECT testvalue2
FROM testtable
WHERE testvalue IN ($P!{LoggedInUserAttribute_TestAttribute})

The query you wrote is correct, it will not give an error when you run it through ireport, but if you run it through any data programming compiler you will get an error.

2. Can you share the fields and data types of the table with us? like the example below:

CREATE TABLE suppliers
( supplier_id number(10) NOT NULL,
   supplier_name varchar2(50) NOT NULL,
   address varchar2(50),
   city varchar2(50),
   state varchar2(25),
   zip_code varchar2(10)
);

Regards,

Halit

[pjimenez_2]

Hello,

Have you tried the solution found here:

https://community.jaspersoft.com/wiki/how-use-attributes-input-control-query

I hope this helps.

-Paul