PHP suitability for producing complex printed reports 3

[piquet]

Hi,

We currently use shell script and AWK on Linux-based systems to pull data from various large flat-file databases and then Excel VBA macros to basically cut and paste the data into complex report templates. The final output is printed or output as PDF. Excel provides us with a very professional look and the ability to include charting, images and text on the page. It also shrinks to fit when the data would otherwise overflow the page. It also formats numbers simply and effectively, and allows for changes to be implemented quickly.

It has been suggested that a better approach would be to use PHP for the output process instead of Excel, since this could all run on the Linux servers without intervention.

I would be very interested to hear professional opinions as to the suitability of PHP for this type of application. Is PHP well suited to producing complex printed output which has an equivalent quality to Excel? Is it the right horse for this particular course?

Thanks in advance.

Phil

 

[nickroper]

Hi Phil,

You can generate pdf files with PHP and there are also some charting libraries. You might be interested to take a look at chart director - which you can use to generate a wide variety of professional looking cahrts that could then be incorporated in pdf documents:

http://www.advsofteng.com

No great experience of generating pdfs myself, but the O'Reilly Programming PHP book has a section devoted to it.

Hope this helps a bit anyway.

Nick

 

[KarveR]

We use PHP internally for a wide range of reports, and also GD or JPGraph for the graphs, and all have a very professional shine to them, the data is extracted from MySQL instead of files but whatever floats your boat, PHP can do it.



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[nickroper]

Hi KarverR
Just checked out the JPGraph site - looks cool.

Bristol, huh? Just up the road from me.

 

[ingresman]

Have you condsiderd automating the process on to windows, I woukd think that trying to replicate excel functionality with PHP is going to be an uphill struggle even with the libraries that PHP gives us. You will have issues as soon as someone wants a change, your into development time scales rather than modifying the excel script.
You can run AWK on windows you would need to be able to see the flat file databases via something like samba or odbc or nfs.
IF you want to chat more post back !

 

[piquet]

Hi ingresman,

Thanks for your post. I have to say that my initial assessment was that it would be difficult to replicate Excel functionality in PHP. Looking at the various online examples of using charting libraries and PDF creation libraries, yes you can do all thosse things but not with the simplicity of creating a template in Excel and using one of many ready-built Excel macros to populate the template with data.

We could certainly run AWK in Windows and the data is accessible to our Windows network but I think the increased network traffic would make that too slow. Another option we have proved is having a Windows-based PC running Excel on the network which sits and waits for output to process. As soon as a folder is populated the appropriate macro autoruns to complete the output, dumping it in another area where a Bravo II CD writer/printer awaits input. This all works rather well - can you imagine PHP being that simple?

Phil

 

[ingresman]

To be honest I would do it with the PC and the macro, as a side question how do you know when the file is closed an so ready ?.
But to go back to the orignal point, I can't see any one wanting to write custom stuff just for reports when you have the likes of excel, crystal reports, business objects etc. End user just don't want to wait these days especially when a lot of them are excel savvy any way !!
I would gaurentee if you went the PHP route you would post endless questions about the libraries !!!!!,
good luck tell us what decisission you make

 

[piquet]

ingresman, when everything the macro requires has been copied to the directory, we touch a "ready.dat" file in the same directory. The auto-run macro is looking only for the presence of this file which acts as its trigger to run. The macro looks every 30 seconds for "ready.dat" files. When it starts processing the data the macro renames the "ready.dat" file to "doing.dat", and when complete the macro finally renames the file to "done.dat". This way several PCs could share the work between them.

Don't get me wrong, I and my colleagues would love the opportunity to get into PHP and all that it offers. I just think that there are horses for courses, and for the type of application I have described maybe PHP would be an hard work.

Does anyone else have an opinion?

Thanks, Phil

 

[nickroper]

Interesting thread. Having previously worked on a variety of applications for an investment bank - to take data from flat-files, rdbms and real-time feeds and then generate integrated reports and charts - I'd go for a non-Excel solution every time these days. The general consencus was that Excel was a great end user tool for modelling etc, but there was no way they were going to build a critical reporting application on it.

Also,a PHP solution is going to be a lot more portable across operating systems.

Anyway, that's just my opinion - you did ask.

 

[ingresman]

Nick,

I know banks of old !!, it's nearly always "we've done it this way and we'll continue this way".
I dont think I'd consider a critical application under excel but a report.....

 

[piquet]

I did ask! Did "they" give reasons as to why they wouldn't build a critical reporting package using Excel VBA?

The reasons I would give for not doing it are really only that it is sometimes quite slow. For example, we have one report set which creates around 1100 pages of complex financial analysis. Most of the pages comprise smaller variable-sized panels of information depending on the context.

I guess I've just been a bit scared off PHP by looking at some of the examples of how people go about building up reports. There was one article I read on PDF creation which went on about how to draw lines - for crying out loud, I don't want to be drawing gridlines using commands when I can drop them into a nicely pre-formatted Excel template!! Then, when the inevitable requests for minor tweaks arrive, I don't want to be searching through oceans of code to find the line to change, when I can go into Excel and make the change visually.

Please bear in mind, I have very little PHP knowledge, and maybe there are vastly better ways of achieving professional looking report pages than I have discovered in just a few days - and I guess that's what I was looking for from you guys.

