Creating Custom PDF Reports from an SQL Database

I was posting on LinkedIn asking for some assistance in find an EASY way to create a simple 30-Day BTU usage report in PDF format from some historical data that our controls system is archiving. And sure enough I found a gem.

Here is what I initially posted to a couple of LinkedIn Groups…

NEED: Easy PDF creator from MySQL data for BTU usage reports.
I am looking for an easy way of creating custom (with logo/letterhead) PDF report templates that can be used with a MySQL database. The resulting reports will be simple table formatted data with maybe 3 or 4 values showing daily metered BTU usage for the month.

The resulting report in this video(just an example) is exactly what I would like to duplicate…

Don’t forget… EASY. Not looking to spend a week learning to program a table in PHP etc. though I am open to having to do some code if there are lots of online help or message forums with examples or community support.

(I am not sure what was used to create these reports,
but you can contact Digital Air Control Inc. if you are curious.)

 

Create Custom PDF Reports Solution Found

Now after I posted that up there were a few people who asked me for some additional information and few ideas that weren’t exactly what I was looking for but… Out of no where Christian Tremblay suggested I check out Jaspersoft. A few clicks later exploring their website about their various reporting and dashboard applications I could see they had everything I was looking for…

  1. It is Open Source with FREE GPL and Paid Commercial Licensing
  2. It has a large, supportive community with message forums, wikis and blogs
  3. There’s even articles and YouTube videos explaining how to do things

It looks like this may be the answer.

 

Jaspersoft Studio Reports are Feature Packed

So much so that for my simple application of being able to pull about 4 values for a 30-day period and put them in a table with 4 columns… it is pretty much overkill. BUT the biggest saving grace is that it is free, so I can install it and give it to our customer without cost to us under the GPL License. If creating reports is as easy as I have seen in many Youtube videos… then it isn’t much harder than working in Excel, mixed with a little database know-how.

It may take a few days playing with it to get what we want done, but being able to have a solution for our customers when they want some really schnazzy custom reports… Definitely a value added feature to any project. It will easily pay for itself over and over. It would also be quite the cookie cutter operation because once you learn to create a particular report you can just save it as a template and on the next project reopen the template and maybe change around what values you want to show in the report. Pretty efficient, I like that.

Work Smarter, NOT Harder

 

A Walkthrough of Creating a Report in Jaspersoft

Below are some images from their website that kind of give you an idea of the simplicity of creating your own custom reports from a database as opposed to having to do some complicated programming like PHP or hand written XML. Which is a god send for me because I have better things to do than have to sit days upon days learning programming that someone else probably can do in their sleep.

Easy and intuitive screens that you are used to seeing with other Applications allow to select a pre-made report template or a blank one if you wanna get crazy.

Easy and intuitive screens that you are used to seeing with other Applications allow to select a pre-made report template or a blank one if you wanna get crazy.

Here we can choose what data fields we want to pull from our data source to incorporate into your report.

Here we can choose what data fields we want to pull from our data source to incorporate into your report.

After you have inserted the various fields from your data source and located them where you want to see them in your report, you are almost done. What's better is you can even define in the report fields that should repeat down the page.

After you have inserted the various fields from your data source and located them where you want to see them in your report, you are almost done. What’s better is you can even define in the report fields that should repeat down the page.

Wonder how to jazz up those reports and really make your customer happy... insert their logo or images to really connect them with your system or reports. You have many options to be able to insert images, charts, etc.

Wonder how to jazz up those reports and really make your customer happy… insert their logo or images to really connect them with your system or reports. You have many options to be able to insert images, charts, etc.

Preview of custom report in Jasper Studio

Here you can preview your custom report with the actual data that you have linked to your project.

Now that everything is looking good, you are ready to export the completed report. More decisions to make... there are many export options for any situation.

Now that everything is looking good, you are ready to export the completed report. More decisions to make… there are many export options for any situation.

One Response to Creating Custom PDF Reports from an SQL Database

  1. Frank Garcia August 16, 2014 at 22:53

    Thanks Abel, I was looking for something like this. Going to try it out. How are you doing ??

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

A Great Value in Online Advertising

The Controls Freak has slowly been building up a dedicated audience of Building Automation/Controls and Facilities Professionals in the United States and around the globe. As we bring aboard more contributors our viewership will continue to grow exponentially.

Full analytics reports can be requested on the Contact Form along with a request for current rates and available advertising spaces.

Now is a great time to support The Controls Freak and introduce your brand of products or services to even more potential customers for an extremely affordable rate.

Send a request today and let’s start working together.