Creating reports with Agata Report

In his book Inside Relational Databases, Mark Whitehorn writes, “One golden rule of design is not to ask the question of what information do I want to put into the table, but rather what information do I want to get out of the table.” An equally important question is how to get information out of the table. Some database management systems include tools for creating queries and reports, while others, such as MySQL, PostgreSQL, and SQLite, require that you use third-party tools. One such tool that receives high marks is Agata Report.

Agata Report allows you to create reports that extract and present data from a wide range of database systems. In addition, Agata Report provides visual tools that you can use to build SQL queries, design report layouts, perform mail merges, create graphs, and export reports in different formats. However, Agata Report does require you to have a working knowledge of SQL, especially when it comes to creating relations between database tables, where understanding of different types of joins is essential.

Agata Report can connect to a wide range of database systems without using any intermediary drivers, which makes the setup process easy. Since the application is written in PHP-GTK, it can run on both Linux and Windows. The only thing currently missing in Agata Report is a manual. Hopefully, this article will help help you get to grips with Agata Report’s basic features.

To install Agata Report on Windows, simply unzip the agata.zip file and move the agata folder to the root of your C: drive. To launch the application, double-click on the agata.bat file. Installing the software on Linux requires more steps, but the whole process is described in detail at Agata’s Web site.

With the installation complete, you next have to create a connection to your database. If you want to connect Agata Report to a MySQL database, launch Agata Report and press the Add button in the Database Connection Wizard window. Give the new connection a name, fill out the fields, and press Connect. If the connection is successful, you will see a confirmation message, and then a list of tables under the Tables tab.

Now you are ready to create your first report. The MySQL database used in this article has two tables: PRODUCTS (containing product names and prices) and PURCHASES (containing info about each purchase including customer name, product, and quantity). Let’s say that you want to create a report that lists purchases grouped by product type, as well as the total price for each purchase.

The first thing you have to do is to create a relation between the tables.

• Select the PURCHASES table in the Tables tab and press the Join button.
• In the Linked tables dialogue window, select the PRODUCTS table, press the Join tables button. Select Inner Join from the drop-down list.
• In the Linking settings window, press Add, select the Product field in both tables, and press the Link Tables button.

If you’ve joined the tables correctly, you will see them in the Tables Structure window. The Tables line in the Query window should also contain the following SQL statement: purchases INNER JOIN products on (purchases.Product = products.Product)

The next step is to add fields to the report:

• Click on the Selection tab.
• From the list of fields in the Tables Structure window, select the field you want to add.
• Press the Select This Column button.

Repeat this procedure with other fields.

Since the report has to display the total price for each purchase, you need to add a field that shows the result of the Quantity*Price calculation. To add the field, click on the Tables tab and then click on the Fields line in the Query window. In the List Edition dialogue window, press Add and enter the following calculation Quantity*Price as "Total"

Press the Write button to save the changes and close the window. You can then use the Constraints tab to add filters to your report. For example, if you want to view data only for a particular customer, select the Customer field in the Tables Structures window, press the = button, and enter the customer’s name in the Expression window so that it looks like this:

purchases.Product = "Sam Weller"

To make the report easier to read, you can use the Adjustments tab to tweak each column’s settings, including size, alignment, and format.

Since you want to view data in the report grouped by product, click on the Sum(x) tab, select the Product column from the Group field, and press Apply.

To see whether the report is what you want, choose Report > Preview of Report to see a preview window containing data from the tables. If everything looks good, save the report (File > Save Report or Ctrl-S). You can run the created report whenever you need and export the generated results in a variety of formats and layouts. For example, to export the report to PDF, choose Report > Export to PDF, then select the layout you want and press OK.

If you don’t care for any of the default layouts, you can easily design your own. Choose Tools > Layout Configuration, add a new layout, and configure its settings. Although the number of available options is somewhat limited, you can still design some attractive layouts.

The example described here uses only a few basic features of Agata Report. As you become proficient in using Agata Report you will find that you can generate graphs using the data from a report, you can use the provided tools to create mail merges, and you can publish and run reports on the Web.

By mastering Agata Report’s features, you can really unleash the potential of your database.

Dmitri Popov is a freelance writer whose articles have appeared in Russian, British, and Danish computer magazines.