Sql Report Writing Tutorial

The BIRT Report

Introduction

I recently attended the Maximo UK and Ireland User Group meeting at IBM’s fabulous Bedfont Lakes facility where we were treated to a demonstration of Actuate’s new BIRT reporting capability.

Although rather hampered in terms of performance by the virtual PC on which the demonstration was running the software and technology looked rather appealing. The interactive “flash” based reporting seemed to suggest that the long promised and tantalising possibility of “easy to create” dashboard style reports, over which the end user has an unparalleled degree of control may finally be a reality.

Just to set this in context, I have been a software developer for over 30 years, currently working in the field of interfacing Maximo with financial systems, it is rare that a new technology spikes my interest and imagination. So when I say that I spent a whole Saturday (much the chagrin of Mrs B) downloading, installing and attempting to understand what BIRT was all about, you will realise that this is indeed something worthy of note.

Working for a software vendor and yet still being granted the opportunity of attending the user group meeting I thought I would give something back by publishing the findings of my weekend’s toil so that other members of the group may benefit.

What is BIRT?

Initially I had no idea what BIRT was or what Actuate had to do with it, but somewhere along the line the magical words “open source” had been mentioned and I suspect that this may have had something to do with my new found enthusiasm for the product. So a bit of Googling later I discovered that BIRT (Business Intelligence Reporting Tools) was actually a freely available, open database reporting technology, that consisted of two major components, the report designer and the report server – which when combined allowed the user to create graphically rich reports that can be deployed from a central web server into a standard internet browser.

Roughly translated this means that pretty much anyone, with a reasonable working knowledge of the database against which they want to report can write reports that contain lists and graphs and that can allow the user to filter the data by any columns that the report author allows. Furthermore, these reports can be hosted on a central server in an organisations intranet or even on a publically available web server and viewed by anyone who had a copy of Internet Explorer handy on their PC. I suspect that other browsers are supported although I didn’t try that yet!

What do you need?

At first I discovered that in order to create a BIRT report one required a copy of something called Eclipse. This turned out to be a freely available, open source Integrated Development Environment (IDE) into which one installs the BIRT designer components. So I made my first goal to install a copy of Eclipse with these components installed.

Google soon pointed me in the right direction to obtain the relevant software and in no time I was able to follow a tutorial on how to build my first BIRT report.

The Eclipse Designer

At this stage I was beginning to be impressed – the report designer is clearly and logically laid out and basic listing reports are created in a similar manner to creating an HTML table in a WYSIWYG web design package. The downloadable components even include a database against which you can build your first report. This was a godsend because I had no idea how to connect the report designer to one of my existing SQL Server databases, but after creating my demo report this was my next challenge.

Connecting to SQL Server

Again turning to Google and the wonderfully informative BIRT forums I found a great Java component called JTDS, which according to the forums was faster and more reliable than the Microsoft JDBC connector. Once I had this in place I was very easily able to connect to my SQL server and access the tables. I still have a small problem with this whereby I am unable to browse the database tables from within the BIRT / Eclipse environment, but so long as I knew the table and field names that I was after this did not present a significant difficulty.

Getting Ambitious

Before long I had created a report which combined data from two tables, into a third, from which the ultimate report would run. I even ventured as far as creating four separate report variables,  that would allow the user to enter parameters such as a period and account range, against which the report would filter. Having gained some confidence by this stage I was able to apply a bar chart above my data table and even an image to the header of the report. Every step of the way I could preview my report in the report viewer and very soon came up with a result that I was reasonably happy with.

Having written a parameter driven report, containing advanced components and a complex data source my next challenge was to publish it to a browser.

Publishing to a Browser

For this I discovered that I needed a copy of the Tomcat web server, again freely available online. Having downloaded and installed Tomcat and put my report in the folder where the instructions told me to put it – it didn’t work! In order to solve the problem I had to copy my JTDS driver into the Tomcat server framework and restart the Tomcat service in order to pick up the change. Then I was able to point a web browser, from another machine, across the network at my Web server run my report from all the way across my living room!

What does Actuate Add

One of the exciting things about the demonstration that we saw on the user group day was the ability for the report user to physically tweak a flash control that looked a bit like a VU meter and have the report data modified to reflect the change in real time. This turns out to be the key difference between what you can download and use for free and what you get if you buy the BIRT add-ons from Actuate. There is also a different report designer available from Actuate and I shall be installing the trial version of these tools as soon as my wife will let me and hope to write about my experiences with those too.

Conclusion

In conclusion, the freely available BIRT reporting tools running on the Eclipse and Tomcat combination allow intermediate level users to create and deploy graphically rich, parameter driven reports. The report designer does need a good understanding of the source database and these reports are not capable of using the advanced, real time, flash based Actuate tools, but still offer an easy to use reporting environment with a result that is easy to deploy across the enterprise.