Very interesting!!

 

[piquet]

... sorry... the Excel macro producing 1100 pages of analysis takes almost an hour to run on an Pentium 4 3Ghz PC...

 

[ingresman]

Try business objects or crystal reports they are very robust, I've also found that it's memory not cpu speed that speeds things up, get as much as you can on the PC

 

[nickroper]

Guys,

Can't name the bank as I signed my life away on non-disclosure and confidentiality agreements - and you never know who is reading these posts. Probably being paranoid, but I've seen guys marched out by security within minutes of sending a slightly dodgy email.

Anyway, to get back to the main thread, this particular bank certainly wasn't old school - although I do know what ingresman means. They are actually very innovative and willing to explore any options in order to get an edge over the competition. They even developed their own proprietory programming language based on A+. The main resaon for not using Excel were:

a) It didn't offer a development environment that easily supported teams of developers working on a central project.

b) It wasn't fast enough at processing the data.

c) It wasn't reliable enough once you started plugging other stuff into it to provide interfaces to files, databases market tickers etc.

d) It wasn't straightforward (not then anyway - I'm a bit out of the loop now) to deploy the application from a central server to multiple clients.

Admittedly the majority of the reports were delivered via a browser based interface incorporating charts as gif files rather than all in pdf format - although in the early days we were converting Wingz spreadsheet into postscript files - which was a real can of worms!

However, I know for a fact that Excel was used extensively to layout templates that were then used as part of the design spec for java/php based applications. However, if you have a solution that works for you then I'm a firm believer in the 'if it ain't broke, don't fox it' mantra.

Don't get me wrong, Excel is a great product - if even if it did used to assume that no long-term bonds would ever expire beyong 2037 or something (so perps were fun) and it may well be pointless to re-invent the wheel in PHP just for the sake of it. On the other hand, there is a PHP API to Word & Excel, so you could have your template set up in Excel and populate it with PHP and have the best of both worlds...

Nick

 

[ingresman]

Nick,

Just a last one, I've worked for banks and the phrase

"They even developed their own proprietory programming language based on A+."

Says it all for me !!
btw what is A+?

 

[piquet]

Nick,

I really appreciate the time you've taken to explain. It does help me to weight up the pros and cons. Out of your reasons for not chosing Excel, only the speed thing really applies to us since we only really need to pass in csv files and we are only a very small department.

The way it works for us is that our field-based consultants request reports via an intranet. Shell script and AWK programmes then interrogate the database to create the required summary files which are packaged using pkzip together with an Excel template and macro. The resulting zip file is automatically emailed out to the requestor who simply unpacks it to a folder on their PC and double-clicks on the Excel macro (xla), makes a cup of coffee, and then comes back to a finished report set.

The great thing about this approach is that the email attachment is usually less than 1Mb in size. The final Excel report is frequently many Mb and definitely too weildy to email.

Given this, I'm not sure a PHP API to Excel would help would it? We would still need to develop macros to populate the templates with the data. For example, if a report was by department, our csv data would have "department" as a key field, the macro would use a pivot table to select each department and create a separate report page.

One of our guys has PHP experience, and he created an example of how we could use it in the framework I explained above. He used Chart Director I believe to create the graphs as .gif files which were embedded into html output. The trouble with this though is the enormous size of the output to be emailed to the field consultant.

It definitely is the final stage I need to work out. We're happy using shell script/awk/php anything Linux-based we have expertise in, to do the really complex work of analysing the data. Having done that I'm trying to find out the best approach to getting smart looking output which is still small enough to email out.

Thanks again, Phil.


ps. We had Wingz once too! It seemed to be a very flakey product, and lost its appeal once Claire Rayner started advertising it

 

[nickroper]

Ingresman,

OK, point taken and I have to get on with writing some PHP anyway...

The language is very terse, but extremely powerful, and designed to handle and perform operations and manipulations on scalar and vector data objects very efficiently. For example, you could get the last 100 days of closing prices for a set of bonds, calculate the individual yields for each price and then display the results in a window as a chart in a single line of code.

However you had to be a bit of a propellor-head to be able to read or write the code...

Good to chat,

Nick

 

[nickroper]

Hi piquet,

You had Wingz once - jeez, I've never actually come across anyone else that used it apart from clients I - where do you work if can say? Claire Rayner - really - I'm intrigued, and I can understand how that would damage its credibility - we are talking about the Wingz spreadsheet from Informix aren't we?

Cheers,

Nick

 

[piquet]

I'd rather not say!

Yes, we had Wingz the spreadsheet from Informix which we were piloting at the time. We got it out of the box, admired the pictures in the user guide, and had a play with it. After a very short time it became clear that its main value was in brightening up the office shelving - the box was really special!

Actually I think the Wingz Claire Rayner advertised might have been an entirely different product...

Phil

 

[nickroper]

Hi Phil,

The sad thing is that I can remember the box! I have to say that Wingz was very good for me and I pretty much built the business of the back of it, as for several years it was pretty much the only spreadsheet than ran on Unix and had an interface to real-time data and Informix & Sybase. Mind you, most of the users couldn't get on with it - so I made a good income providing development, training and consulting. Then Excel got real-time feeds via reuters and telerate and I made another income converting all the Wingz models to Excel.

Telecomms by any chance ?

Nick