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

Comments

GNU Enterprise (future)

[Micah]

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.

Depending on the levels of reporting...

[alexhmit01]

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.

NOOOOooooo....

[OlympicSponsor]

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

Huh?

[OlympicSponsor]

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

It's all coming back now...

[OlympicSponsor]

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

HTML not great for printing

[brlewis]

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.

sort of minimalist answer: use perl

[StandardDeviant]

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.