| Archive for Downsizer For an ethical approach to consumption |
| tahir |
MySQL reportingI need a report writer for mySQL, something that can generate queries as well as format output, I looked at this:http://www.hkvstore.com/phpreportmaker/ But it can only connect to a single DB at a time, no use to us as we have a few different DBs. |
||
| Blue Peter |
Re: MySQL reporting
Can you create views between databases? It might be a performance hit, but that may not be a problem anyway with reports? Peter. |
||
| tahir |
You mean in PHP ReportMaker? Not according to the vendor. | ||
| Northern_Lad |
Are all the databases on the same server? If so, and provided you have the security rights, you can create views on one database that have data from other databases. | ||
| Blue Peter |
No, views in the database. E.g. in database A you have a view of the data in table X of database B. (A bit as NL suggests; though in MS SQLServer, the databases don't even need to be on the same server), Peter. |
||
| tahir |
So now I need to create views AND joins  
|
||
| Northern_Lad |
Well, you can't have a join without creating a view ...
Not sure of the syntax in MY SQL, but in MS it's something like database.schema/owner.table |
||
| Blue Peter |
You would use views to get the data into the target database, and then you would join this data to other bits of data to create the reports which you want. The creation of the views may or may not also require joins, Peter. |
||
| tahir |
I need to investigate further | ||
| tahir |
I've learnt how to do a "left join", a "group by" and am looking at "rollup", anyone ever used it? I want to insert a subtotal row in the output. | ||
| orangepippin |
Fab, you are starting to sound like a real SQL programmer - I may have some work for you!
Surely there must be reporting tools for mySQL that do this stuff? |
||
| tahir |
Prolly, our programmers dealing with a dos attack at his biggest client, so I took the chance to start poking around. Must say nothing I can see at the mo. |
||
| orangepippin |
I've never tried it myself, but I have been told MS Access works very well as a front-end reporting tool, it has a good but simple report writer. It will connect to non MS databases if you have an ODBC driver - there are ones for MySQL. | ||
| Northern_Lad |
If you're just after fairly simple reports then Access will work work. Never used it as just a reporting tool, but it certainly links through OK.
You might have issues with long fields being trimmed or not being searchable with 'like' though. |
||
| tahir |
Will take a look | ||
| tahir |
Well I've connected via OpenOffice Base using the MySQL connector:
http://forge.mysql.com/wiki/Connector_OpenOffice Can't see how to open more tahn 1 DB though. |