JS Custom fixed rows per page

1.1 Problem

In the actual project, it is required to display a fixed number of rows on each page, and to turn pages in the toolbar, the effect is shown in the following figure:

1.2 Solution

Use JavaScript to display a fixed number of rows per page, and customize the toolbar buttons to turn pages.

2.1 Dataset preparation

Create a new template, create 2 new datasets in the template, the sql statement is as follows:

1）ds1：

SELECT * 
FROM ORDERS
limit ${if(fr_pagenumber>=1,(fr_pagenumber-1)*pageSize+','+pageSize,pageSize)}

ds1 will be the data to be presented on the report page, in which the data set parameter pageSize is set, pageSize is the number of rows displayed on each page, and the default value of pageSize is set to 5.

fr_pagenumer is a built-in parameter. This parameter represents the current page of the report and does not need to be added manually.

2）ds2：

${if(totalCount>0 && totalPage>0,'select '+totalCount+' as totalCount ',
'SELECT count(*) totalCount FROM orders 
'+ if(len(shipmethod)>0," and shipmethod='"+shipmethod+"'",""))}

ds2 is used to obtain the number of records of each query result, that is, how many records are there in each query result.

The data set parameters totalPage and totalCount are set, totalPage represents the total number of pages in the report, and totalCount represents the total number of records in the report.

2.2 Add parameter widget

According to the set dataset parameters, add parameter widgets in the parameter pane.

1) Add a drop-down box widgetpageSize, pageSize customizes the number of rows displayed on each page of the 5~20 single selection, as shown in the following figure:

2) Add text widgets: fr_pagenumer, totalPage, totalCount, where totalCount is bound to the totalCount column of the data column ds2, as shown in the following figure:

3) Add a predefined query button and add a button click event, as shown in the figure below:

JavaScript:

var widget=contentPane.parameterEl.getWidgetByName('fr_pagenumber');
widget.setValue('1');//Reset the current page to 1 each time the query is clicked
_g().parameterCommit();

2.3 Design report

Design the report style, as shown in the figure below:

Among them:

• B3 cell insert formula seq() to generate sequence number

• Insert a formula to count the page information in cell B4. The formula is as follows: CONCATENATE("Page ",$fr_pagenumber,", ",roundup(value("ds2","totalCount")/$pageSize), " pages in total, ",$pageSize," items/page, A total of ",$totalCount)

Formula explanation:

2.4 Customize the page turning button

Click menu template>template Web Attributes>Pagination Preview Setting, select set individually for the template

1) When using the toolbar, first, clear the original toolbar, and then add 4 custom buttons , as shown in the figure below:

The names of the four custom buttons First page, Previous page , Next page , Last page, as shown in the figure below:

2) Add click events to the buttons respectively, as shown in the following figure:

JavaScript:

1) First page:

page_up_down('firstPage',this);

2) Previous page:

page_up_down('prevPage',this);

3) Next page:

page_up_down('nextPage',this);

4) Last page:

page_up_down('lastPage',this);

2.5 Reference JavaScript

1) Download page.js from the attachment and save it in the %FR_HOME%\webapps\webroot\help folder.

2) Click on the menu template>template Web Attributes> Reference JavaScript, in the disk file, find the file page.js you just downloaded, as shown below:

Note: This JS file only supports the current report after being referenced. If you want to use it in all reports, you can add page.js in menu "Server>Server Configuration>Reference JavaScript".

page.js is as follows:

//Page turning button event
function page_up_down(mode,obj){
var widget=contentPane.parameterEl.getWidgetByName("fr_pagenumber");
var fr_pagenumber=widget.getValue();
console.log('fr_pagenumber='+fr_pagenumber);
if(mode=='firstPage'){
if(fr_pagenumber>1){
widget.setValue(1);
}else{
obj.setEnable(false);
return ;
}
}else if(mode=='lastPage'){
var widget2=contentPane.parameterEl.getWidgetByName('totalPage');
var totalPage=widget2.getValue();
//console.log('totalPage='+totalPage);
if(parseInt(fr_pagenumber)<parseInt(totalPage)){
widget.setValue(totalPage);
}else{
obj.setEnable(false);
return ;
}
}else if(mode=='prevPage'){
if(fr_pagenumber>1){
fr_pagenumber=parseInt(fr_pagenumber)-parseInt(1);
widget.setValue(fr_pagenumber);
}else{
obj.setEnable(false);
return ;
}
}else if(mode=='nextPage'){
var widget2=contentPane.parameterEl.getWidgetByName('totalPage');
var totalPage=widget2.getValue();
fr_pagenumber=parseInt(fr_pagenumber)+parseInt(1);
if(parseInt(fr_pagenumber)<=parseInt(totalPage)){
widget.setValue(fr_pagenumber);
}else{
obj.setEnable(false);
return ;
}
}
_g().parameterCommit();//Report parameter submission
}
function afterload(){
//Update the total number of pages
var widget=contentPane.parameterEl.getWidgetByName('totalPage');
var widget2=contentPane.parameterEl.getWidgetByName('pageSize');
var widget3=contentPane.parameterEl.getWidgetByName('totalCount');
if(widget){
var totalCount=widget3.getValue();
var pageSize=widget2.getValue();
var totalPage=Math.ceil(totalCount/pageSize);
console.log('totalPage='+totalPage);
var newPage=widget.getValue();
if(newPage<1){
newPage=1;
}
//if(newPage!=totalPage){
widget.setValue(totalPage);
//}
var widget4=contentPane.parameterEl.getWidgetByName('fr_pagenumber');
var pagenumber=widget4.getValue();
if(pagenumber>totalPage && totalPage>0){
widget4.setValue(totalPage);
}
}
}

Code explanation:

page.js contains two functions:

• The page_up_down function, the function is to trigger a JS event when the page turning button is clicked, first get the current page, then compare it with the total number of pages, perform the corresponding addition and subtraction functions of the next page and the previous page, etc.

• Afterload function, the function is to update the total number of report pages and the total number of reports returned by the data set to the widgets of the parameter panel, and then pass it as a parameter to SQL to obtain report data during query.

2.6 Add afterload function

Click menu template>template Web Attributes>Pagination Preview Setting, and add the report loading end event, as shown in the following figure:

JavaScript:

afterload();

2.7 Preview effect

Save the report, click the pagination preview, the report effect is shown:

3.1 Template

Custom fixed rows per page.cpt

3.2 JS

page.js