Dynamic Data source implementation for reporting tools

Sometimes we need to dynamically specify a data source with parameters, or to connect a multiple data source to a single data source, or to dynamically pass in a data source name to a subreport, table control. For such requirements, reporting tools often use high-level languages to implement or sacrifice security to reduce complexity, especially Birt, Jasper, and other single-source reports.

Use the free collector to compensate for this deficiency. The collector encapsulates a rich structure of computational functions, supports dynamic parsing of expressions, supports mixed computation of multiple data sources, and can implement dynamic data sources by writing simple scripts. The collector also provides an easy-to-use JDBC interface, which the report tool executes as a database stored procedure, passing in parameters and using JDBC to get the returned results.

The integration structure of the collector and report tools is as follows:

The following is an example of the basic process by which the collector dynamically switches data sources based on parameters:

data Sources myDB1 and oradb point to different databases, two libraries have the same structure in the table Sorder, the report needs to dynamically connect the data source according to the parameters, query and display sorder in the amount greater than 1000 of the order.

some of the data in MyDB1 Sorder are as follows:

Some of the data in Oradb Sorder are as follows:

The Collector code:

=${psource}.query ("select * from Sorderwhere amount>?", Pamount)

Psource, Pamount are report parameters, where Psource represents the data source name, ${...} Represents the resolution of a string or string variable to an expression, Pamount represents the order amount.

when psource= "MyDB1", the A1 calculation results are as follows:

when psource= "oradb", the A1 calculation results are as follows:

the report tool invokes the collector script in the form of JDBC, just like calling a stored procedure in a normal database, such as: Call script file name ( parameter 1 ...). Parameters N). The return result of the collector participates in the report calculation in the form of a normal data set. For specific usage, refer to the following documents: Jasperreport integration of the integrator and application, integration of the Birt with the integrated integration of the collector and application.

as a professional report data source Tool, the collector can also achieve more calculations, the following examples.

Multi-source data join post calculation

Sales is a table in the MySQL database that stores multiple orders per day for several salespeople, where field Sellerid is the salesperson number. EMP is a table in the MSSQL database that stores the salesperson information, where the field Eid is the salesperson number. Now it needs to be shown in the report: Order number, date, amount, salesperson's name, department name, if the order date is in the last n days (say 30 days) or the order belongs to a number of interested departments (such as Markeding and finance). Some of the source data are as follows:

Library Table Sales

Library Table emp

The Collector code:

A1,A2: Querying the database, myDB1 and myDB2 are straight to MySQL and MSSQL respectively.

A3: Replace the Sellerid field in the A1 with the corresponding record in the A2, and the associated fields are Eid. A3 evaluates to the following (the blue font indicates that the data item contains subordinate members):

when the corresponding record is not found in the A2, the function switch retains the record in A1 by default, and the corresponding Sellerid appears empty, and the effect is similar to the left connection. If you want to make an internal connection, you should use the option @i, such as:[email protected] (Sellerid,a2:eid)

A4: Filter The results of the association, the 1th condition is that the order date is in the last n days (corresponding to the parameter day), the expression is orderdate>=after (date (now ()), days*-1). The 2nd condition is that the order belongs to a number of concerned departments (corresponding to parameter depts), and the expression is Depts.array (). POS (sellerid.dept). operator | | Represents a logical relationship "or".

The function after can calculate the relative time, and the function array can split the string into a collection by delimiter. Function Pos can find out where members are in the collection. Sellerid.dept represents the Dept field for the record that corresponds to the Sellerid field.

Days and Depts are from the parameters of the report, if you enter 30, "Marketing,finance", then the results of A4 are as follows:

A5: Get the fields from the A4 that the report requires, and the result is as follows:

Result set Union

The result set DS1 and DS2 have the same structure, from MySQL and text files, which need to be ds1 and ds2 vertically and then presented as cross-tables. The source data is as follows:

The Collector code:

