DEV Community

Kate Naylor
Kate Naylor

Posted on

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:

Reports From MySQL Data 01

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
;  


Enter fullscreen mode Exit fullscreen mode

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:

Reports From MySQL Data 02

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”:

Reports From MySQL Data 03

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

Reports From MySQL Data 04

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

Reports From MySQL Data 05

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

Reports From MySQL Data 06

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

Reports From MySQL Data 07

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

Reports From MySQL Data 08

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

Reports From MySQL Data 09

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

Reports From MySQL Data 10

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

Reports From MySQL Data 11

And this is how it looks like in HTML preview:

Reports From MySQL Data 12

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:

Reports From MySQL Data 13

And here are the alignment options:

Reports From MySQL Data 14

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

Reports From MySQL Data 15

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

eports From MySQL Data 16

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

Top comments (0)