Report development
There are many examples where esProc assists reporting tools in data handling. Most of the time, the obstacles occur before the stage of presenting data through the reporting tool. In preceding examples for text-file handling and SQL-style computations, many are related to building a report. Other times the difficulties are associated with report layout, or are non-computing problems which can be solved through data-source handling. Here let’s look at how esProc works to assist reporting tools with their jobs.
Data-source and datasets
Dynamic data sources
In reporting tools, generally the data source a report uses is definite and the report parameters only specify the condition (the SQL WHERE clause) according to which a data set is generated, rather than define the data source. To define the data source of the report through parameters, most reporting tools provide API for writing a program. But this is complex.
esProc, which can work as the definite data source for the report meets the need easily. In the esProc script, we can use the parameter to connect to the desired data source to retrieve data and return.
| >A | |
| 1 | =${pds}.query(“select * from T where F=?”,pF) |
The parameter pds is used to pass in the data source name. This way it doesn’t matter whether the reporting tool supports dynamic data source connection or not.
The same method applies to a similar situation, where the report requires that the main report and the subreport use different data sources but the reporting tool only allows them using the same one. We make esProc the nominal common data source of the main report and the subreport, while the actual data source is determined by the parameter in the esProc script.
Dynamic datasets
With reporting tools, normally parameters, which are also the SQL’s parameters, are used to specify conditions for creating data sets. At times, instead of replacing the SQL with parameters, we want to make part of it, the whole WHERE clause for instance, passed in through one parameter in order to gain a more flexible query condition.
Some reporting tools can make it using macros. But others would resort to their APIs to change the data set defined for the report template through recoding. That is not smart at all. esProc, however, is extremely neat:
| A | ||
| 1 | =”select * from T” + if(where!=””,” where “+where,””) | Append the WHERE; do not append it if there’s no such a condition |
| 2 | =db.query(A1) |
Despite the support of macros, these reporting tools cannot compose SQL easily. For example, to perform aggregate based on a passed-in list of fields, we should add sum() to each of these fields. But average reporting tools haven’t direct means to handle string concatenation and have to use API, or do it beforehand in the main program. This is also easy to handle in esProc.
| A | ||
| 1 | =sums.array().(“sum(“+~+”) as “+~).string() | Change a, b to sum(a) as a, sum(b) as b respectively |
| 2 | =db.query(“select G,”+A1+” from T group by G”) |
Controlled data retrieval
Because the memory capacity is limited, we make the reporting tool retrieve 10,000 rows at most each time. If the rows retrieved are less than the maximum number, we need to supply an extra row marked with “Continuing” to show whether the retrieved rows have the regular number. Usually reporting tools are only able to retrieve the fixed number of rows, they need to use API to tackle this kind of controlled retrieval with complicated code.
esProc issues the following code to handle the controlled data retrieve with ease:
| A | B | ||
| 1 | =db.cursor(“select * from T”) | =A1.fetch(1000) | |
| 2 | if A1.fetch@0(1) | >B1.insert(0,”Continuing”) | Mark the irregular retrieval |
| 3 | >A1.close() | return B1 |
Report layout
Horizontal multi-column layout
Most reporting tools support vertical multi-column layout, but few can handle horizontal multi-column layout. In view of this, we can use esProc to first prepare the data set.
| A | B | C | |
| 1 | =db.query(“select a,b,c from T “) | ||
| 2 | =A1.step(3,1) | =A1.step(3,2)|[null] | =A1.step(3,3)|[null] |
| 3 | =A2.derive(B2(#).a:a2,B2(#).b:b2,B2(#).c:c2,C2(#).a:a3,C2(#).b:b3,C2(#).c:c3) | ||
This piece of code joins the 3-column data set (a, b and c columns) into a 9-column data set (a, b, c, a2, b2, b3, a3, b3 and c3) with the number of rows reduced to one-third of the source data set. After that according to the normal way of report building, the reporting tool can create a horizontal 3-column layout.
Supplying empty rows
In printing a report, each page should be filled up. If there are not enough rows on the last page, empty rows need to be supplied. Many reporting tools lack related functionalities. It’s not easy to supply these rows to the data set in SQL, whereas esProc can easily get it done.
| A | ||
| 1 | =db.query(“select * from T”) | |
| 2 | =pn-A1.len()%pn | Calculate the number of rows to be appended |
| 3 | =A1|if(A2!=pn,A2*[null]) | The data set with empty rows appended |
The number of rows for each page will be passed in through parameter pn.
Inter-column calculation for the cross table
According to the following report, calculate the product sales amounts for a specified year (defined by parameter) and its previous year, as well as the growth rate.
| Product | 2014 | 2015 | 2015-inc |
| AC | 100 | 120 | 20% |
| TV | 200 | 210 | 5% |
| … | … | … | … |
The data structure of the source table is like this – product, year and amount.
The resulting table seems like a cross table, where the last column, the growth rate, involves inter-column calculation. The average reporting tool only provides aggregate operations (such as sum or average) for columns of a cross table. If we don’t use a cross table, we need to transpose data first, during which we need to get column names dynamically. But the required functions don’t exist in reporting tools.
Now we use esProc to calculate the growth rates and append to the source table, and then present data with a cross table.
| A | B | ||
| 1 | =db.query(“select product,year,amount from T where year=?-1 or year=? order by product,year”,Y,Y) | Y is the parameter specifying the year | |
| 2 | for A1.group(product) | >A1.insert(0,A2.product, string(Y)+”-inc”, string(A2(2).amount/A2(1).amount-1,”#%”)) | Calculate the growth rates and insert them to the source table |
| 3 | return A1 | ||
Computational process control
Avoiding hidden cells
Find the big clients who contribute half of the sales amount, as well as the sales amount of each of them, their numbers and average sales. The data structure of the table is simple – client and amount.
The steps for performing the computation are obvious. First sort clients by sales amounts and calculate the grand total; then find the eligible records in the client list by accumulating the amounts in order to half of the total.
But it’s hard to implement the algorithm automatically in reporting tools, which perform state-style computation, that is – after all expression are written, they automatically identify the relationship between them and determine the computing order. To get control of the computing order, we need to have an appropriate relational pattern for cell reference, during which the intermediate results will be presented in hidden cells. It’s not easy even for the reporting tools with strong computing power.
esProc will first prepare the desired data source and then create the report with it. The process is much clearer.
| A | B | |
| 1 | =db.query(“select client,amonut from Sales order by amount desc”) | |
| 2 | =A1.sum(amount)/2 | =0 |
| 3 | =A1.pselect((B1+=amount)>=A2) | return A1.to(A3) |
Though several lines of code are needed to express the algorithm just mentioned in an esProc script, they are natural, easy to write and understand. The script will only return a data set of records of the eligible big clients, according to which any reporting tool can build the report easily as they always do.
Intermediate steps with position changing
The intermediate steps involving the change of the positions of cells in some algorithms cannot be expressed with hidden cells, even if we’re willing to go to the trouble and to sacrifice the computing efficiency.
For example, a relatively common scenario is to sort grouped records by aggregate values but many reporting tools cannot handle it directly. Mostly reporting tools does sorting before grouping (because this is more frequently seen), but not vice versa. We can easily implement it in esProc with flexible code, which can express any requirement involving position changing.
| A | ||
| 1 | =db.query(“select …”) | Retrieve data |
| 2 | =A1.group(G).sort(~.sum(A)) | Group by G and Sort by the aggregated A |
| 3 | =A2.conj() | Concatenate into a single-layer set and return |
The script will return a result set sorted by aggregate values, so reporting tools will simply group it and calculate the aggregate values again.
Likewise, it’s almost impossible for reporting tools to sort the big clients in the above subsection by names, rather than by sales amounts. Yet with esProc we just need to change B3 to A1.to(A3).sort(client).