How to use the report tool FineReport to dynamically display report columns and report finereport

How to use the report tool FineReport to dynamically display report columns and report finereport

I believe that the implementation of dynamic columns has plagued a lot of people. The large amount of data, loading of multiple fields will be very time-consuming, and data cannot be truly dynamic and flexible. The existing methods are implemented by means of backward hiding.

How can this problem be solved? Here we will share the FineReport implementation scheme of the FineReport software report designer, which is based on the relevant content encountered in actual work.

MYSQL is a common local database, so I know a little about it, so the implementation method is also based on mysql. First, let's take a look at the use of the information_schema database that comes with MySQL.

When you install or use MYSQL, you will find that there is an information_schema database besides the database you have installed. What is the use of the information_schema database? If you use a WordPress blog, you may wonder if you want to install the database added by the template? After reading this article, you will understand information_schema database.

The information_schema database comes with MySQL, which provides a way to access database metadata. What is metadata? Metadata is data about data, such as database name or table name, column data type, or access permission. In some cases, other terms used to express this information include "Data Dictionary" and "system directory ".

In MySQL, information_schema is regarded as a database, specifically an information database. Information about all other databases maintained by the MySQL server is saved. Such as the database name, database table, and data type and access permissions in the table column. There are several read-only tables in INFORMATION_SCHEMA. They are actually views rather than basic tables, so you won't be able to see any files related to them.

Information_schema database table description:

• SCHEMATA table: Provides information about all databases in the current mysql instance. Is the result of show databases.
• TABLES Table: Provides information about TABLES in the database (including views ). Describes in detail the schema, table type, table engine, and creation time of a table. Is the result of show tables from schemaname.
• COLUMNS table: Provides column information in the table. Describes all the columns of a table and the information of each column in detail. Is the result of show columns from schemaname. tablename.
• STATISTICS table: Provides information about table indexes. Is the result of show index from schemaname. tablename.
• USER_PRIVILEGES (user permission) Table: Provides information about full-process permissions. This information is from the mysql. user authorization table. Non-standard table.
• SCHEMA_PRIVILEGES (scheme permission) Table: provides information about the scheme (database) permission. This information is from the mysql. db authorization table. Non-standard table.
• TABLE_PRIVILEGES table: Lists table permissions. This information is from the mysql. tables_priv authorization table. Non-standard table.
• COLUMN_PRIVILEGES (column permission) Table: provides information about the column permission. This information is derived from the mysql. columns_priv authorization table. Non-standard table.
• CHARACTER_SETS table: Provides information about available character sets of mysql instances. This table is obtained from the show character set result SET.
• COLLATIONS table: Provides control information about each character set.
• COLLATION_CHARACTER_SET_APPLICABILITY table: Specifies the character set that can be used for verification. These columns are equivalent to the first two display fields of SHOWCOLLATION.
• TABLE_CONSTRAINTS table: Describes tables with constraints. And the constraint type of the table.
• KEY_COLUMN_USAGE table: Describes the restricted key columns.
• ROUTINES table: Provides information about stored subroutines (stored programs and functions. In this case, the ROUTINES table does not contain udfs ). The column named "mysql. proc name" specifies the mysql. proc table column corresponding to the INFORMATION_SCHEMA.ROUTINES table.
• VIEWS table: displays information about VIEWS in the database. You must have the show views permission. Otherwise, you cannot view information.
• TRIGGERS table: provides information about the trigger program. You must have the super permission to view the table.

Extended applications

• View the number of records in a database table

Selecttable_schema, table_name, table_rows from tables where TABLE_SCHEMA = 'database name' order by table_rowsdesc;

• View the space occupied by the database

Selectconcat (round (sum (data_length/1024/1024), 2), 'mb') as data_length_MB,

Concat (round (sum (index_length/1024/1024), 2), 'mb') as index_length_MB

Frominformation_schema.tables where

Table_schema = 'database name ';

• View the space occupied by a table

Selectconcat (truncate (sum (data_length)/1024/1024, 2), 'mb') as data_size,

Concat (truncate (sum (max_data_length)/1024/1024, 2), 'mb') as max_data_size,

Concat (truncate (sum (data_free)/1024/1024, 2), 'mb') as data_free,

Concat (truncate (sum (index_length)/1024/1024, 2), 'mb') as index_size

Frominformation_schema.tables where TABLE_NAME = 'table name ';

I believe it may be a bit confusing after reading it. That is to say, mysql has a system library information_schema. This database contains the database instance name, table name, table comment, field name, field comment, and so on of the mysql database. The existence of this library provides a foundation for implementing dynamic columns. (Other databases such as oracle, SQL Server, and db2 have similar features .)

Then we continue to return to the topic. I have created a test database reporttest locally to test the table report. Create a new database reporttest In the mysql database (or use an existing database, but if an existing database is used, the SQL content in the report must be changed. So it is best to create a new one), and then run the reporttest. SQL file I provided with a tool similar to navicat to create a table, import data, and refresh the data. At this time, the data preparation is complete.

The designer creates a data connection test and fills in the corresponding database information. If the test is successful, the test is OK.

In this case, the database name is reporttest, the table name is report, and the data is connected to test. In this case, you can directly preview the dynamic column Implementation Scheme. cpt.

 

You can clearly see the effect. If you select a column, It queries the column and only displays the data of the column. This is the implementation scheme of the dynamic column. Based on my logic, you can replace it with your own data connection database and table data for testing!