Reporting

[cantlep]

Hi Steve,

Whilst looking through some of the modules available for Xataface, I found this: http://weblite.ca/svn/dataface/modules/Reports/trunk/ Is this reporting that can be used in a "normal" installation or is it for Web auction or something else? This type of thing would be ideal (see test.pdf) for one of my sites. I'd basically like to select certain fields from certain DB tables and produce a tender document (much like that example PDF).

Will this do the job?

Cheers

Paul

[jhenry]

Paul,

Not sure if you have solved this or not but if you are looking for a reporting solution you might want to look at Jasperserver. There is a community version that is FOSS and there is pretty good support. I currently use xataface to enter workorders for a service business and I use Jasperserver to print the workorders as a PDF to email to customers, it will also export in excel, word, csv. I also have invoicing built into the system and have set it up to print those as well. You use a client side report writer that is pretty simple to use called iReport. I created an inventory list that prompts for a customer id in a few minutes with this setup. You have the ability to schedule reports and have the system email them to you. It requires a Tomcat app server setup with access to the database and has pretty good documentation to get everything set up. The server has permissions built into it so that you could set up a report and give users access to run but not edit the reports. Not sure if this will help but it has been pretty useful for me. It is described as a replacement for crystal reports. Hope this is helpful.

Jason

[shannah]

The reports module is not complete. I was working on it a couple of years ago but got side tracked. No eta on it. For my recent projects I have generally just produced HTML CSS reports and converted them to PDF.

The reports module was going to be along the lines of Jasper reports.

[cantlep]

Thanks to both of you for your comments. I'd rather keep things server side if possible so I'll probably re-look at this when I'm ready to get it working on my site..

Thanks again.

[shannah]

You can produce server side PDFs from HTML/CSS quite easily using any number of methods. My preferred method of late is to use
http://code.google.com/p/wkhtmltopdf/

-Steve

[cantlep]

Hi chaps,

I'm ready to start looking at this now. Any pointers you can give me regarding the actual code to get this working within Xataface? I need approx. 30 fields taken from multiple tables across a Xataface installation.

Any help would be most appreciated.

Thanks

Paul

[jhenry]

I know you were not sure about the Jasperserver I suggested earlier but just an FYI I found you can pass a URL to Jasperserver and it will output the PDF after you have created the report. It is a little more to go through to get it going but until the functionality is local to xataface it may work. You can see the requirements at jasperforge.org . So you could set up an aftersave in the delegate file the will pull the record number and pass it to the URL and it will basically allow them to open or download the report in a PDF, word or excel or ODF. Let me know if you want a little more detail.

Jason

[cantlep]

Hi Jason,

Cheers for the info. I've spoken to the client and it looks like they have something to do the conversion to PDF anyway so they're happy to do that. I basically need the simplest (and easiest) way to implement what I'm after. If someone else (i.e. you ) has done something similar then if you could help me with it, I'd be really grateful.

My first "worry" is that not all the info I require to be sent on the invoice is in 1 table. So there may be 2 or 3 tables involved. Does this sound like an issue?

Thanks for the help.

Paul

[jhenry]

You should be able to create a view or a transient table with an sql statement that uses an inner join to link the info from the separate tables. I use this format to pull in the data for my reports as I also have the info in several tables. It looks like:

SELECT w.*, c.name, c.address, e.make, e.model FROM Work_Orders w
INNER JOIN Equipment e ON w.control_number = e.control_number
INNER JOIN Customers c ON e.customer_id = c.customer_id

Use the sql statement in the fields.ini to pull in the data you need to report and then convert to PDF from there. Hope this helps.

Do you host the customers data or is it located internally to them?

Jason

[cantlep]

Cheers Jason,

Excellent so the multiple tables isn't an issue

Yeah, I host all the data on my server here.

Effectively, I need to create some sort of action (I guess) that the end-user can click a button or tick a row in one of the tables and select "Generate Invoice" (or something similar) and then that will do "some magic" and print the necessary fields to the screen so that they can turn them into PDFs.

Is that how you do it?

Cheers

[jhenry]

I use Jasperserver for all my reports as I need to do some tabular reports and some other parameter driven events for reporting.

Do you know CSS? If so you can create a CSS template for printing that strips out all the formatting that isn't needed and presents only the info needed for the invoice. The magic part I think I might be able to solve. If I can explain it is another story. I have created a link on one of my tables that is located on the block with new record and update set buttons. When I have selected a record this button uses code in the Table.php to get the record info needed to pull up another url for editing a related table. So in the invoice table this button could link directly to the url that could specify the transient table with the css applied for that record to print the invoice. I hope that makes any kind of sense to you...lol. I don't have access to the code right now as my vpn is all messed up at home so when I get home tonight or if I can resolve the issue from here I will post the code so you can see if you can use it for your purposes.

I am interested is seeing what you have done to secure the server for customer access and if you use a virtual host environment as I would like to start doing this myself. Thanks.

Jason