GuyWithDogs

I've got a Java applet that talks to a backend server (all under my control). There's a MySQL database involved on a separate box, that is _only_ accessed by the backend server. That database has the data I wish to report on. I'm currently using JasperReports 1.3.0 for reporting functionality (and I see that many newer versions have come out since). Currently, I've got a tab in my applet UI that lets the user specify some data - usually a date or date range, and some selected items to report on. When the user clicks the "Get Report" button, that data gets sent to a servlet on the backend server, and that servlet figures out what parameters to pass, which .jasper file to use, and establishes a connection to the database server. Then it does a Code:jPrint = JasperFillManager.fillReport(sb.toString(), paramMap, conn); operation to get a JasperPrint object for the desired report. I then send that JasperPrint object back to my applet as a ByteArray[], and once back in my applet, I cast it as a JasperPrint object again and call the JasperViewer to show it to the user. So far, so good. Mostly. There are two things happening now that are problematic: Some users have WAY more data than we expected at this point - one user has a data table with 100 million rows. So the queries take a long time and the report doesn't show up. I'm working on that - it looks to be more of a query optimization (and really needs a database schema change to improve things, probably). I have a "Save" function enabled in the JasperViewer I display. Most clients want XLS or CSV because they want to put data into another database or into Excel for their own purposes, but they like to look at the neatly formatted report initially produced to make sure that the data is indeed what they were expecting. But when they save, the output isn't "useful" to them - they don't want the formatting, the titles, the wrapped cell entries, etc.[/ol] So - I'm currently leaning toward changing my reporting parameter collection to also let the user select the report format. If they choose "viewable", I'll do what I do now and put the data into a JasperViewer and disable the "Save" function (or at least remove the XLS and CSV options). If they select XLS or CSV, I'll resend the parameters, re-run the query, and select a different .jasper file that doesn't have all the headers, etc. and simply saves the data in the desired format (and I may end up with more questions about doing that effectively later). I'd toyed with the idea of reworking the servlet to actually run the query I need and sending the data back to the applet, and letting the JasperReport stuff happen at the user's machine, but that gets into a lot of tricky areas that I really don't want to deal with (pushing .jasper files out, dealing with resource limits and lack of files at the user side, etc.) Can anyone suggest any other options for dealing with getting the output saved in a different format and layout? Are there options in some of the export*() functions that I'm overlooking? Are there new features in the 2.0.3 release that I haven't found yet? Thanks.

jmurray wrote: First rule is to make 100% certain that the edges of all your elements line up exactly in the vertical and horizontal planes. Here is the JasperReports guide to creating Excel/CSV friendly output: http://jasperforge.org/sf/wiki/do/viewPage/projects.jasperreports/wiki/FAQ9 Thanks for the pointer. I looked at that - seems like Excel is a bit tricky. I'll have to look into how to suppress the various pieces when Excel is chosen - I think I saw something about that, just can't remember where. Sadly, I'm doing this stuff on a "hit and miss" basis - I don't really have time to experiment and play to learn the ins and outs, because I'm doing some other stuff for a different part of the project (in Delphi -- I'm not really a Java programmer yet). Makes sorting out the issues with a product tougher, since I'm never sure if it's an issue with the programmer...

Is there some place I can find out the tips and tricks to deal with exporting to Excel properly from within JasperViewer? I've used iReports 1.3.1 to design a report. It looks good, exports fine to PDF, and then I tried Excel. I lost 2 columns (the rightmost) of data, and my column headers didn't show up. I did some searches on these forums, and it sounds like there are a number of people having issues with Excel exports. In my case, I'm pretty sure that some of the columns are defined in the database to be fairly wide, but having short strings in them (e.g. a VARCHAR(128) field for "Pet Name" in the database, but the common entries are things like "Kitty" and "Rover") Does that cause some issues with Excel export? If so, is there something I can configure to tell the report creation routines to be a bit more clever about how the output is constructed? Thanks.

Is there a way of telling JasperViewer to use the JExcelApiExporter rather than the JRXlsExporter? From what I've read in this forum, and in the book "JasperReports for Java Developers", the JExcelApiExporter is preferred, but it seems that the more backwards-compatible version is selected in JasperViewer.

