As I mentioned way back, one of the fundamental weaknesses DDMS has is the lack of an easy way to get reports into spreadsheet programs to allow you to work with your data in a coherent way. We currently have two reports, Item by Contract and Customer by Contract where we’ll often want to work with them on a deeper level than we’ve had the ability to thus far. Today I’ll show you an easy, flexible way to turn your reports into Excel spreadsheets.
At this point, if you’ve been following along, we’ve got three reports thus far: a PO Bin report, which gives us the bin location for an item in our warehouse, an Item by Contract report, which shows us all contracts to which an item is attached, and a report, which shows us all customers that have a specified contract attached.
The Item Bin report is pretty self-contained – there isn’t much need for it to do anything beyond return its data on the same page. For the other two, and most of the reports that will follow, you want to do more than look at them in a browser. Rather than fumbling about creating reports in Report Writer using pipes as separators and the like, we’ll create something of a template in Excel that will give your reports consistent formatting every time you run them.
The way we do that is a package called PHPExcel, available from http://phpexcel.codeplex.com/. It was written by Mark Baker, a brilliant if slightly cantankerous guy. I won’t delve deeply into it beyond saying that most of the work I’ve done with the PSyOPs Framework likely would have stopped dead in its tracks if it hadn’t been for this package.
If you downloaded the Bendustries XAMPP package, PHPExcel 1.7.8 is already in /php/pear/Classes. If not, I would recommend following that structure for ease of use, as all these files are written with that path structure in mind. If nothing else, just be sure the PHPExcel.php file is in the same directory as the PHPExcel directory.
We’ll start with the Item on Contract report. The ready-made report is here: bendustries.co/files/contract_by_item-dl.zip.
The other part of this is in the Contract Reports page itself. If you downloaded the version I posted previously and cracked open the code, you may have noticed three lines commented out:
<button onclick="return false" id="viewItemOnContract" value="viewItemOnContract" url="#">View</button>
<div class="subtext">Download in Excel xls format</div>
<button onclick="return false" id="dlItemOnContract" value="dlItemOnContract" url="#">Download</button>
I’ve made the needed changes to the contractreports.php file and posted it to bendustries.co/files/contracts/contract_by_item-complete.zip. As a side note, the way to tell that the necessary JS exists in the appropriate JS file is that the button is styled by the ThemeRoller theme. If it wasn’t, you’d see a button like this:
Incidentally, when I’m developing new reports, that’s what I use to motivate myself to get the download part finished – the mismatched styling drives me bonkers.
You will commonly see this sort of structure in the PSyOPs reports. Where it makes sense, I like having both options – sometimes I just need a quick look at a report and dumping it to the Reports page is the fastest method. Other times I might need to spend some time manipulating the data and in that case, spending 20 minutes up front to put together the appropriate code in PHPExcel pays off within the first couple times of running the report.
Once you’ve got the changes in the contractreports.php file and you’ve put the contract_by_item-dl.php file in the /includes/contracts directory, enter an item number and press the Download button. Shortly, you’ll have a report something like this:
Click for full size
I took a screencap of the entire Excel window to point out some of the nicer features. At the top, we have a filename, Item by Contract for SAN30001 2013-06-20.xls. The naming is always consistent and I can see what the item is and when I ran the report directly in a file browse dialog or Windows Explorer. We have consistent headers – medium blue, always bolded, always centered titles. The rows have alternating colors, like the old greenbar paper. I’ll admit those are imperfect – if you re-sort the report, the colors move along with the rows, rather than always alternating (which is really a failing of Excel). One thing you can’t see here is the top row is frozen in place. Granted, that’s not as painful in newer versions of Excel, but it’s a nice convenience to not even have to think about it – the reports generates this way every time. Down at the bottom, our tab title is ‘Item by Contract – SAN30001′ – personally, I like named tabs. Unfortunately, putting the date in makes it too long – the character limitation is 20, as I recall.
I’ll do a separate post to cover what I’ve run into with PHPExcel that specifically applies to what we’re doing here. While PHPExcel is awesome, the documentation and finding ways to resolve issues is a little scattershot. Where I can, I’ll cover the pitfalls I’ve run into to hopefully pull together fixes for problems I’ve had to overcome in one place (or at least on this site, rather than scattered far and wide across the web).
I’ve now given you what really unleashes the power of DDMS’ SQL reporting capabilities. Here we have reports that can be run on-demand and output in native Excel formats (we’ll cover xlsx later). They only need be formatted once and the output remains the same every time. Once the initial setup is done, it’s as easy as pressing a button.
I hope I’ve gotten across the incredible power and flexibility this framework provides. As ever, if you have questions or require assistance, I am 
Recent Comments