Reporting Functionality for Web Applications?

Geoffrey Wiseman asks: "I'm considering my options for reporting functionality in a web application. Part of the problem stems from the fact that I'm not aware of a good solution for reporting on any web platform. It may well exist, but I haven't seen one yet. So let's start with the general questions first: I need the ability to build reports from data. The formatting has to be pretty solid, including the ability to make use of appropriate fonts and page breaks. Further, I'll likely need to do some batch reporting, so it has to be able to generate, say, a few hundred pages of reports, with formatting, in a reasonable time frame. That makes HTML and browser printing a little ... underpowered."

"It's not vital that the reports be printed from the client machine (as opposed to the servers), but that would be preferable, for flexibility. At the very least, the person initiating the reporting should be able to choose an appropriate network printer on the server's network, but ideally a client network printer would be better.

Of course it would be nice if the reports were relatively easy to build; I would prefer not to have to write a ton of source to gather and format data. I'd prefer not to have to write any, really. The kind of drag-and-drop report builders you get with something like Microsoft Access would be nice, although it's not a requirement.

First of all, what are the thoughts and solutions on this general problem space? What tools can I make use of to really do this kind of thing well in a web application space.

Secondly, considering that I'd prefer to make use of J2EE for the project, what are some of my options for integration with J2EE. It would be nice if the reporting software could integrate with the object model rather than the database, so that I could re-use business logic. Not necessarily critical, but definitely a nice-to-have feature."

Comments

Ultimate Solution

[Anonymous Coward]

Please read this post in Pakistani accent.

First of all, all of the solutions being mentioned here are useless if you need to genrate more than a few hundred pages per report. Apache FOP for instance explodes at 60-100 pages depending on content (Any bad xml/xsl transform tool will do that to you, its called running out of heap by way of using xsl on a huge xml document). If money is no object, I suggest you go for Actuate (http://www.actuate.com/) They have the best reporting server solution out there, it allows complete formatting control with a GUI builder, support for a VBScript like language in addition to Java and extremely neat control over data sources. From my experience extremely scalable too. I generate over 3 gigabytes of PDF reports every night for hundreds of clients in addition to an interactive report generation system that creates PDF output on the fly.

If money is an issue (what!! you cant afford a $80K/Processor 8 Processor recommended solution? what are you working for? a .gone?), you need to stick to your code monkeying skills, PDF format is documented very nicely by Adobe, its pretty straight forward. Write a C/C++ deamon that listens for messages on a queuing server and generates PDF files on the fly based on message queue parameters, records its activity in a table and listens to the queue again. This works for both interactive and batch mode operations, your servlets or cgi or php or perl scripts will look up the database for finsihed reports and their PDF locations on the file server, stream it over to the browser as is. As for queuing solution you can use MSMQ, MQSeries or even a home grown database base queueing engine which won't be asynchronous but will do the job if you create a thread that checks a queue table in a dbms every so often and does the trick.

Hope I helped you solve the problem
If not, give me a shout at optimizer@hotpotatoesmail.com and I will be happy to assist you.
You will need to be able to wire money to Pakistan :) To email me, remove potatoes from my address.

Faraz Babar

Actuate

[Jeffrey Baker]

Sounds like you want Actuate e.Reporting Suite. Hopefully in the future you will realize that 1 second spent with Google is equivalent to 1000 Ask Slashdot posts.

Anyway my personal opinion is that HTML is wrong wrong wrong for this stuff. Generate TeX output and convert to postscript, or *roff, or generate postscript directly. If the target is dead trees you should use dead-tree-era technology.

PHP can massage your data and output to either or.

[crovira]

A php script can read files on the server (or submitted by the client,) massage them into the appropriate format.