jmurray wrote: You can do this in mySQL using the DATE_FORMAT and SEC_TO_TIME functions. To convert you millisecond values to a time value you would use SEC_TO_TIME(yourmillisecondvalue/1000). That gives you a date/time value. You can now format the output as string using DATE_FORMAT around the outside of that conversion, like this: select CONCAT(TIME_FORMAT(SEC_TO_TIME(yourmillisecondvalue/1000),'%H:%i:%s'), ".", yourmillisecondvalue % 1000) as ACTTIME, otherfield, otherfield, from .... .<br><br>Post edited by: jmurray, at: 2007/02/28 01:56 Thanks for that. A couple of new functions I'd not seen before. There's a bit of an issue with the formatting of the milliseconds - if the remainder is less than 100, like 76, then the value that gets appended is .76 instead of .076. I'll look at the methods to format the value. This looks great. Thanks again.

I'm trying to format a computed duration (in milliseconds) into an hh:mm:ss.zzz appearance in a report. The computed value comes from doing a calculation like this: Code:select machine, date(rundate), sum(acttime) from datatable group by machine, date(rundate) I end up with a set of information like Machine1 12/02/07 34565.0 Machine2 12/02/07 3523525.0 Machine1 13/02/07 3623423.0 Machine2 13/02/07 etc... The SUM() comes back as a double - sort of surprised me, but it's been a while since I've messed around with aggregate functions. Anyway, I've tried Google and various MySQL forums for a way of converting the millisecond value using the database functions. Got nothing but partial successes. It's possible, because of the "slop" in the timing of the various entries that get added for a given day, that there could be more than 24 hours (not by much but we've seen as much as a couple of minutes, and we can live with that). I could figure the value out by doing some sort of calculation - I'm not sure where'd I'd put that in iReport, however. Does anyone have an example, or can point me to a MySQL formatting routine, or to a tutorial somewhere that can help me with this formatting issue? Thanks.

I'll update my own question with what I got working. I'm not sure if it's the best way, but it is working, so the people who wanted this are happy (for now). I ended up making sure that the following jar files got pushed to the machine that loads the applet: jasperreports-1.3.0.jar jasperreports-1.3.0-applet.jar poi*.jar (some version of poi) commons-collections-*.jar (some version of commons-collections)[/ul] With all that, I was able to get a JasperViewer up that showed the report that I serialized (as a JasperPrint) back from my web application, and I can save it in a number of formats that make people happy. Like I said - I'm not sure this is the best, preferred, or even a good way. However, I got it to work. One task off the list, lots more to go...

I'm trying to understand how to set up some default values to support an optional date range on a query. So I've got a column that's a java.sql.Date called datesubmitted. What I'm trying to set up is a query that does something like Code:select * from datatable where datesubmitted between $P(RANGESTARTDATE} and $P{RANGEENDDATE} order by datesubmitted I've got two parameters set up as java.sql.Timestamp values - RANGESTARTDATE and RANGEENDDATE. I'm trying to set up a default so that RANGESTARTDATE is "beginning of time" and RANGEENDDATE is "end of time". I tried Code:[code]new java.sql.Timestamp(0) and new java.sql.Timestamp(Long.MAX_VALUE) respectively. However, if I run the report and indicate "Use default" (through iReport 1.3.0), I get "document has no pages". If I use the prompts in iReport to select 2 dates, I get the correct set of results. What's the incantation to create the start and end dates I need to essentially select all the records if I don't pass in the parameter values? Thanks.

