I. Overview
Report Row-based engine for databases that are not supported, need to check the row engine button area to achieve the paging effect on the basis of writing paging SQL.
This document will introduce how to write paging SQL for databases that are not supported by the row engine.
II. Databases that need to write pagination SQL
Note 1: The database with paging SQL must be written, and the paging SQL must be written on the basis of checking the row engine button to achieve the paging effect.
Note 2: There is no need to write a paging SQL database, and Report Row-based enginecan be used to achieve the paging effect.
| Database that must write paged SQL | No need to write paging SQL database |
|---|---|
| Sqlite | Oracle |
| Access | MySQL |
| SQL Server 2005、SQL Server 2008 | HSQL |
| INFORMIX | SQL Server 2012 and above Note: SQL Server 2012 and above support row engines, but order by must be written in SQL |
| Sybase | |
| etc. |
III. Pagination SQL for Sqlite database
Take the built-in Sqlite of FRDemo as an example to illustrate how Sqlite writes pagination query.
1. Set new data
Create new data set ds1：SELECT * FROM ORDERSDETAIL.
2. Add pagination query SQL statement
1) Steps
Click Pagination Query button in the data query panel to edit the pagination SQL statement, as shown in the following figure:
The complete SQL statement is as follows:
SELECT * FROM ORDERSDETAIL
SELECT * FROM (SELECT * FROM( SELECT * FROM ORDERSDETAIL ORDER BY ORDERID ASC limit ${fr_pagesize*fr_pagenumber}) AS e1
ORDER BY ORDERID DESC
limit ${if(fr_pagenumber == int((((fr_rowcount-1)/fr_pagesize)+1)),fr_rowcount - (fr_pagesize*(fr_pagenumber-1)),fr_pagesize)} ) AS e2
ORDER BY ORDERID ASC
2) Paging SQL statement comments
${if(fr_pagenumber ==int( (((fr_rowcount-1)/fr_pagesize)+1)),fr_rowcount - (fr_pagesize*(fr_pagenumber-1)),fr_pagesize)}
Meaning is:
If it is the last page, take the number of lines remaining on the last page. If it is not the last page, take the number of lines that need to be displayed on each page. In the example, the number of lines that need to be displayed on each page is 30 lines.
fr_pagenumber：the number of pages currently browsed. If the second page is previewed, fr_pagenumber=2；
fr_rowcount：the total number of data in the current data set;
fr_pagesize：indicates the number of lines to be displayed on each page when setting the row-based Engine, in this example fr_pagesize=30.
During preview, the set pagination query will generate database query based on the values of 3 variables. For example, when fr_pagenumber=2 and fr_pagesize=30, the report will be previewed on the Web side. When previewing to page 2, the SQL statement above will be change into:
SELECT * FROM ( SELECT * FROM ( SELECT * FROM ORDERSDETAIL ORDER BY ORDERID ASC limit 60 )AS e1 ORDER BY ORDERID DESC limit30 ) AS e2 ORDER BY ORDERID ASC
When previewing to page 3, fr_pagenumber=3, the SQL statement is transformed into:
SELECT * FROM ( SELECT * FROM ( SELECT * FROM ORDERSDETAIL ORDER BY ORDERID ASC limit 90 )AS e1 ORDER BY ORDERID DESC limit30) AS e2 ORDER BY ORDERID ASC
When previewing to the last page, the remaining data may be less than 30 rows, so what will the SQL statement be transformed into?
If fr_rowcount=100, fr_pagesize=30, that is, the total number of data rows is 100, and each page displays 30 rows. When the preview reaches the last page, which is page 4, fr_pagenumber=4, the SQL statement will be converted to:
SELECT * FROM ( SELECT * FROM ( SELECT * FROM ORDERSDETAIL ORDER BY ORDERID ASC limit 120)AS e1 ORDER BY ORDERID DESC limit10 ) AS e2 ORDER BY ORDERID ASC
3. Report body design
Drag the data column in the data set to the cell, as shown in the figure below:
4. Row-based Engine settings
In the designer, click Template>Report Engine Attribute, and then check the Execute report by row-based engine and Use page-based computations and separately execute report buttons in turn. As shown below:
5. Effect view
Click on Pagination Preview, the effect picture is as follows:
6. Completed template
Please refer to the completed template:%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Advanced\PagingSQL.cpt
Click to download the template: PagingSQL.cpt
VI. Access、SQLServer2008、SQLServer2005 pagination SQL statement of the database
Notes：SQLServer2008、SQLServer2005 and Access database operation steps are the same.
This chapter takes the SQLServer2008 database as an example. In the SQLServer2008 database, the query statement example: SELECT * FROM [guest].[ORDERDETAIL, you need to bring the pattern name prefix to search.
1) Import the ORDERDETAIL in Section III of this article into SQLServer2008.
2) Refer to Section III.1 of this article.
3) Refer to Section III.2 of this document, the paging SQL statement is as follows:
SELECT *
FROM (
SELECT TOP ${ if(fr_pagenumber == int((((fr_rowcount-1)/fr_pagesize)+1)),fr_rowcount - (fr_pagesize*(fr_pagenumber-1)),fr_pagesize) } *
FROM(
SELECT TOP ${fr_pagesize*fr_pagenumber} *
FROM ORDERSDETAIL
ORDER BY ORDERID ASC
) AS e1
ORDER BY ORDERID DESC
) AS e2
ORDER BY ORDERID ASC
4) Refer to Section III.3, III.4, and III.5 of this article.
V. SQLServer2008 view table paging SQL
When using the view table, paging according to the above SQL, there will be many pages repeated display, so when SQLServer uses the view, before writing the paging SQL, you must userow_number to give each data a unique number to order by.
When using paging SQL, you need to query, and you need to writewhere condition in the paging SQL, otherwise the data cannot be queried.
with t_rowtable
as (
select row_number() over(order by object_id DESC) as row_number,*
from sys.[all_columns]
where 1=1
)
SELECT *
FROM (
SELECT TOP ${ if(fr_pagenumber == int((((fr_rowcount-1)/6)+1)),fr_rowcount - (6*(fr_pagenumber-1)),6) } *
FROM(
SELECT TOP ${6*fr_pagenumber} *
FROM t_rowtable ORDER BY row_number ASC
) AS e1
ORDER BY row_number DESC
) AS e2
ORDER BY row_number ASC
Previous：Report Row-based Engine
