dc137654 Posted June 15, 2007 Share Posted June 15, 2007 Hi, I am having strange issues with JasperServer and my SQL queries. Here is my situation:I want to use an input control that allow users to select the Quarters of the Year i.e. Q107, Q207, etc. This selection will then be used to populate my query. The issue is that the Quarter is embedded within a field (Deal ID) so I will need to use the 'LIKE' operator. Here is my query: select a.deal_id as deal_id, b.status_name as status_name, a.su_code as su_code, c.eu_name as eu_name, d.nr_5 as net_revenue, a.deal_init_date as deal_submit_datefrom dealstatus a, nextstep b, customerdata c, financialanalysis d where a.status_id = b.sub_status_id and a.deal_id = c.deal_id and a.deal_id = d.deal_id and a.status_id in (8, 16, 17, 18, 19, 21, 22) and a.deal_id like $P{quarter} order by deal_init_date ==================================I can successfully run this when I create a Parameter Prompt from within iReport (I type in %Q107%), however, when I try deploy this report to JasperServer, I get this error: org.springframework.webflow.engine.ActionExecutionException: Exception thrown executing [AnnotatedAction@2ebbbd targetAction = com.jaspersoft.jasperserver.war.action.ViewReportAction@90067, attributes = map[[empty]]] in state 'verifyData' of flow 'viewReportFlow' -- action execution attributes were 'map[[empty]]'; nested exception is org.acegisecurity.AccessDeniedException: Access is denied On JasperServer, I created a list control with the valuesNAME VALUEQ107 %Q107%Q207 %Q207%etc. When I remove the %, the report runs with no errors (though it obviously does not return any results) Any ideas? Link to comment Share on other sites More sharing options...
swood Posted June 15, 2007 Share Posted June 15, 2007 I think you are running into a HTML encoding problem with the %s. Rather than using a code like '%Q207%' in the list, what you can do is have the code be 'Q207' and in the query in the JRXML do: a.deal_id like '%$P{quarter}%' ShermanJasperSoft Link to comment Share on other sites More sharing options...
dc137654 Posted June 15, 2007 Author Share Posted June 15, 2007 wow, what a quick response! :) you are correct about the HTML encoding problem, however, your solution gives me the following error: Error filling print... Error preparing statement for executing the report query : select a.deal_id as deal_id, b.status_name as status_name, a.su_code as su_code, c.eu_name as eu_name, d.nr_5 as net_revenue, a.deal_init_date as deal_submit_date from dealstatus a, nextstep b, customerdata c, financialanalysis d where a.status_id = b.sub_status_id and a.deal_id = c.deal_id and a.deal_id = d.deal_id and a.status_id in (8, 16, 17, 18, 19, 21, 22) and a.deal_id like '%?%' order by deal_init_date which is the correct error since in a prepared statement, you must enter the ? as the entire value i.e. "... a.deal_id like ?" I fixed this by entering the HTML escape for % in the list control on JasperServer. For example, for Q307, I put %Q307% Not a really pretty solution but it worked for me. Thanks for your advice sherman! Derek Link to comment Share on other sites More sharing options...
dc137654 Posted June 15, 2007 Author Share Posted June 15, 2007 Seems like this post is having issues with the escape characters too.:pinch: what I meant to say was: For Q307, I put %Q307% and it worked Link to comment Share on other sites More sharing options...
lucianc Posted June 15, 2007 Share Posted June 15, 2007 Which JasperServer version and what browser are you using? We haven't managed to replicate this issue.. Regards,Lucian Link to comment Share on other sites More sharing options...
dc137654 Posted June 19, 2007 Author Share Posted June 19, 2007 Firefox 2.0.0.4 on ubuntu, JasperServer 1.2.1 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now