多資料集實現層式報表

1.1 版本

1.2 問題描述

若報表使用的資料量是上百萬條的話，覺得報表展現的速度慢，可以使用層式報表來提高報表展現速度。

但由層式報表章節可以得知，層式報表必須是單資料集，若是多資料集的範本且資料量又很大，想要提高報表的查詢速度要如何實現呢？

1.3 解決思路

在定義資料集時透過使用資料庫本身的行序號或者使用資料庫函式生成行序號（即行號）並且在 where 條件中透過頁碼參數使得行號在一定範圍內顯示，點選自訂的上一頁下一頁按鈕時重新傳入頁碼參數取出相應的資料。

注：SQL Server2000 無法生成行號，因此需要定義儲存程式實現分頁查詢，以下具體介紹 Access 實現分頁的步驟，SQL Server2005 以及 Oracle 資料庫實現分頁的步驟有一點差別，以下會詳細介紹。

如果使用者所在場景行式引擎無法支援，則可以試用 新計算引擎功能 ，實現多資料源情況下的分頁查詢功能，使用者無需再寫複雜的分頁 SQL 或 JS 按鈕，就可以讓報表快速完成首頁載入。
注：該功能支援 Oracle、SQL Server、MySQL、HANA、PostgreSQL、Impala、DB2 等大部分主流 JDBC 資料源。

2.1 建立範本

建立範本 mutipage.cpt。為了加快展示速度，我們可以使用分頁查詢，獲取每個產品的詳細資訊並計算產品對應的訂單中的應付金額，因此新增資料集 ds1 時查詢 SQL 語句為 

select 產品.產品ID,產品名稱, 供應商ID , 類別ID, 單位數量, 產品.單價, 產品.庫存量, 產品.訂購量,sum(應付金額) as 應付款項 from 產品,訂單,訂單明細 where 產品.產品ID=訂單明細.產品ID and 訂單明細.訂單ID=訂單.訂單ID and 產品.產品ID between 10*(${page}-1)+1 and 10*${page} group by 產品.產品ID,產品名稱, 供應商ID , 類別ID, 單位數量, 產品.單價, 產品.庫存量, 產品.訂購量 order by 產品.產品ID

設定參數 page 的預設值為 1，首次看到的是第一頁，只查詢出第 1~20 條記錄；若 page 參數為 2 時，查詢出第 21~40 條記錄，即第二頁內容。

注：這邊定義報表每頁顯示 10 筆資料，即 ds1 的查詢 SQL 一次只取 10 筆資料，進而加快報表展示速度。

2.2 自訂上一頁、下一頁按鈕

使用分頁查詢後，報表需要根據 page 參數查詢出行號在一定範圍內的資料，當點選下一頁時，page 需要加 1 並傳入報表查詢出後10條記錄；點選上一頁時，page 需要減 1 並傳入報表查詢出前 10 條記錄。而報表內建上一頁下一頁按鈕無法做這些操作，因此需要自訂上一頁下一頁按鈕。

1）在儲存格中求出上一頁、下一頁頁碼的值

在工具欄中無法直接獲取 page 參數的值，因此先在儲存格中求出上一頁下一頁的頁碼值，然後再在工具欄按鈕中獲取儲存格的值。

2）自訂上一頁下一頁按鈕

點選「範本>範本 Web 屬性>分頁預覽設定」，選擇為該範本單獨設定，在工具欄中增加兩個自訂按鈕分別命名為上一頁，下一頁，使工具欄上只剩下如下圖所示幾個按鈕。

2.3 第一頁與最後一頁的處理

首次存取報表時，預設顯示第一頁 page=1，此時上一頁按鈕應該是無效的，否則點選上一頁按鈕時（頁碼為 0），此時查詢行號在 -9 到 0 之間的記錄將會出錯；同樣，顯示到最後一頁時需下一頁按鈕無效。即當上一頁頁碼page-1=0時，上一頁無效；當下一頁頁碼 page>總頁數 時，下一頁無效。
求出總頁數

根據總記錄數及每頁顯示條數，求出總頁數。新增資料集 ds2，SQL 語句為：SELECT count(*) as 總數 FROM 產品，查詢出產品表總條數，拖入儲存格，如下：

雙擊總數所在儲存格彈出資料列設定對話框，選擇「進階>自訂顯示」，在值中填入公式：roundup($$$/10,0) 求出總頁數。

將第一行的行高設定為 0，或者是隱藏：

上一頁按鈕設定：選中上一頁自訂按鈕，點選自訂事件設定回呼函式，在 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/Advanced/multipage.cpt&page=" + page;
}

注：第一句是獲取上一頁頁碼（A1 儲存格）的值，其中最後的 html() 可以用 Text() 代替；第二句是重新載入報表並給 page 參數指派。

注：若獲取的頁碼時合併了儲存格，那麼需要注意獲取的儲存格。舉個例子，如果需要獲取D1儲存格中的值，那麼在不合並儲存格的情況下，eq 後面的列號應該寫 3；如果 ABC 列儲存格合併了，那麼他們其實是作為了一個整體算列數，這時獲取 D1 的值，eq 後面的列數就需要寫 1。

上一頁按鈕設定：下一頁按鈕設定與上一頁的 JS 差不多，只需要獲取 B1 的值就可以了，所以在 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/Advanced/multipage.cpt&page=" + page;
	}

注：首次開啟報表的時候，由於 page 參數是在資料集中定義的，資料集參數的預設值在第一個次開啟報表時沒辦法傳到儲存格中，所以需要定義一個完全一樣的範本參數 page，預設值設為 1。

分頁預覽，即可查看效果。

2.4 已完成範本

點選下載範本：multipage.cpt

注：設定參數page的預設值為 1

方案一：使用 ROW_NUMBER() OVER (ORDER BY 主鍵欄位) AS rowno 生成行號。

資料集 ds1 查詢 SQL 語句：

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

方案二：使用 NOT IN/TOP

資料集 ds1 查詢 SQL 語句：

select top 20 * from 產品 where 產品id not in (select top (20*(${page}-1)) 產品id from 產品 order by 產品id) order by 產品id

方案三：使用 NOT EXIST

資料集 ds1 查詢 SQL 語句：

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

注：推薦使用方案一的分頁方法。ROW_NUMBER() 只支援 SQL2005 及以上版本，NOT EXISTS 執行效率比 NOT IN 強一點。

Oracle 資料庫中本身有行序號 ROWNUM，因此只需要將上例 ds1 資料集修改成如下：SELECT * FROM (select A.*,ROWNUM rn from (select * from 產品) A where ROWNUM <=${page}*20) where RN >${page-1}*20即可。

注：ROWNUM 只支援小於，大於是不支援的，因此要做如上定義。

注：設定參數 page 的預設值為 1

MySQL 資料庫分頁首選 LIMIT 文法，因此只需要將上例 ds1 資料集修改成如下：

方案一：LIMIT

資料集 ds1 查詢 SQL 語句：

SELECT * FROM 產品 ORDER BY 產品id LIMIT ${t},20

並給參數t指派公式：20*($page-1) ，如下圖所示：

注：MySQL 資料庫中 SQL 語句不能進行直接運算，因此將運算公式指派給變數 t。

方案二：limit+子查詢

資料集 ds1 查詢 SQL 語句：

SELECT * FROM 產品 WHERE  產品id >=(SELECT 產品id FROM 產品 ORDER BY 產品id LIMIT ${t}, 1) LIMIT 20

並給參數t指派為公式：20*($page-1) 

注：LIMIT 接受一個或兩個數字參數，參數必須是一個整數常數。如果給定兩個參數，第一個參數指定第一個傳回記錄行的偏移，第二個參數指定傳回記錄行的最大數目。初始記錄行的偏移是 0。