Multi Data Sets Achieving Layered Reports

If the amount of data used in the report is millions of items, and the report presentation speed is slow, you can use layered reports to improve the report presentation speed. But from the chapter on layered reports, layered reports must be a single data set. If it is a template with multiple data sets and the amount of data is large, how to improve the query speed of the report?

When defining the data set, use the row number of the database itself or use the database function to generate the row number (that is, the row number) and use the page number parameter in the where condition to display the row number within a certain range, click on the customized previous page next page When the button is pressed, the page number parameter is re-passed in to retrieve the corresponding data.

Note: SQL Server2000 cannot generate row numbers, so you need to define stored procedures to implement paging queries. The following specifically introduces the steps of Access to implement paging. There is a little difference between SQL Server2005 and Oracle databases to implement paging, which will be described in detail below.

If the line engine of the user's scene cannot be supported, you can try the New Calculation Engine Plugin, which implements the paging query function in the case of multiple data sources, and the user does not need to write complex paging SQL or JS buttons, and the report can be completed quickly Home page loading.

Note: The plug-in supports most mainstream JDBC data sources such as Oracle, SQL Server, MySQL, HANA, PostgreSQL, Impala, and DB2.

1. New template

Create a new template mutipage.cpt. In order to speed up the display, we can use paging query to obtain the detailed information of each product and calculate the amount payable in the order corresponding to the product. Therefore, the query SQL statement when adding the data set ds1 isselect ProductID,Product_name, SupplierID , CategoryID, Unit_quantity, Unit_price, Inventory_quantity, Order_quantity from Product where ProductID between 10*(${page}-1)+1 and 10*${page} group by ProductID,Product_name, SupplierID , CategoryID, Unit_quantity, Unit_price, Inventory_quantity, Order_quantity order by ProductID, The default value of the parameter page is set to 1, the first page is viewed for the first time, and only the 1st to 20th records are queried; if the page parameter is 2, the 21st to 40th records are queried, which is the second page content .

Note: Here you define that the report displays 10 pieces of data per page, that is, the query SQL of ds1 only fetches 10 pieces of data at a time, thus speeding up the display of the report.

2. Customize the previous and next buttons

After using the pagination query, the report needs to query the data within a certain range according to the page parameter. When the next page is clicked, the page needs to be increased by 1 and passed into the report to query the last 10 records; when the previous page is clicked, the page needs Decrease 1 and input the report to query the first 10 records. The previous page and next page buttons built in the report cannot do these operations, so you need to customize the previous page and next page buttons.

1) Find the value of the previous and next page numbers in the cell

The page parameter value cannot be obtained directly in the toolbar, so first find the page number value of the previous page and the next page in the cell, and then obtain the cell value in the toolbar button.

2) Customize the previous page and next page buttons

Click Template>Web Attributes>Pagination Preview settings, choose to set the template separately, add two custom buttons in the toolbar and name them Last Page and Next Page, so that only the following figure is left on the toolbar Several buttons.

3. Processing of the first and last pages

When accessing the report for the first time, the first page page=1 is displayed by default. At this time, the previous page button should be invalid. Otherwise, when the previous page button is clicked (page number is 0), the query line number is between -9 and 0 The record of will be wrong; similarly, when the last page is displayed, the next page button is invalid. That is, when the page number of the previous page page-1=0, the previous page is invalid; when the page number of the next page page>total number of pages, the next page is invalid.

Find the total number of pages

According to the total number of records and the number of items displayed on each page, find the total number of pages. Add data set ds2, the SQL statement is: SELECT count(*) as total FROM Product, query the total number of products in the table, drag it into the cell, as follows:

Double-click the cell with the total number to pop up the Data Column Settings dialog box, select Advanced>Custom Displays, and fill in the formula in the value: roundup($$$/10,0) to find the total number of pages.

Set the height of the first row to 0, or hide it:

Previous button setting: Select the custom button on the previous page, click the custom event to set the callback function, and fill in the JS:

