Complex reports where is the complexity?

report developers will always encounter more complex reports, such as the number of such reports are usually very small, but the development time spent a lot, and sometimes become problematic. This article will discuss the complexity of these complex reports in what aspect, and what method to solve, I hope to improve the efficiency of the development of the report is helpful.

previous complex reports are primarily complex in the front-end:

N cell Merge, slash header.

N font styles vary according to the size of the data.

N the calculation between any cells. For example, for a few special detail data, calculate the ratio of their value to the summary.

N There are regular calculations between the patches. For example, the horizontal header is the product classification, the longitudinal head is composed of two kinds of grouping: three-tier mechanism grouping, single-layer annual grouping, the intersection to show a category of products in different groups of sales.

N Irregular grouping. For example, the state and state offices at intervals show the state's product sales (the total can not be repeated calculation of the state Government sales).

reporting tools after 10 years of development, most of the front-end challenges have been properly resolved, such as Style report,Runqian reports,QlikView, Tableau, each of which solves these challenges in different ways.

Now the report is mainly complex in the backend, that is, the calculation of the data source.

N Complex business logic. For example: Find salespeople with three consecutive months of sales growth exceeding 10% , showing their sales and sales. Another example: Find the customer who purchased all the item in the parameter listand show the account balances of those customer .

N cross-Library calculations. For example: An enterprise should be based on performance and basic salary accounting for different types of employee real wages, base pay is stored in the financial management software MSSQL database, and performance score is stored in the performance appraisal system of the Oracle database, Please convert the performance score to the salary amount and present in the report. Another example: A chain stores each store has its own database, the headquarters need to use a report to present the aggregated results of these data.

N calculation of non-database data sources. For example: According to the data in Excel, from a number of stocks to pick up 5 consecutive days of the stock. Another example: according to the data in the log, show each user in the specified time period of each product's attention time.

N multiple sources are merged into a single source. For example,BIRT,Crystal report,Japserreport and other reporting tools are not convenient for multi-database support, often need to write a user-defined data source to merge multiple sources into a single source.

The Common reporting tool is only responsible for the data to be taken out, does not involve the generation of back-end data, the report developer must find a way to solve the above problems, so the complexity of the report backend is always the biggest obstacle to report developers, but also complex report is the main cause of complexity. At the same time, the modern report is simple and easy to read, users of complex style requirements are gradually reduced, and the data itself is more concerned about the focus of the complex report has already shifted from front-end to the backend data source. In fact, most of the complexity of the front end can be solved by the backend, for example, there are regular calculation and irregular grouping between slices, which makes the calculation of the report data source more important.

solve complex data source calculations in a report, you can use SQL\SP, intermediate database, custom data source, etc. three kinds of ways.

SQL\SP In theory, complex business logic can be solved, but on the one hand is limited to a single database, for other situations, such as: Non-database data sources, cross-Library computing, multi-source consolidation as a single source. On the other hand, complex business logic is not easily implemented by ordinary report developers, and often requires more experienced programmers to be deployed. Thus,sql\sp can solve the problem is limited, the requirements of personnel is high, the shortcomings are very obvious.

Intermediate databases can be used to implement cross-library computing, which is to load heterogeneous database data into the same database, and then use the reporting tools to access the consolidated view in the intermediate database. Intermediate databases typically need to be configured separately, adding additional cost burdens. In addition, the intermediate database has a loading process, the real-time comparison is poor. If you want to implement incremental real-time loading, you need to set up a scheduling task or add triggers and timestamps to the source library, and write more complex data load scripts, obviously, additional development effort will increase significantly. Not only that, many commercial software database is not allowed to modify, can not add triggers and timestamps, the performance of the upgrade can not be discussed. Thus, the shortcomings of the intermediate database is high cost, the development of large workloads, real-time and performance is not guaranteed.

The calculation of non-database data source can also adopt the method of intermediate database, the advantages and disadvantages are similar, the difference is mainly in real-time and cost. First, non-database data sources are difficult to add triggers and timestamps, and real-time computations cannot be achieved. Second, many non-database data sources of large amounts of data, will occupy valuable database storage space, higher cost. For example, in front of the log data to calculate the attention time of the example, the business site daily logs have tens of millions of, a year of data may be a few terabytes , intermediate database has to face frequent escalation of suffering. Large data volumes also have a large impact on performance, and to improve the performance of the database, it is necessary to adopt parallel databases, which is very expensive.

Custom data Sources are the interfaces that most reporting tools provide, and it is convenient to solve the problem of multi-source merging as single source, the most common of which is user-defined Java beans. User-defined Java beans allow developers to combine heterogeneous databases, heterogeneous data sources, and semi-structured data sources into a single data source in high-level languages, with the advantage of being flexible and free to do everything, but with the same drawbacks. Unlike SQL/ESPROC/R , a professional data computing language,JAVA and other high-level languages lack of structured computing functions, developers first to implement filtering, grouping, aggregation, ranking, sorting, unique values, association algorithms and other large number of underlying functions to be calculated, The development effort is enormous. For general and even simpler algorithms, it is extremely difficult to implement in a high-level language.

The R language can also be used as a custom data source. Its advantage is that the library function is rich, can be mixed computing, the disadvantage is that there is no JDBC interface, performance and stability is poor, so few people in practice use it to solve the report data source of complex calculations.

Run-dry company development of the collection report not only can be very good to achieve the complex presentation of the report problem, but also can be very good to complete the report complex data source computing tasks.

The collection report has a built-in programming language collectorEsproc, is a powerful tool for customizing data source programming. and theSqlSimilarly, a collection of reportEsprocis a professional data computing language with rich structured data computing functions, so it is easy to solve the first type of puzzles: complex business logic. and theRsimilar to the language, the collection report passesEsprocyou can directly do database, text file,Excel, semi-structured data hybrid computing, without intermediate library staging, enables high-performance, low-cost cross-Library computing and non-database data source computing. EsprocFor application developers, the syntax is simple, the code is easy to write, the interactivity is strong, the debugging function is perfect, so the technical requirements for developers are low. A collection report can beJdbcmethod CallEsproccan also be called directly by defining a "collector DataSet". Set up report built-inEsproca stand-alone parallel computing engine that can take full advantage of multipleCpuor moreCpuNuclear computing resources, excellent performance.

The following is an example of a method of programming a custom data source for a collection report, such as the example in the previous cross-Library calculation, "Calculating real wages in terms of performance".

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/B2/wKiom1VQQbeSbrrpAAHphPWFsjk182.jpg "title=" 2015-05-11_134355.jpg "alt=" Wkiom1vqqbesbrrpaahphpwfsjk182.jpg "/>

l correlation calculation for cross-Library: A5

l Complex business logic: A6-c9

l structured data calculation functions: A4,a10-a13

l return the output result: A14

The collection report calls this collector program with the following configuration:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6C/AD/wKioL1VQQ1aD7Dp_AAEk55npO-4123.jpg "title=" 2015-05-11_134438.jpg "alt=" Wkiol1vqq1ad7dp_aaek55npo-4123.jpg "/>

DS1 receives The result set returned by Esproc,test.dfx is The program file name of Esproc.

This article is from the High performance report data calculation blog, so be sure to keep this source http://report5.blog.51cto.com/8028595/1650225

Complex reports where is the complexity?