I. Overview
1. Version
| Report server version | Jar package |
|---|---|
| 10.0 | 2018-12-27 |
2. Application scenarios
When a large amount of data is exported, it will cause certain pressure on the server, network transmission and database.
In order to prevent such risks, there's the function of Large dataset export, which can export directly according to the data set results.
3. Function description
Large dataset export is a kind of Excel export method with less resources and high speed, which can be used for background streaming export without front-end data display.
This function is mainly aimed at the detail table. The user can skip the report calculation and export the data directly through the user-defined JavaScript code call interface. The implementation principle is as follows:
1) Using sxssfworkbook stream lines to export, it is fast.
2) Using the producer consumer mode, one thread is used to retrieve data and store the data row in the queue, while another thread reads and exports the row.
4. Interface introduction
Big dataset export interface: directExportToExcel: function (dsName, fileName, params, colNames)
| Key | Value | 举例 |
|---|---|---|
| sessionID | specific sessionID | - |
| dsName | dataset name | ds1 |
| fileName | export file name, if not specified, use the default "template name - dataset name. xlsx" | sales_volume |
| params | Parameter dataset JSON，Parameter Name: Parameter Value | { id: '9527', name:'Stephen' } |
| colNames | Column names are separated by commas, if not specidied,use all fields of the dataset. | col1,col2,... |
The interface example is as follows:
//The interface is directexporttoexcel: function (dsname, filename, params, colnames)
//Note that special characters in parameters need URL encoding, such as braces, colons, etc.
var paramStr = encodeURIComponent("{param1:1,param2:\"21','22\",param3:\"text\",...}")
//passing parameters of dataset，string is suggested in the format \"text\"
var colNames = encodeURIComponent("col1, col2, col3,...")
_g().directExportToExcel("dataset name", "file name", paramStr, colNames)
5. Precautions
1) This function only supports relational databases. And SQL Server database needs to set cursor as server cursor.
2) In Oracle database, the default returned column name in SQL query is uppercase, so when JS passes in colnames parameter, the case of column name should be consistent with it.
3) This function can't directly export date/datetime null value. You need to add zerodatetimebehavior = converttonull parameter after the URL of JDBC data connection.
4) It is recommended that the amount of data to be exported should not exceed "1000W rows * 20 columns". If the amount of data is too large, only part of the data will be exported.
5) The exported excel is the data obtained directly from the database through SQL statements, not the data in the report, so the data format set in the report cannot be exported.
6) This function does not support mobile terminal.
II. Example 1: exporting fixed parameter values from large datasets
1. New template
1) New dataset
Create a new normal report and a new dataset. The SQL statement is as follows:
DS1: select * from sales_volume where 1 = 1 and region in ('${area}') and salesperson in ('${stuff})
DS2: select salesperson from sales_volume where region in ('${area})
2) Design report
The report body style is shown in the following figure:
2. Setting query widgets
Edit parameter pane, add two label widgets, two drop-down check box widgets and one query widget. As shown in the figure below:
1) region widget
Select the first drop-down check box widget, set the widget name as "area", the label name as "region". "The data dictionary is set as" region "in the" sales_volume" table in FRDemo database, the return value type is "string ", and the separator is ', ', as shown in the following figure:
2) Salesperson widget
Select the second "drop-down check box widget", set the name of the widget as "stuff", the label name as "salesperson", the data dictionary as "salesperson" in the dataset "DS2", the return value type as "string", and the separator as ', ', as shown in the following figure:
3. Setting export widget
1) new widget
Add a button widget, click widget settings > attributes, and set the button name to large dataset export, as shown in the following figure:
2) Setting click events
Select button widget click Control Settings > event, add click event, and enter JavaScript statement, as shown in the figure below:
The JavaScript code is as follows:
//port is directExportToExcel: function (dsName, fileName, params, colNames)
var paramStr = encodeURIComponent("{area:\"Jiangsu Province','Zhejiang Province\",stuff:\"Chen Yu','Chen Ping\"}")
//dataset passing parameter
var colNames = encodeURIComponent("Region,Salesperson,product type,product,Sales volume")
//Specify the data columns to be exported. The exported fields are arranged in this order. If it is empty, all the columns will be exported by default
_g().directExportToExcel("ds1", "Sales_volume", paramStr, colNames)
Note 1: the parameter name here refers to the template parameter name, not the data column name in the dataset.
Note 2: before and after the SQL parameter value, \, should be added to prevent it from being parsed.
4. Effect preview
Save the template and click pagnation preview. Click the Large dataset export button to export the DS1 data set with "regions of Jiangsu and Zhejiang Province and salesperson of Chen Yu and Chen Ping" to excel file. The exported content has nothing to do with the query content, as shown in the figure below:
Note: mobile terminal is not supported.
III. Example 2: exporting dynamic parameter values from large datasets
1. New template
Create a new normal report and a new dataset. The SQL statement is as follows:
DS1: select * from sales_volume where 1 = 1 and region in ('${area}') and salesperson in ('${stuff})
DS2: select salesperson from sales_volume where region in ('${area})
The report body style is the same as example 1.
2. Setting query widgets
Edit parameter pane, add two label widgets, two drop-down check box widgets and one query widget.
1) region widget
Select the first drop-down check box widget, set the widget name as "area", the label name as "region". "The data dictionary is set as" region "in the" sales_volume" table in FRDemo database, the return value type is "string ", and the separator is ', ', as shown in the following figure:
2) Salesperson widget
Select the second "drop-down check box widget", set the name of the widget as "stuff", the label name as "salesperson", the data dictionary as "salesperson" in the dataset "DS2", the return value type as "string", and the separator as ', ', as shown in the following figure:
The steps is the same as II.1, II.2.
3. Setting dynamic export columns
Add a label widget and a drop-down check box widget. Select the drop-down check box widget, click widget Settings > attributes, set the widget name as col, the label name as export column, the data dictionary type as formula, and the actual value as TABLEDATAFIELDS("ds1"), as shown in the following figure:
4. Setting export widget
1) new widget
Add a button widget, click widget settings > attributes, and set the button name to Large dataset export, as shown in the following figure:
2) setting click events
Select button widget, click widget settings > event, add click event, and enter JavaScript statement, as shown in the figure below:
The JavaScript code is as follows:
var widgetNames = ['area', 'stuff'];
//Define the name of the widget in the array.
function getWidgetValueByName(name) {
var widget = _g().parameterEl.getWidgetByName(name);
//get the widget value according to the widget name
if (widget == undefined) return;
var obj = {};
obj[name] = widget.getValue();
return obj;
}
//If you add a new parameter, you can directly add the widget name in the widgetnames. There is no need to modify it here.
var paramJson = widgetNames.map(getWidgetValueByName).reduce(function(a, b) {
return Object.assign(a, b)
});
var paramJsonStr = JSON.stringify(paramJson);
//Change JSON data to string
var col = this.options.form.getWidgetByName("col").getValue();
//alert(col);
//URL coding of parameter
var colNames = encodeURIComponent(col)
_g().directExportToExcel("ds1", "Sales_volume", encodeURIComponent(paramJsonStr), colNames)
5. Effect preview
Save the template and click pagination preview. Query "region" and "salesperson", select the data column to be exported, and click "large data set export button". The exported content is consistent with the query content, and only the specified data column is exported. As shown in the figure below:
Note: mobile terminal is not supported.
IV. Completed template
1. Example 1
For completed templates, please refer to:
%fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\JS\LargeDS_fixed.cpt
Click to download the template: LargeDS_fixed.cpt
2. Example 2
For completed templates, please refer to:
%fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\JS\LargeDS_Dyn.cpt
Click to download the template: LargeDS_Dyn.cpt
V. Precautions
When exporting a large dataset, it is connected to Presto database. If the export fails and there is the following error message:
Warning: 14:39:00 eventthread error [standard] com.facebook.presto . jdbc.NotImplementedException : Method Connection.prepareStatement is not yet implemented
Just upgrade the driver to presto-jdbc-339.jar. Enter the official website of Trino (Presto is now Trino) and download the driver as shown in the following figure: