Creating Professional Reports From MySQL Data

For this purpose I would recommend dbForge Studio for MySQL. The tool offers a whole gamut of features for developing and managing MySQL databases. However, it also has very powerful capabilities to create professional reports from MySQL data. Let’s show this with an example.

Let’s say, we are working with the MySQL sample database world_x. It has four tables:

We would like to create a report showing details about each country: its name, its capital city, the continent where it’s located and its total population. Along with these, we would also like to list different cities and towns of the country and their populations.

We have written the following query to get all the relevant info:

USE world_x;

SELECT 
    country.Code AS Country_Code,
    country.Name AS Country_Name,
    country_info.Continent AS Continent,
    country_info.Region AS Region,
    country_info.Population AS Country_Total_Population,
    city1.CityName AS Capital_City,
    city2.CityName AS Country_City,
    city2.Population AS City_Population
FROM
    country
      INNER JOIN 
          (
          SELECT 
            doc->>'$._id' AS CountryCode,
            doc->>'$.Name' AS Country,
            doc->>'$.geography.Continent' AS Continent,
            doc->>'$.geography.Region' AS Region,
            doc->>'$.demographics.Population' AS Population
          FROM 
            countryinfo
          ) AS country_info
          ON country.Code = country_info.CountryCode AND country.Name = country_info.Country
            INNER JOIN
              (
              SELECT 
                ID,
                Name AS CityName,
                CountryCode
              FROM 
                city
                ) AS city1
                ON city1.ID = country.Capital AND city1.CountryCode = country.Code
                  INNER JOIN
                    (
                    SELECT 
                      ID,
                      Name AS CityName,
                      CountryCode,
                      Info->'$.Population' AS Population
                    FROM 
                      city
                    ) AS city2
                    ON city2.CountryCode = country.Code
ORDER BY
    2
;  

Now, we can use this query as a data source to build a report. To do this, we choose the “Database > Report Designer…” option from the main menu. This starts a data report wizard:

We keep the default option selected (Standard Report) and click “Next”.

In the next screen, we choose our database connection and select the option to run a custom query. Then we click “Next”:

In the next screen, we paste the query we showed above and click “Next”:

In the next screen, we choose the fields we would like to display in the report, and click “Next”:

In the following screen, we choose two grouping levels. However, we will change this later. Once we choose the grouping levels, we click “Next”:

In the following screen, we keep the stepped layout for the report and the portrait orientation, and click “Next”:

In the next screen, we select the “Casual” reporting style and click “Next”:

In the final screen of the wizard, we provide a title for the report and click “Finish”

dbForge Studio for MySQL now generates a report for us, and opens that in a design palette:

We won’t go into the nitty gritty here, but after some changes, here is our report’s final design:

And this is how it looks like in HTML preview:

Now, you may wonder if the report elements provided by the wizard are all you can get. That’s not the case. Here are the controls you can use in your reports:

And here are the alignment options:

There is also a scripting option to respond to dynamic events:

And finally, you can export your finished report to different formats:

You can also send the report in any of these formats via email to your coworkers.