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

Solve several difficult problems of report making by report tools such as Birt

In the previous article, "how Birt implements irregular month statistics", we explained the detailed process of how to help Birt make such reports. In this article, we continue to discuss several similar production problems, and focus on how to write aggregator SPL scripts, without repeating the steps of introducing SPL in Birt.

1. Intra-group cross-line calculation

Intra-group cross-row calculation means that when calculating the value of a calculated column in a row, the data referenced to other rows in the group is needed for calculation. For example, the following example:

The library table sample has three fields, of which id is a grouping field. A grouping table needs to be designed and grouped using id. The detail field is v1PowerV2 and the calculation column crossline, where the algorithm of crossline is the sum of v1 and v2 of this record plus the sum of v1 and v2 of the previous record in this group. The sample source data is as follows:

Idv1v2112123211222333

The final result of the report to be shown is as follows:

The SPL code for the aggregator is as follows:

A1=connect ("demo") 2=A1.query ("select *, 0 as crossline from sample") 3 > A2.group (id) .run (~ .run (v1+v2+v1 [- 1] + v2 [- 1]: crossline)) 4 > A1.close () 5return A2

A1 connects to the database

A2 queries the database and generates an extra column of constants for standby.

A3 is grouped according to id, and the calculated column crossline is modified in each set of data, and finally merged, where v1 [- 1] and v2 [- 1] are the unique writing methods for locating the fields in the previous row of records.

A4 shut down the database

A5 returns the calculated result dataset in A2 to the report tool

two。 Cross-library data source

The data for making reports often come from a variety of data sources, such as different physical databases, text files, Excel files and so on. These data often need to be related to each other in the report.

The report tool itself can fetch data from multiple data sources, but it will be difficult to carry out association operation, or the operation performance is very poor. On the other hand, the workload is generally very large when it is programmed by the developer to do the association operation. And the aggregator SPL can help a lot at this point.

In the following example, the order table orders and the order schedule orderDetail data come from two different databases, and the join operation is performed between the two. The data of the two tables are as follows:

Finally, the report results you want to show are as follows:

The SPL code for the aggregator is as follows:

A1=connect ("db1") 2=connect ("db2") 3=A1.query ("select orderID,customer,orderDate from orders") 4=A2.query ("select orderID,productID,price,mount from orderDetail order by orderID") 5 > A1.close () 6 > A2.close () 7=join@1 (A32=connect orderID and A42=connect orderID) 8=A7.new (# 1.orderID design 1.customer.orderID presentation 2.productID presentation 2.productID presentation 2.priceID 2.mount) 9return A8

A1 connects to database 1

A2 connects to database 2

A3 query order table data

A4 query order schedule data

A5A6 closes database connection

A7 uses orderID of A3 and orderID of A4 as primary keys to left join. The connected result set has two fields, the first field is the record of A3, and the second field is the record of A4.

A8 uses the fields of the two fields in A7 to form a new dataset, that is, the desired result

A9 returns the dataset of A8 to the reporting tool

This example only demonstrates the left join of two data sources. In fact, SPL can do any data operation that a relational database can do, such as various join, union, filtering, grouping, sorting and so on.

3. The field is split into records

In this example, the database table data has two fields, and the ANOMOALIES field is multiple strings separated by spaces. We need to split the ANOMOALIES into multiple strings by spaces, and use each string and the original ID field to form a new record. The source data is as follows:

IDANOMALIES3903B1 D1 CAT13904D7 D2 B1 CAD4

Finally, the report results you want to show are as follows:

The SPL code for the aggregator is as follows:

A1=connect ("db") 2=A1.query ("select ID,ANOMALIES from data") 3=A2.conj (ANOMALIES.array ("") .new (A2.ID2=A1.query ID description anomalites) 4 > A1.close () 5return A3

A1 connects to database 1

A2 query data table data

A3 splits the ANOMALIES field value into spaces and forms a new record with the original ID

A4 close database connection

A5 returns the dataset formed by A3 to the reporting tool

4. Dynamically insert subtable fields into the main table

In this case, the database table dColThread is the primary table and the primary key is tID. DColQuestion is a child table, and the foreign key is tID, as follows:

DColThread

TIDApplicationNameUserPhoneDeclineA01mfcBill+700000001A02mfcJohn+187612212A03javaJack+80140012316A04mfcTim+0086131331234A05dbJohn+187612218

DColQuestion

QIDtIDstatus1A01yes2A01no3A01yes4A02yes5A03no6A04no7A04no8A05yes

The report needs to query the main table according to ApplicationName and present the data in the form of a list. As you can see, in the child table, there are multiple status field values for each record in the main table, but no more than 5. We need to horizontally arrange these records in the child table and insert them between the Phone and Decline fields of the main table, which are named QuestionNo1 and QuestionNo2 in turn. QuestionNo5 . At the same time, if a column of data is empty, the column is not displayed. The final table looks like the following figure:

To prepare the data with the aggregator, the SPL code is as follows:

AB1=connect ("db") 2=A1. Query ("select * from dColThread t dColQuestion Q where t.tID=q.tID and t.ApplicationNameplate?", arg1) 3 > A1.close () 4=A2.group (tID) 5=create (ApplicationName,User,Phone,QuestionNo1,QuestionNo2,QuestionNo3,QuestionNo4,QuestionNo5,Decline) 6for A4=A6. (status) | [","] 7

= A5.record (A6.ApplicationName | A6.User | A6.Phone | B6.to (5) | A6.Decline) 8return A5

A1 connects to the database

A2 executes SQL and fetches the data associated with the master child table. Arg1 is from the report parameters. If arg1= "mfc", the calculation of A1 is as follows:

A4 is grouped according to tID. Each group is a master table record and its corresponding child table record, as shown in the following figure:

A5 create an empty 2D table according to the structure of the list in the report.

A group in A6 loop A4, inserting one record into A5 at a time. In the body of the loop, A6 can be used to refer to the loop variable, and # A6 can be used to reference the loop count.

B6 takes the field value of status in the current group and complements at least 5 records.

B7 appends new records to A5. At the end of the loop, A5 is as follows:

A8: returns the result to the report.

The task of hiding empty columns is left to BIRT, and the list table is designed. The template is as follows:

Should be hidden if the QuestionNo column is empty. There are many ways to hide dynamically, one of which is introduced here. For QuestionNo5 (other columns are similar), you can first use the following script in the onFetch method of dataSet:

If (reportContext.getGlobalVariable ("T5") = = null) {

ReportContext.setGlobalVariable ("T5", row.QuestionNo5)

} else {

ReportContext.setGlobalVariable ("T5", reportContext.getGlobalVariable ("T5") + row.QuestionNo5)

}

Then use the following expression in the Visibility property of the QustionNo5 column: BirtStr.trim (reportContext.getGlobalVariable ("T5")) = ""

After previewing, you can see the report results:

5. Summary

Through the above examples, we can see that the data preparation work which is difficult to deal with is often encountered in the report making, and it can be solved perfectly with the help of the aggregator. This is because the aggregator provides a complete data source connection function, which can connect and take numbers from a variety of common data sources in the market. At the same time, SPL also provides a rich function library, which can easily carry out all kinds of data association operations outside the library.

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.