In daily work, when it comes to tables, calculations, charts and analysis of data, Excel must be the first choice.

However, I also see many friends puzzled by the difficulty of in-depth study of Excel and the efficiency of big data processing.

For example, a colleague was overwhelmed with nearly 10,000 pieces of data to process seven or eight dimensions. Another person hundreds of thousands of lines of data to the computer frequently crashed; Furthermore, as the multi-task lines cross, the sum of tasks increases exponentially…

It’s not that Excel can’t do these kinds of tasks; it can’t replace routine spreadsheet records and data calculations. However, when it comes to business reports for enterprise management, huge and complex data analysis, financial analysis and budget preparation, Excel is not good at it, because its data is not unified, there is no independent computing engine, data exist independently in each Excel file, data merger is very difficult.

For example, my daughter-in-law recently joined a new company as a data specialist for a brand store. Although I was in this industry before, I was not good at making data statements.

The colleague in charge of the handover is an old employee who has been working in this company for more than 3 years since graduation, and has been doing the report work. In order to make the handover more accurate, I asked my daughter-in-law to use the screen recording software to record everything she had to do every day.

Results found that this guy from the morning, began to ask the information department to data, and then use excel copy, paste, sum, percentage, vlookup, and then copy, paste, sum, percentage, vlookup, delete, adjust…… A few hours later, the first report came out…

After watching the video, I was devastated. It was insane. Well, this guy is really good, the speed is absolutely first class, at least it’s diamond V in LOL.

But I can not bear to daughter-in-law every day like this, and for me, as a computer major, as long as there is a database, these repetitive work is minute code/tools can be solved.

Given the number of changes in requirements, it was not feasible for my wife to change the code, so I looked for a ready-made reporting tool. After comparing SPSS, SAS, Cognos, Crystal, FoxTable and Finereport, the report software Finereport is determined. Built from mysql table, data import, production daily, weekly, monthly, all sorts of comparative reports began to break them one by one, finally in hand speed is not lower than the gold grade of me into after a week of effort, all finished, after a young man and manual could check table, make sure no problem, after install deployment to the daughter-in-law’s office computer.

A few days later, the young man resigned, from now on, the daughter-in-law to work only need to collect data to the person in charge of the store in the morning, and then spend a few minutes to import the database, and then click the mouse, generate all kinds of statements, and then…… Save it first, drink water, eat snacks, fruits, rest and surf the Internet.

So, in terms of efficiency is professional reporting tools can replace a lot of repetitive excel processing, for example FineReport, deployment information department after installation, can connect each business system data, will be regular business report, and weekly monthly reports that, to develop into a can automatically extract data and display the template, do one time, Just query each time.

Because this tool is mostly used for information department development report forms, many people may not understand! I will try to explain in simple and understandable language how this reporting tool is a technical tool that saves time and labor and has amazing visualizations that can replace Pivottables.

First, use FineReport to create a template, which can be used instead of multiple sheets of manual work Excel!

Why do you say so? First, let’s understand how FineReport tabulates

1. Tabulating principle of FineReport

You have to have data first to make a report. Where does the data come from? One is the data directly connected to the business system or database, and the data can be imported into Excel files. After the data is connected, we start to make templates, similar to excel tables, design display styles and calculation formulas, and finally display them on the Web.

However, unlike Excel, which deals with data one by one, finereport deals with data fields when making templates. Each cell in the template puts a data field, and the fields are expanded when displayed on the Web side. The expansion of cells in the FineReport report is directional. It can be vertically, horizontally or not.

The expansion of a cell is for a certain cell. When multiple cells are bound in the report body, the relationship between cells and cells follows the expansion based on the parent-child relationship. The data of the child lattice will be filtered and grouped according to the data of the parent lattice, and will be expanded with the direction of the parent lattice expansion.

2. Three types of report design, solve all daily report requirements, and reuse one table.

1) Ordinary template design, which is the most commonly used. Depending on the extension of the cell and the relationship between the father and son to achieve template effect, can be parameter query, fill in reports, chart design and so on. Such as cross reports, line reports, grouped reports, and so on.

2) Aggregate report design for irregular large reports. It is suitable for complex reports that display multiple independent modules in a template and aggregate several report blocks together.

