SQL Report Writers For Unix? 27
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."
ODBC (Score:1)
Perl formats + DBI (Score:1)
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.
Re:ODBC (Score:1)
GNU Enterprise (future) (Score:2)
Depending on the levels of reporting... (Score:2)
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.
Crystal Reports (Score:1)
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.
NOOOOooooo.... (Score:2)
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 [openone.com]
Huh? (Score:2)
Second of all, why use "if()"? Create a defined field on the DB OR Access end and do the work there.
--
MailOne [openone.com]
Re:NOOOOooooo.... (Score:1)
Re:NOOOOooooo.... (Score:1)
Well, he said he's just using it to make mailing lists labels and lists. Sounds pretty simple. Plus, he mentions LaTeX, so I don't think he's trying to get his stuff onto the web. I still think Crystal might do well. I've been using it off and on for three years, only for Btrieve, Access, and a little bit of other stuff. No problems with those drivers at all. Trying it with Novell's (very beta) ODBC for NDS driver sucks butt, though, so it probably is up to the quality of the driver.
Crystal is great for small companies: the only kind I've worked for that used Crystal. This is his own DB. Why would he need to worry too much about user-defined fields? As far as calculated ones go, I've had no problems building calculated fields in Crystal. Does using Crystal Data Dictionaries take care of some of those details, as far as user-defined fields go?
It's all coming back now... (Score:2)
We even resorted to creating our own scheduler in VB using the CR VBX/OCX. Pretty ugly, but we eventually did it.
--
MailOne [openone.com]
Re:ODBC (Score:1)
I see that KSql [sourceforge.net] doesn't yet allow formatting of query results.
What exactly is the problem area (Score:1)
(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.
Re:Huh? (Score:1)
HTML not great for printing (Score:3)
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 [sourceforge.net] and pdflatex. If anyone wants help doing it the same way, let me know.
I was looking to do the same thing (Score:1)
Jonathan Moran
If you don't care if it's open source... (Score:1)
You could always use something like Brio Technology's [brio.com](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/ [ontko.com].
How about PHP to Excel for printing (Score:1)
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.
Re:Crystal Reports (Score:1)
try PERL (Score:1)
I forgot to mention (Score:1)
3 Ways to do this (Score:1)
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.
ChrisWell, if Access is already part of the landscape (Score:1)
Check out Actuate (http://www.actuate.com) (Score:1)
go to http://www.possl.org (Score:1)
XML/XSL/FOP - PDF (Score:1)
It's also slow as shit, but that's probably acceptable.
sort of minimalist answer: use perl (Score:2)
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.