~楓花雪岳~: [SSRS] 分頁固定筆數

星期三, 1月 28, 2015

[SSRS] 分頁固定筆數

品名下每六筆資料就進行換頁，換頁的部分我已經有爬過 Google 上面的文章搞定了，但問題出在不滿六筆該如何把它補滿六筆

以下文章練習：

每頁要補滿 10 筆資料，沒有資料也要把資料空間顯示出來
設定群組檔頭，讓每一頁都可以顯示檔頭資訊

T-SQL 撰寫

利用 CTE 跑出每個部門每頁 10 筆資料 Table，EX：部門有 12 位，該部門就要有 20 筆資料存在

;
WITH CTE AS
(
    SELECT 
       D.DepartmentID AS DepID , 
       1 AS StartNO ,
       (((COUNT(D.DepartmentID) - 1) / 10) + 1) * 10 AS EndNO
    FROM Person.Person AS P
        JOIN HumanResources.EmployeeDepartmentHistory AS H ON P.BusinessEntityID = H.BusinessEntityID
        JOIN HumanResources.Department AS D ON H.DepartmentID = D.DepartmentID
    WHERE H.EndDate IS NULL
    GROUP BY D.DepartmentID
    UNION ALL
    SELECT 
        DepID ,
        StartNO + 1 ,
        EndNO
    FROM CTE 
    WHERE StartNO < EndNO
)
SELECT 
    T1.DepID ,
    T2.DepName,
    ((T1.StartNO - 1) / 10 ) + 1 AS Group10 ,
    T1.StartNO AS GroupNO,
    T2.LastName,
    T2.FirstName,
    T2.EmailAddress
FROM CTE AS T1
    LEFT JOIN
        (
            SELECT 
                D.DepartmentID AS DepID , 
                D.Name AS DepName , 
                ROW_NUMBER() OVER (PARTITION BY D.DepartmentID ORDER BY D.DepartmentID,P.LastName) AS GroupNO,
                P.LastName , 
                P.FirstName , 
                A.EmailAddress
            FROM Person.Person AS P
                JOIN Person.EmailAddress AS A ON P.BusinessEntityID = A.BusinessEntityID
                JOIN HumanResources.EmployeeDepartmentHistory AS H ON P.BusinessEntityID = H.BusinessEntityID
                JOIN HumanResources.Department AS D ON H.DepartmentID = D.DepartmentID
            WHERE H.EndDate IS NULL
        ) AS T2 ON T1.DepID = T2.DepID 
                   AND T1.StartNO = T2.GroupNO
ORDER BY T1.DepID , T1.StartNO
OPTION (maxrecursion 0)

下圖可見，雖然 Purchasing 部門只有 12 筆資料，但經過處理會產生 20 筆資料

重點簡易說明（上述語法 Highlight 第七行）

根據部門人數找出下一個 10 倍數群組，EX：5 => 10、10 => 10、12 => 20

SELECT
       D.DepartmentID AS [部門 ID] , 
       COUNT(D.DepartmentID) AS [部門人數],
       (((COUNT(D.DepartmentID) - 1) / 10) + 1) * 10 AS [預計產生資料]
    FROM Person.Person AS P
        JOIN HumanResources.EmployeeDepartmentHistory AS H ON P.BusinessEntityID = H.BusinessEntityID
        JOIN HumanResources.Department AS D ON H.DepartmentID = D.DepartmentID
    WHERE H.EndDate IS NULL
    GROUP BY D.DepartmentID