If you use XML you can really go to town on the data, and ignore what you don't need, generate a PDF (or HTML with XSLT but that's less transportable and controling page formatting is more explicit [you'll have to do that work yourself.])

The client web browser can then load the HTML or the PDF and use Acrobat to view & print to their heart's content.

A step at a time...

[James Ojaste]

There are a few solutions out there for generating web-viewable reports but I don't particularly like them (Crystal reports has caused a number of headaches around here in particular).

You need fonts and page breaks? Just about anything other than raw text can do that (yes, even HTML). I use HTML/CSS for all my reports (ranging from crosstabs to listings to canned documents) - how you generate it is wide open.

Hundreds of pages worth of reports at once? If your reports are that large, you probably don't even want to deliver it to a browser. Some sort of server-side report queue would seem to be in order (ie the user requests a report and if they need to download a copy they come back and pick it up later when it's done). If you're printing from the server, just pick and choose your tech.

Drag-and-drop? Well, I far prefer building reports in HTML than in something like Crystal (which is what Access uses, by the way) - I hate mucking around with field alignments/sizes etc. I find that things go a lot quicker for me with HTML (CSS helps tremendously).

I'd really recommend reviewing your requirements. Is *anybody* really going to read hundreds of pages of reports? Perhaps you should be building a more flexible query tool so that your users can get the specific data they want without having to wade through mammoth wads of paper?

reportlab

[platypus]

open source and very nice
www.reportlab.com
Check the demos.
Really, it's cool

Reporting with Crystal, PDF's, and GB's of data

[Brento]

I've got a group in-house that does almost exactly what you're talking about. There's a lot of people on here spewing off about Crystal and PDF's, so let me give you more details on what worked for us.

We have a few large (2-5gb) databases that we have to report on. Most reports are generated on a periodic basis, but we've got a few that have to be done on-the-fly when the user requests them. The formatting has to be flawless, and we print out reports on color lasers as well as make them available for download.

The solution (picked long before I came on board) was Crystal Reports. Whether you like Windows-based development tools or not, you have to admit that it's easier to hire a Crystal person off the street than it is to hire & train for any other report writer.

To give you some idea, we have a dedicated Crystal person on staff, and several machines that churn out Crystal Reports full time. (We keep five Tektronix color lasers busy for most of the day.)

The same Crystal Report files are used whether the report is going online or being printed. In fact, we print to PDF format, and save the PDF's. Before you get one of our report packets in the mail (or FedEx or whatever), you can view the same report online in your web browser, and it prints out perfectly. Plus, there's no CPU/database load on our servers - each report is run once, and stored on hard drive.

For the reports that are done on-the-fly, we use the Crystal Reports viewers. There's several, a Netscape plugin, ActiveX plugin, and a Java plugin. You have to redirect your web users to the right page for your browser, of course. But for these on-the-fly reports, PDF isn't involved. They can print using the Crystal plugin, and page breaks and everything work fine.

I understand why a lot of people on here throw out solutions like XML, but my experience has been that spending more money on the famously well-used tool usually means spending a little less money on the hiring end later. When this becomes a big business (and aren't you planning for that?) you want to be able to hire quickly, and nothing's more widely used than Crystal Reports.

If you've got any questions about the setup, you can e-mail me.

Re:Long printed reports are obsolete, deal with it

[Spasemunki]

First of all, this seems to dodge the question. I think we can rely on the poster to know his needs. Secondly, paper reports are obsolete only if you like having your eyes cross. The current state of the industry in data display technology means that most folks have monitors that are ill-suited for long term reading. Even if you have a nice setup with a laptop that you can move around, there still isn't a lot done to prevent 'screen eyes'. People blink less when they use computer screens, and have less control of the distance between them and the screen. The result is that reading long sections can be quite dreadful. For hard contact wearers, there's nothing quite like blinking after your eyes have dried out, thus scraping the contact along your dusty eyeball.

So until there are some better, lighter, and more eye-friendly solutions to reading through computers, paper will still have a place in and out of the workplace.

"Sweet creeping zombie Jesus!"

Hi Slashdot, can anyone do my job for me?

[Chairboy]

Hi slashdot! My boss has asked me to write an extension to our defect tracking system in Perl. Please e-mail me the code for this before 5 PM pst so I can make it home in time to catch The Simpsons.

Please, no confusing external libraries other than CGI.pm or Win32 ODBC. Oh, I heard that printf is gay, so please don't use them.

Thank you!

reporting

[The_Messenger]

While obviously your situation may preclude it, I've always found Perl's built-in formatting capability to be incredibly easy to use, and it also performs nicely. It's so nice that I've often gone through the trouble of adding a Perl reporter to my C++/Java/et cetera applications. They don't call it "Practical Extraction and Reporting Language" for nothing. (Then again, they don't call it "Pathologically Eclectic Rubbish Lister" for nothing, either. ;-)

