Design and develop an easy-to-use Web Reporting tool (support common relational data and Hadoop, hbase, etc.)

Easyreport is an easy-to-use Web Reporting tool (supporting hadoop,hbase and various relational databases) whose main function is to convert the row and column structure queried by SQL statements into an HTML table (table) and to support cross-row (RowSpan) and cross-columns ( ColSpan). It also supports report Excel export, chart display, and fixed header and left column functions. The overall architecture looks like this:

Directory

• Development environment (development environment)
• Installation and Deployment (Installation & Deployment)
  • Install from source code
  • Install from release package (from release Packages)
  • Scheduled Task program deployment (scheduled task Deamon)
• Instructions for use (User Guide)
  • Preparatory knowledge (Prerequisites)
  • Data source settings (DataSource configuration)
  • Configuration Management (config)
  • Report Design (Reporting)
  • Scheduled Task configuration management (scheduled task configruation)
  • Example (Examples)
  • Related references (referrence links)
• Developer (for Developers)
  • Report Engine interface (Reporting engine API)
  • Custom report Development (Customsized Reporting development)
• FAQs (FAQ)

1. Development environment (development environment)

• jdk1.8
• Maven3
• Idea or Eclipsejee
• Lombok
• tomcat8+
• mysql5+

2. Installation and Deployment (Installation & Deployment)

System default Login User: admin password: 123456

2.1 Installing from source code

First make sure to install jdk1.8 and Maven3, mysql5+, and configure the Maven warehouse, and then follow the steps below:

• Step1:git Clone Https://github.com/xianrendzw/EasyReport.git
• Step2: Find Your_git_repository/easyreport/docs/db/2.0/easyreport2.sql and execute the SQL script in MySQL to create the database and table structure, initial data
• STEP3: Modifying the IP, user, and password of the Your_git_repository/easyreport/easyreport-web/src/main/filters/${env}.properties database connection string
• STEP4:MVN Clean package-dmaven.test.skip=true-p${env} (${env} variable Description: Dev represents the development environment, PROD represents production, test indicates testing)
• STEP5: After Step4, the Easyreport-web.war file is generated in the target directory, and the file is deployed to containers such as Tomcat,jboss,jetty.

2.2 Installing from the release package (from release Packages)

First determine the installation of jre1.8 or jdk1.8 and mysql5+, and then proceed as follows:

• Step1: Download the latest version of the war file directly from release
• Step2: Download Db.zip directly from release and unzip to find Easyreport2.sql, execute the SQL script in MySQL, create database and table structure, initial data
• Step3: Modify the IP, user, and password of the database connection string in the Web-inf/classes/config/easyreport/spring/spring-datasource.xml in the war file
• Step4: Then deploy the war file to a container such as Tomcat,jboss,jetty.

2.3 Scheduled Task program deployment (scheduled task Deamon)

Sometimes need to put the report timing (daily, monthly, quarterly, etc.) to the relevant personnel in the mail, so need to schedule Task Scheduler, commonly used scheduler is also a lot (linux:at,crontab;windows: Scheduled Tasks), this tool to implement a simple scheduler. Description: The program is optional and can not be deployed if you do not need to periodically post the report as a mail message. The specific installation and deployment steps are as follows:

• STEP1:CD Yourgitrepository/easyreport/easyreport-scheduler
• Step2: Modify Src\main\resources\${env}\resource.properties database connection, user and password
• STEP3:MVN Clean package-dmaven.test.skip=true-p${env} (${env} variable Description: Dev represents the development environment, PROD represents production, test indicates testing)
• Step4: After Step3, the Easyreport-scheduler.jar file is generated in the target directory. Then execute the following shell command in Linux:

&

3. Instructions for use (User Guide) 3.1 Preliminary knowledge (prerequisites)

Simply put, a report is a form that uses tables, charts, and other formats to dynamically display data. It is an important part of data visualization. Especially in today's era of big data flooding, all kinds of reports are needed everywhere. Before using the tool, you should understand the concepts of data warehousing, dimensions, metrics, fact tables, and so on, which will help you make reports.

This tool simply reads the data from the fact table in the database (Mysql,oracle,sqlserver,hbase, etc.) and transforms it into an HTML tabular presentation. Complex OLAP-related functions such as cube, drillthrough, slicing, etc. are not supported.

3.2 Data source settings (DataSource configuration)

Before you make a report, you need to set up the data source, which only supports generating reports in a single data source (that is, a database).

3.3 Configuration Management (config)

Configuration management is primarily a Chinese description that automatically matches some commonly used column names when making reports. such as: dt,date (date), title (title) and so on.

3.4 Report Design (Reporting designs)

Usually, as long as the data source configuration successfully can start the report design, report design is divided into two steps: Basic settings and query parameters settings. And the basic settings must be saved before the query parameter settings, query parameter settings are optional, mainly see the intention of the Report Designer.

3.4.1 Basic settings (Basic Settings)

The basic settings for a report are made up of 4 parts (as shown): Report tree list, report basic properties, report SQL query statement, report metadata column configuration. Before designing the report, we introduced a few nouns, we understand the concept of dimension and measure from the Data Warehouse concept, in fact, the result of an SQL statement query is a two-dimensional table, that is, a table consisting of rows and columns, in statistical analysis, we refer to some columns as dimension columns, and some columns are called measure columns. Sometimes the fact table has several dimensions and measure columns, but SQL query results can only be a two-dimensional table, it can not be hierarchical, display way fixed but not flexible changes, so that in the observation and analysis of data is more inconvenient, so some reporting tools to solve these problems. This tool divides the dimension columns in the fact table from the measure columns as shown in the following table:

type	Sub-type
Dimension columns	Layout dimension column, abbreviation layout column
	General dimension column, abbreviation dimension column
Measure columns	Statistics column
	Computed columns

1. Layout columns are primarily used for report presentation, and if the layout is displayed horizontally, the report draws the contents of the layout column to the left of the report header, and the contents of the dimension columns to the other side of the reporting body, and if the layout column is portrait oriented, the report draws the contents of the layout columns to the left of the table report body. The contents of the dimension column are drawn in the report header.

2. Computed columns are dynamically computed based on the values of the columns in the SQL query results and are then calculated according to their configured expressions, which do not exist in the SQL statement or fact table, where the expression engine used is aviator.

With this basic knowledge in hand, let's take a look at the main design flow of a report: 1. Create a report tree Catalog List 2. Click 1 to create a new root node, or you can right-select a child node 3 in the tree list. Choose the specified directory, set basic information such as report name, data source, layout and Statistics column Presentation 4. Enter the report SQL query statement 5. Execute the SQL query statement and get column information for the report 6. Configure the columns of the report 7. Add and save basic settings information to the database after a new success, you can double-click the Report name node in the tree list or click the Report Preview button to preview the report. If you feel that the report is not friendly enough, you can change the display of the report by modifying the display of the layout column and the statistic column. is the report preview result that is displayed horizontally for the layout column and the date. We can modify the relevant configuration to make the report display more intuitive. Because the column name DT has already set the default caption in configuration management, it will automatically match its caption after executing the SQL, or you can add additional column names to the configuration management item so that the default title will be automatically matched the next time the report is designed. Now look at the revised report presentation.

3.4.2 Querying parameters (query Parameter)

Sometimes reports need to be generated dynamically based on specified criteria, such as to see the air quality of different cities, this time I need to create a query parameter variable. The form control is used in the report query parameter display form, mainly has the drop-down Radio box (select), the next Order multi-box (select Mul), the check box (checkbox) and the text box (textbox) four kinds. 1 is the query parameter list. When the form control for a query parameter is a drop-down or multiple-selection, the content source has two different forms.

Content Source	content	Notes
SQL statements	Select Col1 as name,col2 as text from table ...	The Value property of the drop-down box that contains only two columns and the column name must be name and the value of the Text,name column corresponds to the Text property of the drop-down box
Text string	Name1,text1|name2,text2| ... Or name1|name2| ...	Multiple values must be ' | ' Delimited, if name is the same as the text value, select only one and use ' | ' can also be separated

3.4.3 built-in variables and functions (build-in variables & functions)

Some commonly used query parameters do not need to be created by the user every time, so they are integrated within the tool, and these parameter variables are called built-in variables.

Some reports have complex SQL statements that sometimes need to be generated dynamically based on parameters or need to be generated using the template engine (velocity), so you need some functions that can be applied in the template engine, called built-in functions.

1. Built-in variables (case-sensitive)

Variable name	Description	return Value Description
StartTime	Start date	2015-02-04 (the first seven days of the default end date, this can be modified by the number of days the report basic settings are displayed)
EndTime	End Date	2015-02-10 (default current day)
Intstarttime	Integer start date	20150204
Intendtime	End date of integral type	20150210
Utcstarttime	UTC start Date	2015-02-04 (UTC date, China to Utc+8 district)
Utcendtime	UTC End Date	2015-02-10 (UTC date)
Utcintstarttime	UTC Integer start date	20150204
Utcintendtime	UTC Integer End Date	20150204

2. Built-in functions

• Date function
• For string functions, refer to the Org.apache.commons.lang3.StringUtils class

3.4.4 Chart Display (charting)

Click the icon Display button of the report, the following interface appears: If you want to see more than one city can also be displayed by comparison: If the statistics column has only one column, the chart display can support two dimensions at the same time all show:

3.5 Timed Task Configuration management (scheduled task Configruation) 3.6 Example (Examples)

All the data in the sample is from: Pm25.in, Aqistudy, if you need to run the report in the example, the need to create a database named China_weather_air in MySQL, and then unzip Yourgitrepository/easyreport /docs/db/mysql.zip, and executes china_weather_air_mysql.sql to create the table structure and import the initial data.

1. The simplest report, directly corresponding to the database two-dimensional table structure configuration: report:
2. Report configuration with built-in variables and query parameters: Query Parameters Report: A. Layout column landscape, statistic column horizontal B. Layout column portrait, statistic column horizontal c. Layout column landscape, statistic column portrait D. Layout column portrait, column portrait
3. Multi-layout Column report configuration: report: A. Layout column landscape, statistic column horizontal B. Layout column portrait, statistic column horizontal c. Layout column landscape, statistic column portrait D. Layout column portrait, column portrait
4. Statistics column Optional Report configuration: report:
5. Sort configurations for report columns: reports:
6. Column configuration by Percent format: report:
7. Merge report to the left of the same dimension column before merging: after merging:

3.7 Related references (referrence Links)

• Template engine used in report sql: Velocity
• expression engine used in computed columns: Aviator
• Data sources in all examples: pm25.in, Aqistudy
• Chart controls: Echarts, Highcharts
• Front-end Report table and sorting-related JS plugin: tablesorter, DataTables

Design and develop an easy-to-use Web Reporting tool (support common relational data and Hadoop, hbase, etc.)