A Standard Way of Realizing Dynamic Data Sources for Report Creation

Sometimes you need to reference data sources dynamically through a parameter, merge data sources into one, or dynamically pass data source name to a subreport/Table control. Often reporting tools - especially those with support for single data source, such as BIRT and Jasper – have to use a high-level language to accomplish these requirements, or trade security for reduced complexity.

esProc encapsulates rich functions for handling structured data, as well as supports dynamically parsing expressions and handling multiple/heterogeneous data sources, and realizing dynamic data sources with simple scripts. Therefore, it is suitable for serving as the computing tool for preparing data needed for building reports. The reporting tool executes an esProc script as it executes a database stored procedure, passes parameters to the script and gets the returned esProc result through JDBC. Learn more from How to Use esProc to Assist Reporting Tools.

Now let’s look at the typical problems about dynamic data sources and their esProc solutions:

Switching between data sources dynamically

myDB1 and oraDB are data sources that point to different databases. Each holds a sOrder table with the same structure. The report requires connecting to data sources dynamically via a parameter, querying sOrder for orders whose amounts are greater than 1,000, and displaying them.

Below is a selection from the sOrder table in myDB1:

OrderID	Client	SellerId	Amount	OrderDate
1	WVF	5	440	2009-02-03
2	UFS	13	1863	2009-07-05
4	JFS	27	671	2009-07-08
5	DSG	15	3730	2009-07-09
6	JFE	10	1445	2009-07-10
7	OLF	16	625	2009-07-11
8	PAER	29	2491	2010-07-12
9	DY	20	518	2010-07-15
10	JDR	17	1120	2010-07-16

Here is a selection from the sOrder table in oraDB:

OrderID	Client	SellerId	Amount	OrderDate
101	WAN	22	396	2010-11-07
102	SPL	15	142	2010-11-08
103	LA	23	713	2010-11-11
104	ERN	5	5678	2010-11-11
105	FUR	28	154	2009-11-12
106	BSF	27	10742	2009-11-13
107	RHD	4	569	2009-11-14
108	BDR	12	480	2010-11-15
109	OFS	17	1106	2009-11-18

esProc script:

=${pSource}.query("select * from sOrder where Amount>?",pAmount)

Both pSource and pAmount are report parameters. pSource represents the data source name; ${…} indicates parsing a string or a string variable into an expression. pAmount stands for the order amount.

When pSource=“myDB1”, A1 has the following result:

When pSource=“oraDB”, A1 gets this result:

Performing a multi-data-source pre-join

The mySQL database stores a Sales table holding orders from different sellers per day. Its SellerID field contains seller numbers. In MSSQL database there is an emp table of seller information in which EID field contains seller numbers. Create a report to display order numbers, order dates, order amounts, seller names and their departments, based on the condition that the orders should be within the last N days (say 30 days) or belong to certain important departments (say Marketing and Finance). Below are selections of the original tables:

Database table sales

OrderID	Client	SellerId	Amount	OrderDate
1	WVF Vip	1	440	2014-11-03
2	UFS Com	1	1863	2015-01-01
3	SWFR	2	1813	2014-11-01
4	JFS Pep	2	671	2015-01-01
5	DSG	1	3730	2015-01-01
6	JFE	1	1445	2015-01-01
7	OLF	3	625	2015-01-01
8	PAER	3	2490	2015-01-01

Database table emp

EId	State	Dept	Name	Gender	Salary	Birthday
2	New York	Marketing	Ashley	F	11001	1980-07-19
3	New Mexico	Sales	Rachel	F	9000	1970-12-17
4	Texas	HR	Emily	F	7000	1985-03-07
5	Texas	R&D	Ashley	F	16000	1975-05-13
6	California	Sales	Matthew	M	11000	1984-07-07
7	Illinois	Sales	Alexis	F	9000	1972-08-16
8	California	Marketing	Megan	F	11000	1979-04-19
1	Texas	HR	Victoria	F	3000	1983-12-07

esProc script:

A1, A2: Database queries. myDB1 and myDB2 point to MySQL and MSSQL respectively.

A3: Replace A1’s SellerID field with the corresponding records in A2 according to the key field EID. The result is as follows (the data items in blue contain sub-members):

