Topic: Report for items purchased (sorted/totaled by supplier)

First, kudos to everyone who's developing FA.  This application rivals some accounting packages I've worked with in the past that cost thousands of dollars!

I'm very new to FA (installed only a couple of days ago), so this might be right under my nose and I just don't see it, but is there a report (preferably PDF/Printer or Excel, although screen-only will do) where I can get a list of items I have purchased sorted (and totalled) by the supplier?  For instance, I'd like to be able to see how many WidgetA items I have purchased from SupplierA as compared with SupplierB.  Fields for the report would be something like this:

Date
Reference (supplier invoice #)
Quantity Purchased
Unit Price
Total Price

Before processing the report, it would be handy (but not mandatory) to be able to select a reporting period and filter the results item (all or one specific item) and supplier (all or one specific supplier).  These variables would be chosen using date pickers (for the reporting period) and drop down boxes (for item and supplier).

Again, I'm sorry if this reporting functionality is already there and I just can't find it (if so, please point me to it).  If it's not available, I'm wondering if it could be added to the wishlist?  I'd program it myself but I only know enough PHP to totally crash a system!  smile wink  I tried installed and tried repgen, but ran into nothing but major headaches trying to work with it.

Thanks for any and all replies.

Regards,

Chris

2 (edited by apmuthu 01/11/2013 05:14:43 am)

Re: Report for items purchased (sorted/totaled by supplier)

Rough and ready method - when ok, submit to FA project:

Modify the Items & Inventory -> Inventory Reports -> Inventory Sales Reports to replace Customer with Supplier.
It is the Inventory Sales Report : REP_ID=304 => reporting/reports_main.php Lines 198-205 - copy over the stanza to be a new REP_ID=306 and edit it accordingly. The file reporting/rep304.php can be copied to a file called rep306.php (rep305.php exists) and edited to replace Customer with Supplier Name/IDs in labels and sqls.

The preferred way is to provide it as an extension.

Re: Report for items purchased (sorted/totaled by supplier)

This seems to be a good report, I will include it in core FA to next minor.

/Joe

Re: Report for items purchased (sorted/totaled by supplier)

This report has now been incorporated in FA core. It has been sent to HG repository for default.

The three affected files are /reporting/rep306.php1, /reporting/reports_main.php1, and /reporting/includes/reports_classes.inc.

Rename rep306.php1 to rep306.php and reports_main.php1 to reports_main.php before uploading/replacing the files.

/Joe

Post's attachments

rep306.php1 9.9 kb, 3 downloads since 2013-01-13 

reports_classes.inc 17.6 kb, 7 downloads since 2013-01-11 

reports_main.php1 15.7 kb, 3 downloads since 2013-01-12 

You don't have the permssions to download the attachments of this post.

5 (edited by chrison 01/11/2013 10:09:26 pm)

Re: Report for items purchased (sorted/totaled by supplier)

WOO HOO!!!

I have managed a large purchasing department, and this report is *very* handy in analysing purchase history.  Using it, I can quickly see which supplier is giving me the best price, track price changes over time, see how much business I'm giving to a supplier, and so forth.  By adding this, you folks have just increased the purchasing module's functionality by 100%...  thanks!

I installed the changed files (backing up the originals, first, of course!), and took a look at what you've done.  Unfortunately, I haven't yet entered any item purchases yet (I'm still entering my sales), so all I can see right now is the basic layout.  The reporting screen looks *perfect* but I'd suggest flipping the printout to landscape mode as some of the fields are pretty long (e.g. description and supplier) so having more horizontal room might avoid having lines wrap.  Other than that, though, it looks just about perfect!

Thanks SO much for doing that!  I'll let you know what I think once I've entered a few purchases and can look at a "real" report.

I'm sooooooo proud of myself...  I actually contributed something to the FA project!  smile

Regards,

Chris

P.S.  Personally, I'd leave it in FA core.  Anyone who's doing serious purchasing is going to want this kind of report, IMHO.

Re: Report for items purchased (sorted/totaled by supplier)

Btw, just to be helpful, I tried to add the report to the Wiki but when I saved the change it doesn't show up.  Does that mean someone has to review and approve it first?  If so, I tried twice so just review and approve one, not both.  lol

Chris

Re: Report for items purchased (sorted/totaled by supplier)

UPDATE:

I have entered two supplier purchases of inventory items.  The GL shows the transactions posted to inventory.  However, when I run the new report I get the headers but no data.  The Inventory Valuation Report shows the correct items, amounts and values.

Something in the new report is broken.  (SIGH!)

Regards,

Chris

Re: Report for items purchased (sorted/totaled by supplier)

Hello Chris,

Please go back and download a new rep306.php1. Follow the instructions about rename and replace.

It should now be fixed. Good luck with the report.

/Joe

Re: Report for items purchased (sorted/totaled by supplier)

I downloaded the new rep306.php1 and put it on the server (after renaming the original one).  Still no data.  (SIGH!)

Regards,

Chris

Re: Report for items purchased (sorted/totaled by supplier)

Strange, it works like a charm here. It did not work correctly before, just like you said, but with this new file everything is ok at my place.

Caching problem? Try to empty the browser cache.

/Joe

Re: Report for items purchased (sorted/totaled by supplier)

@Chris

Regarding the Wiki, you must login before editing. You can use the same credentials as you use for this forum.

/Joe

Re: Report for items purchased (sorted/totaled by supplier)

@chris

Remember that the items must be invoiced before they can be seen.

Also ensure that your invoice is inside the selected period.

Joe

13 (edited by apmuthu 01/12/2013 01:38:07 pm)

Re: Report for items purchased (sorted/totaled by supplier)

Please note that both Inventory Planning Report and Inventory Purchasing Report use the Alt-P keyboard shortcut in the current codebase as of Mercurial Build 3133.

Also the report details exceed the headings on the right side. Attached screenshot.

Post's attachments

rep306_fa.png 19.2 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

Re: Report for items purchased (sorted/totaled by supplier)

The two files, /reporting/rep306.php1 and /reporting/reports_main.php1 has been replaced above in the 3 files download.

Please download again, rename and replace.

Thanks for pointing out the problems.

HG reository for default updated.

/Joe

Re: Report for items purchased (sorted/totaled by supplier)

Hi, guys!

@joe:

I cleared the browser cache and tried again...  still no luck.  Also, the items have been invoiced by the supplier, and the supplier has been paid.  Finally, my test report was for the full fiscal year so the purchases must have been in the reporting period.

Regarding the wiki, I was logged in at the time, so I'm not sure why it didn't display my edits even though I was able to click on the edit button and do the edit  Oh well...  doesn't matter...  I see someone else has added it.

@apmuthu:

I'm not sure what you mean by the Alt-P keyboard shortcut as I'm not use the Mercurial build, but I wanted to let you know that the headings on the right side are fine here, I'm just not getting any data.

@both:

I was looking at the report306.php1 file, particularly the section that generates the SQL query.  My SQL knowledge is limited, but I did notice that the section is hitting the stock_moves table.  When I go into myPHPadmin and look at stock_moves, all I see are items that have an item type of "service."  There aren't any items in there of item type "purchased."  This seems very strange to me as I thought that "service" items aren't tracked in inventory so why would those transactions be in there but "purchased" items aren't???

Am I right here?  And, if so, is the pointing to my having another problem somewhere???  Btw, when I try to run the Stock Movement report I get the progress bar at the top of the screen for a few seconds and then nothing, not even an empty report in the pdf viewer.

Thanks, again, for your help in looking into this.

Regards,

Chris

Re: Report for items purchased (sorted/totaled by supplier)

joe wrote:

The two files, /reporting/rep306.php1 and /reporting/reports_main.php1 has been replaced above in the 3 files download.

Please download again, rename and replace.

Thanks for pointing out the problems.

HG reository for default updated.

/Joe

Hey, Joe:

I downloaded all three files again, and installed them on my system.  I'm still just getting the report header with no output.  I think this has something to do with the fact that none of my "purchased" items are appearing in the stock_move table for some reason.  See my post above for more info.

Regards,

Chris

Re: Report for items purchased (sorted/totaled by supplier)

Thanks to some behind-the-scenes back and forth between Joe and myself, rep306.php now works flawlessly on my system.  The report now correctly shows the data, and is quite usable in its present format although I have suggested it be flipped to landscape mode to make it a bit less cluttered and more readable.

Thanks, Joe, for your help in getting the report working!  You're the man! cool

Regards,

Chris

Re: Report for items purchased (sorted/totaled by supplier)

Now Chris has final succeeded in printing this report.

A new /reporting/rep306.php1 can now be downloaded above.

Please download again, rename and replace.

The HG repository is updated.

Thanks to Chris for helping testing this.

/Joe

Re: Report for items purchased (sorted/totaled by supplier)

No problem, Joe.  This report is going to be great for me in analysing my purchasing practices, so thanks to you for creating it and helping me get it working right!

Btw, I did some mucking around with the working rep306.php (shame on me!) and have managed to flip it to landscape mode and spread-out the columns so that the report is more readable.  Would you like it?  If so, do you want me to e-mail it to you, or can you give me upload privileges and I'll post it here?

Thanks, again, for all your help!

Regards,

Chris

Re: Report for items purchased (sorted/totaled by supplier)

Just hold it a bit, Chris. We try to keep all core reports in portrait mode. I understand that certain reports would benefit from being in landscape. You could probably just show the different lines, so other users can correct this for themselves if they need it.

Remember to make a backup of your own report, so you can restore it when updating to a new release.

Joe

Re: Report for items purchased (sorted/totaled by supplier)

Okay, Joe.  I didn't realize that core reports must be in portrait mode.  How about if I submit it as an extension?  Would that be okay?  If so, can you give me (or point me to) instructions for submitting extensions?

In the meantime, for those who want this report in landscape mode, save a backup copy of rep306.php so you can get back to normal if something screws up.  Then, change line 137 to this:

    $cols = array(0, 80, 260, 325, 375, 520, 540, 605,    680);

And change line 152 to this:

    $rep = new FrontReport(_('Inventory Purchasing Report'), "InventoryPurchasingReport", user_pagesize(), '10' , 'L');

(Disclaimer:  If you break something, you own all of the pieces!  lol)

Enjoy!

Regards,

Chris

Re: Report for items purchased (sorted/totaled by supplier)

Joe, this can be accommodated using a flag in the form data input - Report Mode -> Portrait / Landscape.
Also hope the numbers pertain to A4 as well.

Re: Report for items purchased (sorted/totaled by supplier)

@apmuthu:

Having the ability to select whether a report prints in portrait or landscape mode when printing reports would be a great feature, but implementing it would require quite a bit of work.  Aside from coding the repot selection screens, all reports would have to be copied and modified so that each report had both a portrait and landscape reportXXX.php file.  My guess is that the developers have plenty of other things to keep them busy.  lol  So, I don't think this is going to happen any time soon.

Anyway, I don't know if my changes will work with A4 paper or not.  I haven't even printed it on letter size...  I just looked at the screen layout.  I suppose I should do that, but I have something else I have to do right now so that has to wait.

Regards,

Chris

24 (edited by apmuthu 01/14/2013 12:20:18 am)

Re: Report for items purchased (sorted/totaled by supplier)

It is possibly easier than it appears.

The form input page can have a dropdown of various paper sizes - A4, Letter, etc.

The reports can remain the existing repXXX.php - no duplication is required.

The horizontal layout of the report for each page size for heading and detail can have their numbers in arrays - one of which is chosen based on the flag taken from form input data. In all other cases, where separate arrays are not available for different page sizes, the Landscape should be the same as the Portrait except that the longest field if any may be expanded to fill the slack space.

The Vertical Layout remains the same as long as the headers and detail elements add from the top and the footer elements subtract from the bottom and the page size determines the number of lines inbetween - that is all automatic in tcpdf used.

Re: Report for items purchased (sorted/totaled by supplier)

You totally lost me there, fella!  lol  But, if the developers can do it easily and want to do it I'll sure as heck use it!

Regards,

Chris