Want to read Slashdot from your mobile device? Point it at m.slashdot.org and keep reading!

 


Forgot your password?
Close
typodupeerror
×
News

SQL Report Writers For Unix? 27

Posted by Cliff from the data-analysis-on-hardcopy dept.
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."
This discussion has been archived. No new comments can be posted.

SQL Report Writers for Linux? More

SQL Report Writers for Linux?

Comments Filter:

  • ODBC (Score:1)

    by jurka ( 57039 )
    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.

  • Well you know, I actually thought about that. The problem here is that Access is very specific about SQL constructs. For some reason (I think it's monopolism, but call it whatever) Microsoft's program doesn't allow you to use any MySQL functions or constructs (example: the if() function). So for anything but the most basic queries, Access dies. This is definitely a 'feature' of Access, not ODBC.
  • 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.
  • Well, it's not free, but...

    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 [openone.com]
  • 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 [openone.com]
  • Don't forget having to create a view everytime you want to hit more than one datasource. CR does't let you hit more than one DS, doesn't let you use Oracle user defined functions. It's seems there are far more things it doesn't do than it does. Oh and you want to schedule a report to run and save data guess what, you'll need Seagate Info also (big bucks) and you can't even run it on the same machine as the Crystal Web Server, so be ready to buy an additional machine. There is also poor interoperability between versions (Crystal 8 Reports don't run under the current version).
  • CR is probably just barely usable for a small company, provide you put ALL of your user-defined/calculated fields in the DB itself.

    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?

  • 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 [openone.com]
  • If you want to use ODBC, both StarOffice and Applixware DB tools will talk to ODBC.

    I see that KSql [sourceforge.net] doesn't yet allow formatting of query results.

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

  • Yep, using a passthrough sure did the trick. Thanks a bunch. :)

  • HTML not great for printing (Score:3)

    by brlewis ( 214632 ) on Monday January 22, 2001 @11:07AM (#491703) Homepage

    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.

  • A few months ago I was looking into printing mailing labels and invoices from info already in a mysql db. Yet I was not able to find a decent open-source solution. I ended up writing them out by hand.

    Jonathan Moran

  • If you don't care if it's open source... (Score:1)

    by Anonymous Coward

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

  • Excel 97 and up has a neat feature that lets it read in HTML tables directly. Here are a few tips:
    1. Pass the MIME type "application/vnd.ms-excel" to the browser. IE will open Excel in-case, Netscape should allow you to launch Excel.
    2. Leave out the HTML, BODY, HEAD and other tags. Just do a straight table of data.
    3. You can include a header, etc. above the table by putting in some regular text, experiment a bit.
    4. 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.
    5. 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 [zdnet.com] and here [15seconds.com]. 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.
  • Crystal Reports? Run away, far, far away. Crystal Reports web access server has a $1,000 fee per simultaneous user. Their logging / licensing system counts anyone who has an open window to a report as an active "simultaneous user" - so the costs can add up quickly. It seems to me there has to be a more cost-effective solution for the basic requirements described here.
  • 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:

    $ myreport.py | groff | lpr -Ppsprinter

    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.

    Chris
  • I'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 [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 [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.

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

Slashdot Top Deals

Friction is a drag.

Close