By default, when there is not a corresponding record in A2 for a SellerID value, switch function retains the A1’s record while the SellerID shows an empty value. The effect is like a left join. Use @i option if you want to perform an inner join. The code is A1.switch@i(SellerId,A2:EId) .

A4: Filter on the result of join. The first filtering criterion is that orders are within the last N days (this corresponds to parameter days), whose expression is OrderDate>=after(date(now()),days*-1). The second one is that orders belong to several important departments (this corresponds to parameter depts), whose expression is depts.array().pos(SellerId.Dept). The operator || denotes the logical OR operation.

after function calculates the relative time duration. array function splits a string into a set using delimiters. pos function locates a member in a set. SellerId.Dept means Dept field in the record corresponding to SellerID field.

Both days and depts are parameters transferred from the reporting tool. Suppose their values are respectively 30 and “Marketing,Finance”, then A4’s result is as follows:

A5: Get the fields of interest from A4. Here is the final result:

Combining result sets with union

Result sets ds1 and ds2 have the same structure, but they come from different data sources – MySQL and a text file respectively. Now concatenate them and display the result in a cross table. Below is the original data:

ds1	ds2
id    name        time 1    name1        2010-07-22 11:01:02.903 2    name2        2010-07-22 11:01:02.903 3    name3        2010-07-22 11:01:02.903	id    name        time 1    t2_name1    2010-07-22 11:01:02.903 2    t2_name2    2010-07-22 11:01:02.903 3    t2_name3    2010-07-22 11:01:02.903 1    t3_name1    2010-07-22 11:01:02.920 2    t3_name2    2010-07-22 11:01:02.920 3    t3_name3    2010-07-22 11:01:02.920

esProc script:

A3: Concatenate the two data sets. The reporting tool’s work is just to create a simple cross table based on the resulting one data set.

Handling different data sources for main report and subreport

For a reporting tool that can support only one data source, if the reporting requires different data sources for the main report and the subreport, it needs to pass in the database URL explicitly or use Java classes to combine the different data sources into one. The former approach is vulnerable to security problems and the latter one produces complicated code. esProc, however, is able to cope easily. Here is an example.

Build a report with a subreport to display order information of sellers whose salaries are within a certain range. The main report’s data source is an emp table (in MySQL database) and the subreport’s data comes from a sales table (MSSQL database).

esProc scripts:

empEsProc.dfx（for the main report）

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

salesEsProc.dfx（for the subreport）

A1: Rrtrieve orders records from the sales table in MSSQL database according to employee IDs. Suppose eid=1, the result would be:

You can see that the two data sources have been joined into one source with esProc. The reporting tool just needs to call the corresponding esProc script for the main report and the subreport.

Some reporting systems do support multiple data sources. But it is hard to handle reports with different and changeable data sources. In that case, using esProc to generate a single data source can make the handling work easier.

For the same reason, another reporting problem can be solved through esProc’s single-data-source solution. That is the “multiple subreports, multiple data sources” problem, which means there are many subreports (or table controls) within one report and each has its own data source. 

Performing dynamic join between main report and its subreports

A main report may use many subreports whose data sources come from multiple databases. The reporting needs to display the result of dynamic join between these data sources and the one the main report uses. esProc implements the task in a simple way. For example:

The main table org is stored in the data source Master. Every record of the org table corresponds a subtable that has a separate data source. For example when org.org_id=“ORG_S”, the record’s subtable is the User able in the data source S_odaURL; when org.org_id=“ORG_T”, the record’s subtable is the User able in the data source T_odaURL. There are more subtables with names all being User. You need to join all subtables dynamically with the main table and display the result data set in a report. Below is the logical relationship between these tables:

esProc script:

A1: Execute the SQL statement to retrieve data from the org table in Master data source. arg1 is a parameter passed from the reporting tool. When arg1=”ORG”, the result would be:

A2: Loop through A1’s records to associate one subtable each time, concatenating each result of join into B2. esProc uses the natural indentation to represent the loop statement’s working range. Here the loop body is B2-B7 where A2 is used to reference a loop variable and #A2 is used to reference the loop number.

