Shulou(Shulou.com)06/03 Report--

In "how to display million-level large list reports in seconds", we introduce that no matter RDB or non-RDB, moistening reports can achieve second-level massive large list reports through asynchronous threads (hereinafter referred to as large reports). In the actual business, in addition to query details, it is sometimes necessary to show full summary data, or query grouping details and calculate grouping summaries. This article will introduce the development methods of these large reports with summarization and grouping.

Large report with summary

Calculating the summary value in a large report is different from the summary of the conventional report based on the data within the report, because the large report uses asynchronous threads, so it cannot be summarized through the data in the report (because only part of the data can be obtained at a time). The summary value can only be calculated and presented to the report during the data processing phase.

We still use the example of the SQL source big report in "how to do a million-level list report in seconds," and now we need to display the order summary (order quantity, total order, total freight) on the last line of each page.

Make report template

Add a dataset ds2 to calculate the summary value:

The last row of the report is rendered with ds2 summary values. Here, in order to contain summary data on each page, set the last line property to the end of the report.

Of course, the performance of large reports with summaries is strongly related to the efficiency of SQL execution that calculates summary values, so you may find that large reports with summaries are slower than simple query details.

Grouping large report

In the actual business, it is often not enough to simply present a large list of reports, and sometimes it is necessary to group a large amount of data to present summary and details. Next, let's take the order data as an example to query the details of the grouping by region and the summary of the order amount.

Demand analysis

First of all, reports that need to present grouping details cannot be aggregated on the data source side. For example, grouping details are not included after SQL aggregation, unless another query is spliced, but two queries obviously have a serious impact on performance. Therefore, we need to group and aggregate the detail data on the application side as far as possible.

Then the problem arises, as the massive data is to be presented by batch reading, how to ensure that each batch of packet data read is complete? If the packet data is incomplete, the result of packet aggregation is obviously incorrect. Therefore, in addition to ensuring that aggregation can be achieved on the application side, it is also necessary to ensure the integrity of packet data when reading data in batches.

The following is the process of drying the report and combining the aggregator (dataset) to realize the grouping of large reports.

Report data preparation

We need to write an aggregator SPL script for data preparation. Here, we will write two SPL scripts that query the grouping details and calculate the summary, and return the result set for the report in batches through cursors.

SPL script 1:group-detail.dfx

Set script parameters:

Write a SPL script:

A

B

C

one

= connect ("db")

two

= A1.cursor@x ("select shipper region, order ID, customer ID, order date, freight, order amount from order where order date > =? and order date

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.