Contact the Author

If you need any specific advice or assistance and to receive updates on my further adventures with BIRT please feel free to contact me.

About the Author

Paul Bappoo

Maximo to financials integration
Automated Software Testing
Bespoke Software Development
System Implementation
Software design
Software Project Management
BIRT Enthusiast!

Paul@Bappoo.com

My first Sustainable Application with SIDE - v1 - Step 6 SQL for Alfresco and BIRT Report


sql report writing tutorial
sql report writing tutorial
sql report writing tutorial

Sql Report Writing

sql report writing"sql report writing"

4 Signs Your Business Needs A Java Reporting System

When it comes to defining the health and financial state of a company, the only way to see all aspects is to have reporting software that is robust enough and flexible enough to deal with all the data. Many times companies have software packages from several different companies and the databases don't always cooperate. Having a package that you can easily customize is key. Java is a programming language that gives companies the ability to write exactly what they need, so getting a management reporting software package based on Java adds a level of flexibility not available with many others. Java reporting software enables you to take data from all aspects of your enterprise and integrate it so you can see what's going on.

Many businesses use a MySQL database while others prefer a single format like Microsoft SQL. Other businesses have specific applications which they have to customize their databases to them. And to complicate things even more, many accounting packages use proprietary databases.

Here are some signs you need a java reporting system:

- The biggest sign you need a java based system is if you've got several different databases in your business. You will have the ablility to import data from different sources with Java.
- Java based systems rapidly integrate all sorts of disparate datasets into a single dataset to work with reporting.
- J2EE reporting systems gives you the option to customize reports constantly to indicate your business changes.
- Java systems will export in virtually format as well. This means you can take reporting data from a custom designed production database, integrate it with accounting data and export it to any Microsoft Office format like Excel for graphing and charting.

With Java reporting tools, you will be able to get data from any database. Even if you're using custom built databases, pulling data for reports goes from a hassle to a breeze. Generating your reports will be as simple as a click of your mouse once the set-up on the import and reporting side is complete. Using other reporting solution methods can have a lot of uncertainty with them and the flexibility of Java reporting software can take care of that. Take a look at your needs and see just how easily Java based reporting software gives you the reporting solutions for your company. Being able to get a clear picture of the bottom line has never been so easy. Or so flexible.

About the Author

Finding the right
Java reporting software
for your business is what matters when it comes to providing the best for your business and clients.
Scott Duglase
had specific needs in mind when he started looking for a good reporting software package to use for his office.

ShelbyPODCAST - SQL Reporting Services


sql report writing
sql report writing
sql report writing

Report Writing Tutorial

report writing tutorial"report writing tutorial"

Oracle SQL Tutorial, Oracle SQL Developer, Oracle SQL

The first thing we need to do so, to explain the various ways and levels of certification, and then we can discuss the best educational resources. Click the new installation and follow the screens that appear. When prompted, enter the instance name of your choice. One screen will ask for a username and password. For the purposes of this tutorial, use your normal log information along with the authenticated Windows. Get a snack or take a lap around the block. If an error occurs during installation, the type of error message in the search engines to find a solution. When installation is complete, you'll see full screen. When the program opens you will be prompted for the server name. You are now very close to writing the actual Oracle SQL Tutorial. Your database should now contain a large amount of data. Check this by clicking on the database in Object Explorer in Management Studio. You should see a list of Adventure Works database.

Now we are ready to write some SQL learning. Find and open a program called Oracle SQL Developer Server Management Studio in Microsoft SQL Server, the folder of your programs on the Start menu. Management Studio is often referred to SSMS provides an environment for writing SQL. Even non technical personnel can use it for reporting and analysis. Anyone with access to data bases or reports will find the knowledge of SQL is essential. Use the name you typed, for example, in the name of the installation process and make sure that the authentication screen reads authenticate Windows. Then click Connect. Since most people do not have the data lying around, you're probably wondering where you can get some. Now you are in the main screen, Management Studio. You should see the instance name in Object Explorer, and probably a lot of empty space.

At this point you're missing one vital element, the most important of all, and this data. You need to download the data to write Oracle SQL. Fortunately, Microsoft made a sample database; called Adventure Works are available on the website msftdbprodsamples.codeplex.com. Follow the instructions on the screen and select the instance name you entered during installation. Find the corresponding package Sample Database Oracle SQL, probably. After downloading the executable file, locate the file and double click it to install the sample database.

About the Author

We offers in high quality interactive Oracle SQL Tutorial, Oracle SQL Developer. We highest care professional knowledge of Oracle SQL which are suitable for students, job seekers and companies.

Paraplanning Online Tutorial 4 Creating a report Part 3 The Investment Recommendation Section


report writing tutorial
report writing tutorial
report writing tutorial