3) Decision report design, namely form, is also dashboard, which is a common visual data report with various charts spliced together.

By dragging and dropping charts, the dashboard can adapt itself to the size of the screen. In most cases, it can also be made into mobile phone reports and large-screen visualization, which will be discussed in details below.

Here are some examples of common report formats:

crosstab

Line statements

Group report

Free report

3, parameters to achieve data query and filtering

How to query the report? In FineReport, by setting parameters binding data fields and filtering controls (check boxes, checkboxes, etc.), through the interface input query conditions to control the content and form of the report display, and then export and print.

Many people work on weekly reports, monthly reports, quarterly reports, etc. Some national companies have branches, stores, offices. Make these repetitive reports on a regular basis. FineReport allows you to centrally upload each piece of data to the database (fill in the report, more on that below). When making reports, do some commonly used dimensions “region”, “time”, “city” and so on as a query parameter control, similar to Excel screening, export. Basically, a weekly report template solves some people’s workload of 52 manual Excel reports a year.

4, provided

Data collection is an important step. Fill in is to input data to the database, used to collect data, just like you register the website submitted email, collection, user name, password, will be stored in the database in accordance with the format.

Template is also required to fill in the application, and the operation process is as follows:

The filling template is composed of controls, text controls, digital controls, password controls, single check boxes, web pages, files and so on. Controls correspond to data fields. With fill in, you can collect data and allow users to add, delete, or modify the database. For the sake of data quality and data security, the data can also be checked.

The biggest use of filling in the form is that it replaces Excel to collect data. After completing the template, the link of the template will be sent to the relevant person. After completing the data, it can be stored in the database, saving excel from passing to passing.

Second, not only reports, but also data visualization

Cool dashboards need to be presented via FineReport forms (decision reports). By dragging report block, chart block and various controls preliminary design interface.

Unlike common reports, common reports are integrated and cannot be partially refreshed. Forms are made up of components that can be refreshed within a component, that is, locally. Like dynamic large screen are global or local refresh, so need to use the decision report to achieve.

1. Decision-making report making process

Create a Form: FineReport supports creating a workbook, a common report template, and creating a form, both in design mode.

Drag in Components: FineReport forms support a number of different component types, including report blocks, TAB blocks, absolute canvas blocks, parameters, charts, controls, and more;

Define data sets: Define data sources for each component. Chart component data can come from data sets or cells.

Style the form: That is, set the display style of the form.

2. Data visualization and interaction

Now, in the era of data analysis, grid reports are boring, and visual reports are appreciated by leaders. Before many people asked how to do visualization, in fact, with FineReport’s own H5 chart, you can make a very cool visual dashboard. As mentioned earlier, FineReport is open and allows technical colleagues to write code for development. Therefore, third-party controls such as Echarts can also be connected to make charts during production.

3. Practical and powerful data maps

Finereport has very powerful map functions: basic map, GIS map, thermal map, big data flow map, etc., basically generated by one button.

GIS map is based on GIS map layer for data display, supports custom GIS theme style, and can be linked with data table drilling. Such as provincial drill to the city, county, street.

Third, summary

In terms of efficiency, the biggest benefit is to automatically generate reports from the data read from the database, and a type of report into a fixed style template, as long as the synchronization each time, automatic production of periodic reports, such as daily, weekly, monthly, quarterly and so on. Each time according to the conditions of query, Excel batch export/print, less manpower to use Excel statistics summary one by one.

And in operation, most functions of FineReport are encapsulated into modules, including write SQL fetch, summary filter filter, etc., basically have corresponding function keys. Like the famous balance sheet, writing code or complex SQL statements in the past is a bit of a death sentence, and if the SQL statement of the day goes wrong who can check it, if the data comes from multiple sources, it is completely impossible to get rid of.

Secondly, in terms of data processing speed, the performance partly depends on the database, partly depends on the FineReport line report engine. Compared with the stand-alone efficiency of Excel, hundreds of thousands of lines of data can be processed in seconds. In addition, enterprise databases have various built-in or third-party clustering solutions, which can increase data storage capacity almost indefinitely through the heap machine.