B2: Get the data source name for each record according to its org_id field. The result during the first loop is “S_odaURL”.

B3: This is the explicit connection to corresponding data source.

B4: Filter on data in the User table.

B5: Append three columns, which derive from the main table, to B4’s subtable. The result during the first loop is:

B6: Concatenate B5’s result into B1. The operator “|” is equivalent to union function. When the loop is over, B1 will have collected all data the reporting needs, as shown below:

B7: Close data source connection explicitly.

A8: Return B1 to the reporting tool explicitly. The default execution is to return the result of the last cell.

Displaying data by different time units specified by parameter

Here is a reporting task that requires using a line graph to show the change of sales over a recent period. unitType is a report parameter, representing time units. If unitType="hour", show sales amount every five minutes in the past one hour. If unitType="day", show sales amount per hour during the past day. And show sales amount per day over the past one week if unitType="week". The data originates from the orders table in which Amount field contains order amount. t is used to reference the order time.

esProc script:

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

A2-B4: Generate different time sequences according to the report parameter unitType. B2 generates 12 time points, with an interval of 5 minutes between each other. B3 generates time points in one day, and B4 generates time points during one week.

A5: Loop through A1 to calculate the sales amount of each time interval. “~” represents the current member and “~[-1] " represents the previous one. In the case of unitType="day", a one-field result set containing 12 records will be generated. Then you can plot the chart to show the result set. 

Examples of Handling Unusual Report Layouts

It is difficult to produce unusual report layouts automatically with functionalities provided by reporting tools. But if we can prepare data sources in an appropriate way, the difficulty of building such a report will be significantly decreased.

esProc supports set operations, order-related calculations, dynamic script execution, and provides a simple and easy-to-use JDBC interface. So it is ideal to use esProc as the tool for preparing the data required by the report. The reporting tool executes an esProc script as it executes a database stored procedure, passes parameters to the script and gets the returned esProc result through JDBC. See How to Use esProc to Assist Reporting Tools for details. 

Below are examples of some unusual layouts and the ways of handling them in esProc.

Horizontal multi-column layout

Most of the reporting tools support vertical multi-column layout, but few can handle horizontal multi-column layout. Yet this shortcoming can be remedied by using esProc to prepare the data set in advance:

Below is a selection of the database table emp that has three fields:

EId	Name	Dept
4	Emily	HR
5	Ashley	R&D
6	Matthew	Sales
7	Alexis	Sales
8	Megan	Marketing
9	Victoria	HR
10	Ryan	R&D
11	Jacob	Sales

The desired layout is that data is sequentially presented in a horizontal way in 3 columns, as shown below: 

By transforming the original 3-column table to a 9-colulmn table with esProc, you can create a horizontal multi-column layout with the reporting tool: 

Divide rows into three parts by their sequence numbers and store them respectively in A2, B2 and C2. And then concatenate the fields in B2 and C2 with those in A2:

Interlocking multi-column layout

Arrange the records of the database table emp horizontally in two columns. The record in the second column of the report’s each row will always be the first record in the next row’s first column, as shown below: 

esProc script: 

A2: An interlocking concatenation, which, in sequence, strings the current record and the previous one together. Get the concatenation result beginning from the third row. The result is like this: 

Present the records horizontally in two columns, as the preceding case shows.

Dynamic vertical multi-column layout based on single-field data sets

Present a single-field data set in a report sequentially in a “vertical first” order. Both the row number and the column number are referenced by parameters. Below is the source data: 

sOrder 26 33 84 133 32 39 43 71 99 20 83 97 107 135 142 1

esProc script:

A1: Retrieve data from the single-field table and convert it to a sequence (an ordered set).

A2: Create a two-dimensional table with the same structure as the expected report, with the initial values being nulls. Row and Col are report parameters.  

A3: This is the final result by appending data to A2: 

Printing a wide table horizontally

The database table sOrderEmp is too wide to fit across one piece of paper. The report requires that each page be printed with column headings and column numbers and that the columns from the first to the N^th be printed on one page and those from the (N+1)^th to the (2N)^th be printed on the next page, and so forth. This is shown as follows: 

