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

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.

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.

--

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).