Regardless of the program you use, try to store the data in XML format. Why? Because then you can use one XSLT for conversion to HTML for web use, another XSLT for conversion to PostScript for printing, another XSLT for conversion to Excel spreadsheets -- you get the idea. While I hate to say so on this site, SQL Server 2000 offers some particularly nice functionality that can be used to implement this -- such as automatic transformation of tables to XML documents.

If you require graphics as well as text, check out the gd graphics library. The Webalizer is an absolutely delicious example of how gd can be used to create slick PDF graphs on the fly.

You mention that you'd like to integrate with J2EE... I'm somewhat of a Java guru and can say without wavering that Java is not a first-choice solution for text-based reporting. If your reports are being generated by a Perl or PL/SQL script and you're just outputting the results from Java, it's fine ;), but text processing and transformation isn't too hot in the standard Java APIs. Now if you want to pay for a third-party API, you may be able to get around this...

For graphical reporting, however, Java is one of the best solutions. There are a plethora of Java charting tools available, although the decent ones will cost some dough...

Anyhoo, if you provide some more details on your specific task I can give a better recommendation.

--

Use VB

[Pinball Wizard]

I'll be tarred and feathered for saying this, but its a great option for what you are trying to do.

First, with VB, you can use ADO to do all the database work. An ADO recordset has support for paging, so you can easily separate your report into pages. It has an easy to use Printer object(which seems to be there by default, I didn't have to declare an object). At the end of each page, all you have to do is call Printer.Print and Printer.NewPage. At the end of your document, call Printer.EndDoc. Presto. A paginated database report, with no external(read: $$$) component. You can further refine your report by setting fonts, etc, but the above outlines the basics.

Another cool thing about VB is that you can drag a WebBrowser control onto your form to make an instant browser. You can then host your web based app in this form. The only reason I recommend doing the printing with VB instead of the web based app is that you generally have to display everything you print if you do it all on the web page, whereas VB you can print things without necessarily displaying them.

I'm hoping Mozilla has the same functionality, as I've done this, with the majority of the application logic in the (cross-platform) web app, using VB only to display the application and to print. If Mozilla has the same functionality, I'll have a powerful application that is cross-platform and updateable without needing to send clients install programs.

Not Difficult

[Tairan]

I can name three right now that can do what you want - Formscape , Monarch Report Writer , and Crystal Reports. They've been around for a while.

I'm surprised something like this got through. Its kinda like asking 'I'm looking for a way to share files between my multiple machines, and I don't want to use floppy disks any more."

Missing something?

[Alien54]

I must be missing something. I would have thought that via perl, or what every that you could generate whatever reports you needed from a data base to your web browser. Once in your browser you could print out as needed. But this requires coding, which you have said you are not partial to.

Now there are a number of products where you could generate a report for a database with HTML tags inserted in the correct local, then print to a text file. snd a separate one for printing. This essentially generates HTML pages for you as you need. All it becomes is a specialized report, that can be uploaded as needed.

So the best option is to have a report that is generated for web display, and have a second one for download in PDF or whatever for printing.

Let's face it, depending on the database, custom reports etc have been where a lot of database analysts and programmers have made the big bucks for a very long time. While you can get away wit simple reports in something like ms access, in the long run your are going to have to get someone who has the knowledge and experience to put it together right.

When I used to do tech support for a consumer database company, I ran into this all the time - Customers wanting to do sometimes complicated things with only a minute or two of effort. Add in some anomolies because the database was not normalised correctly, and you get a bloody mess. It was not so much the system, as it was getting the query right (Alphabetized by state, then town, then family name, and filtering those custers using master card for purchase over 250$ during the month of december, and who still owe us money as of the end of the preceding month)

Depending on the setup, the query could be trivial or a nuisance.

In some cases, you can't get there from here.

Check out the Vinny the Vampire comic strip

Ooh! Ooh! I know.

[tenzig_112]

Use PHP to generate an .rtf file with the pagebreak tags therein. Specs are available on-line. On your browser, use the link save-as feature to save the file as an .rtf and open it in WP or some other word processor. Score! I finally found a question I could answer!

PDF does what you want

[commander salamander]

Try PDFLib ( http://www.pdflib.com/ ). I've used it for generating reports from both Perl and PHP web apps, and it's worked great. You can allow users to set fonts, max. # of pages, etc etc.

According to their site, it supports:
ActiveX/COM for use with Visual Basic, Active Server Pages, Allaire ColdFusion, Borland Delphi etc.
ANSI C
Class wrapper for ANSI C++
Java (via Java Native Interface, JNI), including servlets
Perl
PHP hypertext processor
Python
Tcl

I think one of those languages should suit your needs.

With large, hundred-plus page reports, generating PDFs can take a while (and a large chunk of your server CPU) so you will probably want to cache the generated PDFs and serve them up statically for a few hours...of course, it depends on how often the data gets updated in your application.

Crap replies

[HardYakka]

A lot of the people replying to this question are giving advice worth what it costs: nothing. Anyone can list the google search results for web reporting tools, but most of these aren't worth a crap if your requirements are for large loads. Crystal Reports: Great design tool. Useful in an office environment. Requires a dedicated server. Falls on its face under large loads. Formscape: Not useful for dynamically generated reports. All other report engines : one of the above HTML/text output: As mentioned, looks like crap printed. Real reporting requires grouping/subreports and even graphs. Have fun getting these to print. My company required a web-reporting tool for use by tens of thousands of users nationwide via TCP/IP connections. This means being able to handle hundreds of simultaneous dynamically generated reports. First, we tried a few of the products on the market but even the ones costing >$1,000,000 couldn't handle the load without a giant server farm. Next we tried a "roll your own" servlet/PDF solution. Nice idea, but if you are trying to do this on your real time app servers, system response times will suffer. The solution we settled on was: 1)custom report programs on a mid range server for each report type. 2) all report requests are submitted and processed by a batch manager which limits CPU load on the report server 3) finished reports are converted into PDF via a template file and stored in a table. 4) The users is directed to a pending reports page until the reports are complete. When complete, they can be downloaded as .pdf and viewed with the acrobat plug in The lesson we learned - if you need to handle complex reports and heavy loads, you must custom code them. No reporting engine can handle it (yet).

Re:CrystalReports???

[EastCoastSurfer]

I would avoid Crystal for web reporting. The gui report builder is nice, and the reports work fairly well, but the web server part sucks. My biggest problem with the Crystal Report Web Server is that when one user is pulling a report all users block until that first user is done. The marketing people at Crystal actually comped us a copy of version 8 because of this problem(and the fact that they lied and said that it would not occur like that). The tech support guys are a joke. They were amazed that I got the web reports to work at all with our volume of data(100+ page reports). I have been looking for other solutions, and maybe some comments here can point in the right direction.

Contrary Opinion of Crystal Reports

[tb3]

This may get modded as flame-bait, but what the hell;
Avoid Crystal Reports at all costs if you can. It's irrating, confusing, and buggy, and it's been that way since I started using it in 1993. On every one job I take I swear I won't use Crystal again, but somehow I keep getting stuck with it, becuase it's already being used, or somebody somewhere thought it was a good idea. If you need formatting and page breaks go with some kind of PDF generator, and find one with a Java interface because you're already on J2EE.
Report generation is one of the necessary evils of business application development, so the best thing you can do is find something that will let the users create their own reports; instead of pestering you to do it for them.
End of grumpy report creator rant.
-----------------