var page= $("tr[tridx=0]","div.content-container").children().eq(0).html(); 
if(page==0)
this.setEnable(false);
else{
    window.location.href="${servletURL}?reportlet=doc-EN/Advanced/multipage.cpt&page="+page;
    }

Note: The first sentence is to get the value of the previous page number (cell A1), the last html() can be replaced by Text(); the second sentence is to reload the report and assign a value to the page parameter.

Note: If the cells are merged when the page number is obtained, you need to pay attention to the cells you have obtained. For example, if you need to get the value in cell D1, then without merging the cells, the column number after eq should be 3; if the cells in column ABC are merged, then they are actually counted as a whole The number of columns. At this time, to get the value of D1, write 1 for the number of columns after eq.

Previous button setting: The next page button setting is similar to the JS of the previous page, only the value of B1 needs to be obtained, so fill in the JS:

var page= $("tr[tridx=0]","div.content-container").children().eq(1).html();  
var total=$("tr[tridx=0]","div.content-container").children().eq(2).html();  
if(parseInt(page) > parseInt(total))
{
this.setEnable(false);
}
else{
    window.location.href="${servletURL}?reportlet=doc-EN/Advanced/multipage.cpt&page="+page;
    }

Note: When the report is opened for the first time, since the page parameter is defined in the data set, the default value of the data set parameter cannot be transferred to the cell when the report is opened for the first time, so you need to define an exactly the same template parameter page , The default value is set to 1.

Pagination Preview, you can see the effect.

4. Completed template

Please refer to the completed template:%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Advanced\mutipage.cpt.

Click to download the template:

multipage.cpt

Note: The default value of setting parameter page is 1

Option 1: Use ROW_NUMBER() OVER (ORDER BY primary key field) AS rowno to generate the row number.

Data set ds1 query SQL statement:

select * from (SELECT *,ROW_NUMBER() OVER (ORDER BY ProductID) AS rowno FROM Product) as b where b.rowno between 20*(${page}-1)+1 and 20*${page}

Option 2: Use NOT IN/TOP

Data set ds1 query SQL statement:

select top 20 * from Product where ProductID not in (select top (20*(${page}-1)) ProductID from Product order by ProductID) order by ProductID

Option 3: Use NOT EXIST

Data set ds1 query SQL statement:

select top 20 * from Product where not exists (select 1 from (select top (20*(${page}-1)) ProductID from Product order by ProductID)a where a.ProductID=Product.ProductID) order by ProductID

Note: It is recommended to use the paging method of Scheme 1. ROW_NUMBER() only supports SQL2005 and above. NOT EXISTS is more efficient than NOT IN.

The Oracle database itself has the row number ROWNUM, so you only need to modify the ds1 data set in the above example as follows:

SELECT * FROM (select A.*,ROWNUM rn from (select * from Product) A where ROWNUM <=${page}+20) where RN >=${page}

Note: ROWNUM only supports less than, greater than is not supported, so it should be defined as above.

Note: The default value of setting parameter page is 1

The LIMIT syntax is preferred for MySQL database paging, so you only need to modify the ds1 data set in the above example as follows:

Option 1: LIMIT

Data set ds1 query SQL statement:

SELECT * FROM Product ORDER BY ProductID LIMIT ${t},20

And assign a value formula to the parameter t:20*($page-1) 

Note: The SQL statement in the MySQL database cannot be directly calculated, so the calculation formula is assigned to the variable t.

Option 2: limit+subquery

Data set ds1 query SQL statement:

SELECT * FROM Product WHERE  ProductID >=(SELECT ProductID FROM Product ORDER BY ProductID LIMIT ${t}, 1) LIMIT 20

And assign the parameter t to the formula:20*($page-1) 

Note: LIMIT accepts one or two numeric parameters, and the parameter must be an integer constant. If two parameters are given, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of returned record rows. The offset of the initial record line is 0.