I've got an applet that talks to a web server and gets a report done. That report gets serialized and sent back to my applet, which deserializes it back to a JasperPrint object and displays it via Code:JasperViewer.viewReport(jPrint)where jPrint is my JasperPrint object. Works OK so far, and I can save the report in any format except XLS. When I try the single sheet XLS save, I get the following error: Code:[code]Exception in thread "AWT-EventQueue-1" java.lang.NoClassDefFoundError at net.sf.jasperreports.view.save.JRSingleSheetXlsSaveContributor.save(JRSingleSheetXlsSaveContributor.java:107) at net.sf.jasperreports.view.JRViewer.btnSaveActionPerformed(JRViewer.java:1080) at net.sf.jasperreports.view.JRViewer$2.actionPerformed(JRViewer.java:629) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849) at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236) at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:231) at java.awt.Component.processMouseEvent(Component.java:5488) at javax.swing.JComponent.processMouseEvent(JComponent.java:3126) at java.awt.Component.processEvent(Component.java:5253) at java.awt.Container.processEvent(Container.java:1966) at java.awt.Component.dispatchEventImpl(Component.java:3955) at java.awt.Container.dispatchEventImpl(Container.java:2024) at java.awt.Component.dispatchEvent(Component.java:3803) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822) at java.awt.Container.dispatchEventImpl(Container.java:2010) at java.awt.Window.dispatchEventImpl(Window.java:1778) at java.awt.Component.dispatchEvent(Component.java:3803) at java.awt.EventQueue.dispatchEvent(EventQueue.java:463) at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149) at java.awt.EventDispatchThread.run(EventDispatchThread.java:110) If I try the multi-sheet save, I get Code:[code]Exception in thread "AWT-EventQueue-1" java.lang.NoClassDefFoundError at net.sf.jasperreports.view.save.JRMultipleSheetsXlsSaveContributor.save(JRMultipleSheetsXlsSaveContributor.java:107) at net.sf.jasperreports.view.JRViewer.btnSaveActionPerformed(JRViewer.java:1080) at net.sf.jasperreports.view.JRViewer$2.actionPerformed(JRViewer.java:629) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849) at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236) at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:231) at java.awt.Component.processMouseEvent(Component.java:5488) at javax.swing.JComponent.processMouseEvent(JComponent.java:3126) at java.awt.Component.processEvent(Component.java:5253) at java.awt.Container.processEvent(Container.java:1966) at java.awt.Component.dispatchEventImpl(Component.java:3955) at java.awt.Container.dispatchEventImpl(Container.java:2024) at java.awt.Component.dispatchEvent(Component.java:3803) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822) at java.awt.Container.dispatchEventImpl(Container.java:2010) at java.awt.Window.dispatchEventImpl(Window.java:1778) at java.awt.Component.dispatchEvent(Component.java:3803) at java.awt.EventQueue.dispatchEvent(EventQueue.java:463) at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149) at java.awt.EventDispatchThread.run(EventDispatchThread.java:110) Am I missing some sort of extra jar file or something? If I try clicking on the errors in Eclipse (where I'm debugging from), it looks like I have the jar files I need avaiable. Some hint appreciated. Thanks.

I'm trying to come up to speed on JasperReports in a "learning by doing" manner - I'm trying to replace a different reporting tool with JasperReports, but I'm hitting some brick walls in my understanding. I'm trying some of the examples, and hitting some web sites that have some of the examples, but when I try and look at the webapp example and run the embedded viewer, i get Hello Swing Applet -------------------------------------------------------------------------------- Note: Because the preceding applet uses the Swing 1.1 API, it requires Java Plug-in 1.1.2 or Java Plug-in 1.2. It won't work with Java Plug-in 1.1.1 or uncustomized 1.1 browsers. For more information about running applets see the Java Tutorial. Is there an example that works with newer browsers/JREs? What I'm facing is I already have an applet that needs to invoke a reporting function on our server, passing in some parameters via an XML-RPC invocation. I'd like the resulting report to end up displayed in the JasperViewer, and be able to invoke the various "Export to" functions. Only I'm not grasping parts of this. I've run iReport and prepared a .jrxml and .jasper file to use. I can run the report locally through iReport against a local MySQL database. I'm prepping my applet to send the required parameters to a new call on the server, and then I've got some idea of how to get something going on the server to find a .jasper file and fill it, given a database connection, but I'm not at all sure how to pass back the resulting completed report to be viewed at the user's browser. Is there a sample that sort of covers this?

I'm a total newbie, but the common solution (from what I've seen in reading thru the forum looking for answers to my own date questions) is to try replacing the Code:$P{} with Code:[code]$P!{} It's worth a shot...