esProc script: 

This standard code can be used to print any database argSource in argPagCol columns and argPageRow rows on each page. A2: Dynamically generate an empty two-dimensional table with argPageCol columns. A3: Group A1 every argPageRow rows. A4: Group A1’s field names every argPageCol ones. A5: Concatenate strings together to be processed dynamically by A6. A6: Loop through each group of rows in A3 and insert argPageCol*argPageRow records to A2 each time, with field names and values for each page being appended sequentially. A7: Return A2 to the reporting tool.

A2’s result is as follows: 

Rows duplication

Get three duplicates of the original records and present them in a report.

esProc script: 

A2: [] indicates a sequence (an order set). [~] represents a single-member sequence containing the current record from A1. [~]*3 makes 3 duplicates of the current record. The conj function performs the duplication of every record of A1 and then the concatenation. This is the result: 

Condition-controlled formats for grouped reports

Build a grouped report whose data comes from the database table sOrder, in which Seller is the grouping field, and Client and Amount hold the detail data. Here are the requirements:

1.In each group of detail data, the rows from the second down to the end will display “+”, but the first row will not display it.

2.If there is more than one row in each group of detail data, display a subtotal of the amounts at the end. No subtotal for a single-row group.

Below is the desired report layout: 

esProc code: 

Explanation: Query the database, group data by SellerId and loop through every group to append data values in the current group to the empty A2. Put “+” before the Client value if the sequence number “#” is greater than 1; append a subtotal row to A2 if there is more than one row in the current group. Below is the final result of A2: 

Return A2 to the reporting tool via JDBC.

Inserting a sub-table dynamically into the main table

The database table dColThread is the main table, with tID being the primary key; dColQuestion is the sub-table, whose foreign key is tID. They are shown as follows:

dColThread

tID	ApplicationName	User	Phone	Decline
A01	mfc	Bill	+70000000	1
A02	mfc	John	+18761221	2
A03	java	Jack	+8014001231	6
A04	mfc	Tim	+008613133123	4
A05	db	John	+18761221	8

dColQuestion

qID	tID	status
1	A01	yes
2	A01	no
3	A01	yes
4	A02	yes
5	A03	no
6	A04	no
7	A04	no
8	A05	yes

The report needs to query the main table by ApplicationName and display the data in a list table. Each record of the main table corresponds to multiple status values but less than 5 ones. They need to be inserted horizontally between Phone and Decline fields of the main table, with field names being QuestionNo1, QuestionNo2…QuestionNo5. If all values in a column are nulls, the column won’t be shown.

Below is the desired layout: 

esProc script: 

Explanation: Retrieve the associated data from the main table and the sub-table using a SQL statement and group it by tID and loop through each group. Each loop will insert a record to the empty table sequence A3, during which fields of the main table will be inserted directly and the field of the sub-table will need to be converted into rows before the insertion, with the five fields being all supplied. Here’s the result of A3 after the loops: 

Joining list tables horizontally

table 1 is a Oracle database table and table2.xlsx is a file. Both have the same structure. Below are selections of them:

We need to group table1 and table2 respectively by name, get the number of records in each group and calculate the sum of active field, and then present the results side by side. Below is the desired layout:

name	count1	amount1	count2	amount2
dd	3	352	2	224
yy			1	32
zz	1	5	1	4

esProc script: 

Explanation: Retrieve data from the database and the Excel file, and do a full join between them to put the fields we want in one data set. A5 stores the joining result, as shown below: 

Inter-column calculation for the cross table

The database table store holds the sales amount of multiple products in 2014 and 2015. You need to present the annual sales amount of every product in a cross table and calculate the annual growth rate of every product. Below is a selection of the source data:

Year          item          quantity 2014         Book         35 2015         Pencil        50 2014         Pencil        56 2015         Book         67

Below is the desired layout: 

esProc script: 

Explanation: Columns of the cross table are generated dynamically. The inter-column calculation needs a dynamic second reference. To do it, it’s difficult to write a script with the reporting tool. Yet we can first use esProc to perform the inter-column calculations and append the results to the source data, and then we just need to take care of the design of the simple cross table.

A1’s result is as follows: