2004 IR Help Posted August 23, 2006 Share Posted August 23, 2006 By: Scott - imnotscott Newbie JSP Question 2005-07-15 08:56 I am a Java/JasperReports newbie. I apologize in advance for being a lowely newbie and not knowing what I'm doing. :-) I am working on a project that will use JasperReports in a struts enviornment. Our reporting will mostly involve "stock" reports that won't change. Meaning the columns will remain the same. What will change is the result set that the report user. What I want to do is to create a JSP file that will let the user choose some filter elements such as a date range or to only display data for just one state, etc.. Once the filter criteria is established, a query will be created and passed to the JasperReport. I have played around with the samples included with the JR download but I don't have a clear understanding of how I can pass the entire query to the JasperReport as a parameter. Can someone show me a very brief code snip of both the JRXML file and a JSP that is doing something like this? This humble newbie thanks you for any assistance you can give! Thanks, Scott(not) By: Scott - imnotscott RE: Newbie JSP Question 2005-07-16 10:06 Update: I realized that I had forgotten to close the <queryString> tag. I did that but now get a different error: "String is not SQL query". Any ideas as to what I am doing wrong? I have searched all of the demo's and samples but I can't find any example where the entire SQL statement is passed as a parameter. What am I doing wrong? By: Denny Valliant - xenden RE: Newbie JSP Question 2005-07-16 14:56 Not an answer, but you can create the query first, and then pass the query to the report, similar to the following: newquery = "SELECT * FROM someTable"; if(newquery NEQ ""){ stmt = CreateObject("java","java.sql.Statement"); stmt = connection.createStatement(); rs = CreateObject("java","java.sql.ResultSet"); rs = stmt.executeQuery(newquery); drs = CreateObject("java", "net.sf.jasperreports.engine.JRResultSetDataSource"); drs.init(rs); jasperPrint = CreateObject("java","net.sf.jasperreports.engine.JasperPrint"); try{ jasperPrint = jasperManager.fillReport(jasperReport, parameters, connection); }catch(JRException e){ e.printStackTrace(); } }else{ try{ jasperPrint = jasperManager.fillReport(jasperReport, parameters, connection); }catch(JRException e){ e.printStackTrace(); } } By: Piyush - piyushrules RE: Newbie JSP Question 2005-07-17 08:14 Why dont you try this. try to print the query that you have formed in your java class and then execute the same query directly in the database that you are using to check if your query is not malformed. This could be an issue at times. The second point, what is your syntax in the .jrxml file ? have you given $P{QueryString} or $P!{QueryString}. The latter is the syntax when you are using queries. Hope it works.... Regards, Piyush By: Scott - imnotscott RE: Newbie JSP Question 2005-07-18 03:34 "The second point, what is your syntax in the .jrxml file ? have you given $P{QueryString} or $P!{QueryString}. The latter is the syntax when you are using queries. Hope it works.... " That was it! One little "!" and it now works like a charm. Thanks! Scott (not) By: Scott - imnotscott RE: Newbie JSP Question 2005-07-15 11:53 OK, I took an attempt at this that I thought might work but does not. I took the WebApp example from the JasperReports download and modified it to use a report I created with iReports. The report works fine when I leave the query hard coded into the jrxml file, but when I try to pass it in as a param I get the following error at compile time: java.lang.NoSuchMethodException: No such accessible method: addElement() on object: net.sf.jasperreports.engine.design.JRDesignQuery at org.apache.commons.digester.Digester.createSAXException(Digester.java:2312) at org.apache.commons.digester.Digester.createSAXException(Digester.java:2332) at org.apache.commons.digester.Digester.endElement(Digester.java:1039) at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1263) at oracle.xml.parser.v2.ValidatingParser.parseRootElement(ValidatingParser.java:143) at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:278) at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:184) at org.apache.commons.digester.Digester.parse(Digester.java:1515) at net.sf.jasperreports.engine.xml.JRXmlLoader.loadXML(JRXmlLoader.java:356) at net.sf.jasperreports.engine.xml.JRXmlLoader.load(JRXmlLoader.java:340) at net.sf.jasperreports.engine.xml.JRXmlLoader.load(JRXmlLoader.java:294) at net.sf.jasperreports.engine.xml.JRXmlLoader.load(JRXmlLoader.java:278) at net.sf.jasperreports.engine.JasperCompileManager.compileReportToFile(JasperCompileManager.java:81) at _jsp._compile._jspService(compile.jsp:47) at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:57) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:347) at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:509) at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:413) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:65) at oracle.security.jazn.oc4j.JAZNFilter.doFilter(Unknown Source) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:649) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:322) at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:790) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:270) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:112) at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:192) at java.lang.Thread.run(Thread.java:534) NESTED BY : net.sf.jasperreports.engine.JRException: Error at (60, 11: No such accessible method: addElement() on object: net.sf.jasperreports.engine.design.JRDesignQuery at net.sf.jasperreports.engine.xml.JRXmlLoader.loadXML(JRXmlLoader.java:360) at net.sf.jasperreports.engine.xml.JRXmlLoader.load(JRXmlLoader.java:340) at net.sf.jasperreports.engine.xml.JRXmlLoader.load(JRXmlLoader.java:294) at net.sf.jasperreports.engine.xml.JRXmlLoader.load(JRXmlLoader.java:278) at net.sf.jasperreports.engine.JasperCompileManager.compileReportToFile(JasperCompileManager.java:81) at _jsp._compile._jspService(compile.jsp:47) at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:57) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:347) at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:509) at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:413) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:65) at oracle.security.jazn.oc4j.JAZNFilter.doFilter(Unknown Source) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:649) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:322) at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:790) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:270) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:112) at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:192) at java.lang.Thread.run(Thread.java:534) Caused by: java.lang.NoSuchMethodException: No such accessible method: addElement() on object: net.sf.jasperreports.engine.design.JRDesignQuery at org.apache.commons.digester.Digester.createSAXException(Digester.java:2312) at org.apache.commons.digester.Digester.createSAXException(Digester.java:2332) at org.apache.commons.digester.Digester.endElement(Digester.java:1039) at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1263) at oracle.xml.parser.v2.ValidatingParser.parseRootElement(ValidatingParser.java:143) at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:278) at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:184) at org.apache.commons.digester.Digester.parse(Digester.java:1515) at net.sf.jasperreports.engine.xml.JRXmlLoader.loadXML(JRXmlLoader.java:356) ... 19 more Here is the code from my JSP: File reportFile = new File(application.getRealPath("/reports/level_one_nc_portrait.jasper")); DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@MyHost:1521:database", "userID", "password"); JasperReport jasperReport = (JasperReport)JRLoader.loadObject(reportFile.getPath()); String sqlQuery = "SELECT * FROM ....."; Map parameters = new HashMap(); parameters.put("ReportTitle", "A Title"); parameters.put("BaseDir", reportFile.getParentFile()); parameters.put("sqlQuery", sqlQuery); jasperPrint = JasperFillManager.fillReport( jasperReport, parameters, conn ); Here is the portion of my jrxml file that I modified: <parameter name="sqlQuery" class="java.lang.String"> </parameter> <queryString><![CDATA[$P{sqlQuery}]]> Any help would be greatly appreciated. Thanks, Scott(not) By: Yippie - yippie RE: Newbie JSP Question 2005-08-11 13:33 Scott, Hope this is not too late, I believe this may solve your problem. Since you are trying to pass the query for your report as a parameter the parameter expression in your query needs to be " $P!{sqlQuery} " not " $P{sqlQuery} ". The difference is subtle, but the $P! (note the exclamation point) is defined as a "special syntax used in the report SQL query to indicate that the parameter does not have to be dealt with as a value to transfer a Prepared Statement, but that it represents a little piece of the query." I ran into a similar problem with a report I was working on that required me to be able to handle adding addition WHERE clause elements on the fly. The "$P!" worked wonderfuly. Hope this helps. Craig... 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