Slashdot Mirror
SQL Report Writers For Unix?
Chris27183 asks: "I'm currently using MySQL with PHP on Linux, and love it very much. I'm very glad to do away with Access in favor of a database based in Unix. However, there is one stipulation. At first glance, there does not appear to be a free report writer (or even a sql to latex translator) like that of access. I'm just trying to do simple things like mailing labels and tabular lists, and it would be a great help to not have to reimport data into access to perform these seemingly simple tasks."
Why not keep the data in mysql, but use Access to generate the reports from it using ODBC?
The pathologically eclectic rubbish lister will list your rubbish easily, especially with formats and DBI. Evaling formats from a separate file will help those for whom even the simplest of programming blows their minds.
This won't help now, but check out www.gnue.org. They're planning exactly that, eventually, among other things. Looks like a cool project.
PHP is usually quick enough that writing the "report" in PHP isn't too painful. And using tables and mime types, you can have it load directly into excel where you can process it further.
Now if you have real reporting to do, this may not work, but I've found that a few hours of coding can solve those problems... albeit not as well as the 10 minute job that Access would take.
It's a tradeoff, with Open Source, you get great programmer resources, moderate user resources, and minimal business resources. With closed source, you normally get minimal (or no) programmer resources, moderate user resources (varies by company, most are better than open source, but some are atrocious), and good-great business resources.
I believe that in school we called it, an engineering tradeoff.
Seriously though, you may want to look at some other packages for this task. I love PHP, but if you need lots of reporting and not coding a PHP script for each report, you may want to switch your system to MS SQL and Access as a client.
YMMV,
Alex
Good luck.
As long as you have an ODBC driver for MySQL on a Windows machine, just go ahead and buy Crystal Reports. Far, far better than Access.
Please consider suicide rather than subjecting yourself to the utter trash that is CR. I swear to God, it is the WORST software of ANY kind I have EVER used.
Part of problem was that it kept crashing on us (literally at least once an hour on 4 different machines, often as much as once every 10 minutes). I can't entirely blame CR for this, part of it was the ODBC drivers. But still.
The rest of the problem is the lameness of the software itself. User-defined fields are FAR weaker than they are in Access. The formatting was nearly impossible to get right (or keep right). The UI is nice in some spots but just plain weird in others (why aren't fields listed in some kind of ORDER??).
At least promise me this: Try an evaluation version of the software before buying it. And when you do a sample report, make it act and work EXACTLY like you will when you "go live". With Crystal, making a "quick and dirty" report is pretty easy. Making a known accurate and correctly-formatted report is a nightmare.
CR is probably just barely usable for a small company, provide you put ALL of your user-defined/calculated fields in the DB itself.
--
MailOne
Non-meta-modded "Overrated" mods are killing Slashdot
(Hey Ryan! Here's your proof!)
First of all, you should be able to set Access to "pass-through" so it doesn't care about your query syntax at all. (warning: it's been over a year since I did any report writing, used Access or wrote any SQL).
Second of all, why use "if()"? Create a defined field on the DB OR Access end and do the work there.
--
MailOne
Non-meta-modded "Overrated" mods are killing Slashdot
(Hey Ryan! Here's your proof!)
You are exactly right about the multiple datasource problems and lack of scheduling. I never tried to use Oracle funcs (although I was about to before I quit that job) so I can't speak to that. Never used the web server or the multiple versions either.
We even resorted to creating our own scheduler in VB using the CR VBX/OCX. Pretty ugly, but we eventually did it.
--
MailOne
Non-meta-modded "Overrated" mods are killing Slashdot
(Hey Ryan! Here's your proof!)
I'm not sure exactly what you're using Access for that you can't do with mySQL and PHP. There are really only two tasks:
(1) Write the SQL
(2) Format the report
If writing the SQL for your queries is the problem area then the best recommendation I'd have is to learn SQL better. Access sure provides a cute interface for graphically creating queries, but sometimes it writes pretty bad SQL to do that. I'd search Freshmeat and Source Forge for graphical SQL tools, but that doesn't excempt the need to learn SQL.
If the formating of SQL results is the issue, I'd suggest just using the tool you already have - PHP. Why not output the data in HTML and print your reports from the web? Isn't that what everyone wants now-a-days anyway -- web based reporting tools.
The original poster's mention of LaTeX would seem to indicate interest in printed reports, which HTML was not designed for. For example, HTML does not allow for having column headers repeat at the top of every printed page, but the LaTeX supertabular environment does.
At my company I'm doing printable database-driven reports using BRL and pdflatex. If anyone wants help doing it the same way, let me know.
Jonathan Moran
Oink, Oink!!
You could always use something like Brio Technology's(formerly SQRIBE) SQR Language. It's cross platform (Windows, Unix, VMS, MVS, etc.) can use a variety of databases (Oracle, Sybase, DB2, Informix, etc.). If you ever had to deal with PeopleSoft reports, chances are they were generated using SQR (Crystal Reports works ok too, but like other have said, it sucks).
Here's a web page that has some SQR resources: http://www.ontko.com/sqr/.
- Pass the MIME type "application/vnd.ms-excel" to the browser. IE will open Excel in-case, Netscape should allow you to launch Excel.
- Leave out the HTML, BODY, HEAD and other tags. Just do a straight table of data.
- You can include a header, etc. above the table by putting in some regular text, experiment a bit.
- Standard HTML formatting will be used by Excel. If memory serves, this includes bold, italic, cellspan, width, height, font, color, background color (ie cell color), etc.
- You can use standard Excel formulas like SUM, etc. You do, however, have to use the standard Excel cell names (A1, B1, A2, etc).
This is a bit from memory. I've done it in ASP for a couple of different projects and there are examples on the web of this here and here. The formatting information is from my own experience and from memory (from a project about a year ago that I no longer have code for).The advantages to doing it this way: good printing, most users have Excel, not much coding, just quick PHP and SQL calls with a bit of HTML formatting, no extra software. The disadvantages: requires Excel, no simple report writer, etc.
Well, that's it. I hope it helps someone.
Portable versions of Firefox, GIMP, LibreOffice, etc
For just printing adress labels and tab-separated lists, you should be able to cook up a perl script using the DBI pretty quickly. A little perl can go a long way, and is a good thing to learn if you're the kind of person who wants to run a linux server for business applications.
for more complicated tasks, you can get the specs for PostScript for free off of the adobe website, and write whatever report you want in pure postscript, then have a perl prog. that queries the database, gets the data, and wraps the requisite ps code around it in whatever way you desire. Then it's just an lp away. (Not as hard as it sounds, PS is not that hard to learn)
You have a few options. The quickest and dirtiest would be to use Perl or Python to generate your reports and hard code the Meta-Page Description Language right into the report. Then you end up with something like:
This is the easiest for you to do and stay opensource but it's the hardest to maintain because you have to know SQL, Perl/Python, and groff/latex.
The next dirtiest and probably the quickest for you would be to get the MyODBC module for Windows and use Access to generate your reports. You basically have to set up an ODBC data source for your MySQL database and then create an Access DB where all the tables that you have in Access are Linked to the MySQL tables. You do have to watch out here that you don't run afoul of the limitations of Access, MyODBC, and MySQL. This hasn't been a problem for me but I don't know What you are trying to do. There are notes on the MySQL site. Other than that I'd have to say that the MyODBC module works really well. I was able to access the ip inventory database where we work using the ODBC connection and get some data import/export chores done.
Running Crystal Reports against the MyODBC database isn't a bad option either but the limitations above.
Being the kind of person who would prefer option 1, remain OpenSource, I'd like to know that people would want in this report generation software.
ChrisI've used Access as a front-end (using linked tables) to a MySQL database for administrative end users. You can get some pretty quick and dirty "pretty" reporting that way without having to actually import data as you noted.
It's far from free, but it has a lot of interesting features: fully programmable plus identical output to pdf. We're considering a migration from Crystal (which I don't recommend).
POSSL has an open source report writer for Linux, which currently works with ODBC, oracle, sybase, informix, DB2. There is a Motif GUI editor to design the layout area, which outputs postscript reports. This is currently being hosted by collab.net at http://www.possl.org.
An option nobody else has mentioned (which I'm probably going to be flamed for anyway :-) ) is to generate an XML document from your database (with Perl or PHP or whatever) and use Apache's XML tools (http://xml.apache.org) to generate a PDF from that. There's a lot to learn, especially if you've never used XML, but it's enormously flexible. It's certainly not as easy to use as Access reports, but it's much, much more reliable and flexible.
It's also slow as shit, but that's probably acceptable.
* And remember, it's spelled N-e-t-s-c-a-p-e, but it's pronounced "Mozilla."
After all, one of the official expansions for "perl" is Practical Extraction and Report Language (the other being Pathologically Eclectic Rubbish Lister)...
Perl supports interfacing to MySQL (quite well, see DBI.pm (and the supporting DBD::MySQL.pm for MySQL)). Perl also has something called "formats" which make writing formatted tabular data easy as pie (sort of like fortran IO maybe, or like structured string/variable substitution). See the camel book about that. Basically you'd just grab your result set, and then iterate through it, printing using a format. (you can also do formatted, repeating headers with this; and of course it handles stupid crap like pagination for you) The only "difficult" part would be tinkering with the format to get it like you want (formatting being the pickiest part of any report job).
If you really wanted to be a fancy-pants, perl also has (several? at least two that I can think of) interfaces to PDF generation libraries.
Of course this all requires some knowledge of perl. :-) (If you're a C programmer I'd say you could learn enough perl to do this project in a week or less. Java programmers might feel dirty doing it, but they could probably do the same.)
(I know other languages could do this all, but the format feature of perl would make it _really easy_.)
--
Fuck Censorship.
News for Geeks in Austin, TX