A3: Vertical stitching of two datasets. The report tool simply renders a simple crosstab based on a single dataset.

Master report multiple data sources

for a single data source report, if the primary and child tables in the same report use different data sources, you need to explicitly pass the database URL, or merge different data sources with the Java class, which is less secure and the code complex. The use of the collector is a convenient way to solve such problems, as illustrated in the following example.

Design a master report that displays the order information for each employee by salary range, the main report data from the table EMP (MySQL database), and the subreport data from the table sales (MSSQL database). Some of the source data are as follows:

The Collector code:

empesproc.dfx (The script file is used for the main report)

A1: Query the MySQL database's table emp by salary range.

salesesproc.dfx (This script file is used for subreports)

A1: Find the appropriate order from the sales table of MSSQL by employee ID. If eid=1, the A1 evaluates as follows:

As you can see, different data sources can be combined into a single data source by the collector, and the master report will need to call a different collector file to achieve this requirement.

Some report systems will have a variety of data sources, the data sources used by each report are different and often changed, difficult to manage, using the single data source of the collector can reduce the management difficulty.

Similarly, a single data source can also address the problem of multiple data sources for subreports , that is, there are multiple subreports (or table controls) in the report, and each subreport uses a different data source.

Masters Table Dynamic Association

The primary table-related child tables are distributed across multiple databases, requiring reports to render results that are dynamically associated with those data sources. Such requirements can be easily implemented with the use of a collector, such as:

The primary table org is in the data source master, and the corresponding sub-table for each record in the org is in a different data source, such as org.org_id= "org_s", the corresponding child table of this record is the user table of the data source S_odaurl, Org.org_ When id= "org_t", the corresponding child table of this record is the user table in the data source T_odaurl. There are more than two sub-tables, the name is user, and the main table needs to be dynamically associated and rendered in the report. The logical relationship is as follows:

The Collector code is as follows:

A1: Executes SQL, fetching data from the Org table of the data source master. Arg1 is a parameter from the report, arg1= "ORG" when the A1 evaluates as follows:

A2: Loops the records in the A1 sequentially, correlates each child table dynamically, and merges the associated results in B2. The set is naturally indented to represent the scope of the loop statement, that is, the b2-b7, which can be used in the loop body to refer to the A2, and can be used to refer to the loop Count #a2.

B2: Calculates the data source name of the corresponding child table based on the org_id field of the current record. For the first cycle, the B2 evaluates to "S_odaurl".

B3: Explicitly connect to the data source by name.

B4: Query the data in the user table conditionally.

B5: Added three columns in child table B4, data from the main table. For example, for the first cycle, the B5 results are as follows:

B6: Merges the results of B5 calculations into B1, operator ' | ' Equivalent to the function union. At the end of the loop, B1 stores the complete data required for the report, as follows:

B7: Explicitly closes the data source connection.

A8: Explicitly return B1 to the report tool (the last cell is returned by default)

Display data with different time accuracy according to parameters

The report requires a line chart to show recent sales changes. UnitType is the report parameter, represents the time precision, when unittype equals "hour", needs to show nearly 1 hours every five minutes of sales, when unittype= "Day", need to show nearly 1 days of sales per hour, when unittype= "Week", We need to show sales in the last 1 weeks. The data originates from the Orders table, the field amount is the order amount, and T is the order occurrence time.

The Collector code:

A1: An empty result set used to store the time series generated by the B2-B4.

a2-b2: Generate different time series according to the report parameter UnitType, B2 can generate 12 time points in nearly 1 hours, each time interval 5 minutes, B3 generate the time point within nearly 1 days, B4 generate nearly 1 weeks of time point.

A5: Loop A1, each time a period of sales, "~" represents the current member of A1, "~[-1" represents the previous member. When Unittype= "Day", a single field result set of 12 records is generated.

A6: Pass the A5 through JDBC to the report. It can then be presented as a general statistical chart method.

Dynamic Data source implementation for reporting tools