[SSRS] 階梯狀報表

傳統資料表報表會將父群組放在報表的相鄰資料行中，而階梯狀報表會在相同的資料欄中，顯示父群組底下縮排的詳細資料列或子群組。

以下實作是為了顯示各分公司各部門的事病假報表

• DataSet T-SQL 語法

USE [AdventureWorks2012]
GO

IF OBJECT_ID('Leave') IS NOT NULL
 DROP TABLE Leave

CREATE TABLE Leave (Branch nchar(4) , DepName nchar(20) , EmpName nchar(20) , personal numeric(4,1) , Sick numeric(4,1))
INSERT INTO Leave (Branch,DepName,EmpName,Personal,Sick) VALUES
  (N'台北',N'MIS',N'MIS-1',0.0,0.0)  ,
  (N'台北',N'MIS',N'MIS-2',8.0,8.0)  ,
  (N'台北',N'HR',N'HR-1',8.0,0.0)    ,
  (N'台中',N'Sales',N'Sales-1',0.0,4.0)   ,
  (N'台中',N'Sales',N'Slaes-2',8.0,8.0)  ,
  (N'台中',N'Marketing',N'Marking-1',8.0,4.0)  ,
  (N'高雄',N'Accounting',N'Accounting-1',0.0,0.0)  ,
  (N'高雄',N'Accounting',N'Accounting-2',0.0,0.0)  ,
  (N'高雄',N'Accounting',N'Accounting-3',0.0,0.0)  ,
  (N'高雄',N'Design',N'Design-1',0.0,0.0)  ,
  (N'高雄',N'Design',N'Design-2',0.0,0.0) 

[SQL] WHERE 中的資料篩選

效能議題中，有個原則是不要對欄位進行任何轉換，而最常見的轉換莫過於利用 YEAR()、DATEADD() 、CONVERT() 等函數，對日期欄位進行轉換。

• 利用 AdventureWorks2012 的 SalesOrderHeader Table 說明

-- 針對 OrderDate 建立 Index
CREATE INDEX [IX_SalesOrderHeader_OrderDate] ON dbo.SalesOrderHeader (OrderDate)

-- 跑這 4 個 T-SQL 語法來觀察執行計畫中 Index 使用情況
SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE SUBSTRING(CONVERT(varchar(10),OrderDate,112),1,4) = '2007'

SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE CONVERT(varchar(10),OrderDate,112) LIKE '2007%'

SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE DATEPART(yyyy,OrderDate) = 2007

SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate BETWEEN '20070101' AND '20071231'

• T-SQL 執行結果和其執行計畫

從上圖中就可以觀察到只要對 OrderDate 日期欄位進行任何轉換動作，都會導致 Query Optimizer 使用 Index Scan，而非 Index Seek 來抓取資料，造成效能低落，T-SQL 語法中假如常常需要進行日期轉換，或許可以考慮除了日期欄位本身外，還可以建立年、月、日資料欄位，來增加資料蒐尋的便利性。

• 2011 SQL Hero 考題

延伸閱讀
• [SQL] 日期欄位資料型態的選擇

參考資料
• 論壇相關問題討論 1、2、3

[SSRS] 分頁頁碼

閱讀 MSDN 文章 Reporting Services 中的分頁 時有點霧煞煞，Google 些資料來了解並實作練習此主題

DataSet T-SQL 語法

利用 AdventureWorks2012，找出人員所屬部門

SELECT 
    D.DepartmentID AS DepID , 
    D.Name AS DepName , 
    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

建立父群組

資料列 => 右鍵滑鼠 => 加入群組 => 父群組

群組依據為 DepID 並勾選 ［加入群組頁首］

DepID 欄位 =>滑鼠右鍵 => 刪除資料行，因為沒有要使用該群組欄位，故意把它刪除

微調版面

把 DepID 和 DepName 資料，放在群組頁首

PageBreak 相關設定

資料列群組欄位 => 內點選 DepID 群組 => 屬性 => Group => PageBreak 相關設定 => 設定如下

1. BreakLocation => BETWEEN
2. Disable => False
3. ResetPageNumber => True
4. PageName => Fields!DepName.Value

各屬性說明

1. BreakLocation：會針對啟用分頁的報表元素，提供分頁的位置：開頭、結尾，或開頭和結尾。 若是群組，BreakLocation 可以位於群組之間
2. Disabled：會指出是否將分頁套用至報表元素。 如果這個屬性評估為 True，則會忽略分頁。 如果使用這個屬性，可以根據報表執行時的運算式，以動態方式停用分頁
3. ResetPageNumber：會指出分頁時，是否應該將頁碼重設為 1。 如果這個屬性評估為 True，則會重設頁碼。
4. PageName：針對分頁所造成的新頁面，提供新的頁面名稱
   

頁首設定

頁碼只能在頁首或頁尾設定

在頁首新增文字方塊

文字方塊的運算式內輸入

= "全部分頁號碼：" & Globals!OverallPageNumber & "／" & Globals!OverallTotalPages & "（OverPageNumber／OverallTotalPages）"

同樣步驟再建立一個文字方塊並在運算式內輸入

= "群組分頁號碼：" & Globals!PageName & " - " & Globals.PageNumber & "／" & Globals.TotalPages & "（PageName - PageNumber／TotalPages）"

調整版面並預覽結果

從下圖可以看出全部資料共分 19 頁，此畫面為第 5 頁， purchasing 群組，總共是 1 頁，此為群組第 1 頁。

從下圖可以看出全部資料共分 19 頁，此畫面為第 7 頁， Production 群組，總共是 4 頁，此為群組第 1 頁。

• 參考資料
• Reporting Services 中的分頁
• Report Design: Reset Page Number on Group
• Reset Page Number On Group

[SSRS] 群組分頁

利用 SSRS 的 RowNumber() 內鍵函數來建立資料群組，再根據此群組來分頁，也可以說是每 X 筆資料就分頁的效果。

DataSet T-SQL 語法

使用 AdventureWorks2012 來捉取資料，並利用 ROW_NUMBER() 把資料順序號碼 show 出來，方便報表產生時，可以拿來比較

SELECT         
    ROW_NUMBER() OVER (ORDER BY E.BirthDate) AS ROWNO ,
    P.LastName, 
    P.FirstName, 
    E.JobTitle, 
    E.BirthDate, 
    A.EmailAddress
FROM Person.Person AS P 
  INNER JOIN HumanResources.Employee AS E ON P.BusinessEntityID = E.BusinessEntityID 
  INNER JOIN Person.EmailAddress AS A ON P.BusinessEntityID = A.BusinessEntityID

建立群組

• 新增一個資料列父群組

• 父群組分類條件

此條件文章內一律稱為 RowNumber() 條件

= INT((ROWNUMBER(NOTHING) - 1) / 10)

• 設定群組屬性

［資料列群組］內，可以看見新增的父群組（Group1），點選右邊向下箭頭，再點選［群組屬性］

群組屬性 => 一般 => 修改群組名稱為 GroupPageBreak，方便自己辨識；群組對象則是新增父群組時，所輸入的 RowNumber() 條件，不需要再修改。

群組屬性 => 分頁符號 => 勾選［在群組的每個執行個體之間］

群組屬性 => 排序，RowNumber() 條件在新增時，也會是排序依據， SSRS 無法在排序時，使用 RowNumber() 函數，因此排序條件要設成其他條件或就不要有排序，要不然預覽時就會出現錯誤

• 預覽報表

從 GroupNO 和 RowNO 兩欄位來觀看設定是否異常，GroupNO 是從 0 開始

移除 GroupNO 欄位

GroupNO 欄位是為了方便檢視設定 RowNumber() 條件才存在，實際設計報表不需要出現

點選 GroupNO 欄位 => 滑鼠右鍵 => 刪除資料行

選擇［只刪除資料行］

預覽報表

• 延伸閱讀
• [SQL] 次序函數應用-資料分組
• 參考資料
• SSRS - Page Break
• RowNumber 函數
• 加入分頁符號
• 運算式範例 - 分頁符號
• [RDLC] ReportViewer 設計系列：設定群組之每頁小計

[SQL] Except 和 Intersect

Except 和 Intersect 可以用來比較兩個資料集的差異。

從下方文氏圖可以看出 Except 找出差集、Intersect 找出交集

利用下述範例實作

1. Except：找出資料集 1 中有，但資料集 2 沒有的，並把資料集 1 重覆資料刪除，只留一筆
2. Intersect：找出資料集 1 和資料集 2 都有的，並把重覆資料刪除，只留一筆

DECLARE @T1 TABLE (EmpNO int , EmpName nchar(10))
INSERT INTO @T1 VALUES
 (1,N'蔡一') ,
 (2,N'陳二') ,
 (3,N'張三') ,
 (4,N'李四') ,
 (5,N'王五') ,
 (1,N'蔡一') , -- 故意重覆
 (3,N'張三') , -- 故意重覆
 (4,N'李四')   -- 故意重覆

DECLARE @T2 TABLE (EmpNO int , EmpName nchar(10))
INSERT INTO @T2 VALUES
 (1,N'蔡一') ,
 (4,N'李四')

SELECT EmpNO , EmpName
FROM @T1 -- 資料集 1
EXCEPT
SELECT EmpNO , EmpName
FROM @T2 -- 資料集 2

SELECT EmpNO , EmpName
FROM @T1 -- 資料集 1
INTERSECT
SELECT EmpNO , EmpName
FROM @T2 -- 資料集 2

運算子優先順序：Intersect 優先順序高於 Except，因此下述語法會先進行資料集 2 和 資料集 3 的 INTERSECT，其結果再和資料集 1 進行 EXCEPT，結果會是上圖 Except 結果

SELECT EmpNO , EmpName
FROM @T1 -- 資料集 1
EXCEPT
SELECT EmpNO , EmpName
FROM @T1 -- 資料集 2
INTERSECT
SELECT EmpNO , EmpName
FROM @T2 -- 資料集 3

備註

1. 兩個資料集的資料行數目與順序都要相同
2. 兩個資料集的資料類型必須相容（隱含式轉換）

• 2013 DBA 天團

• 20150606

Querying with Transact-SQL Module4 測驗時，發現到 Except 會移除重複的資料，原來已經筆記過，Orz

延伸閱讀
• UNION

參考資料
• 論壇問題出處
• EXCEPT 和 INTERSECT (Transact-SQL)
• 使用 UNION、EXCEPT 和 INTERSECT 搭配其他 Transact-SQL 陳述式
• 使用 EXCEPT 和 INTERSECT 執行半聯結
• The EXCEPT and INTERSECT Operators in SQL Server
• Except只會回傳經過distinct的資料列
  

[SSRS] 遞迴階層群組

在 MSDN 看見 建立遞迴階層群組 這篇文章，動手實作並記錄

建立 Sample Data

USE [AdventureWorks2012]
GO

IF OBJECT_ID ('RSEmployee') IS NOT NULL
    DROP TABLE RSEmployee

CREATE TABLE RSEmployee (EmpNO int , EmpName nvarchar(20) , ReportsTo int)
INSERT INTO RSEmployee(EmpNO, EmpName, ReportsTo)  
VALUES
    (1, 'Bill', NULL) ,
    (2, 'Jordan', NULL) ,
    (3, 'Jacobson', NULL) ,
    (4, 'Jess', 1) ,
    (5, 'Steve', 1) ,
    (6, 'Bob', 1) ,
    (7, 'Smith', 2) ,
    (8, 'Bobbey', 2) ,
    (9, 'Steffi', 3) ,
    (10, 'Bracha', 3) ,
    (11, 'John', 5) ,
    (12, 'Michael', 6) ,
    (13, 'Paul', 6) ,
    (14, 'Lana', 7) ,
    (15, 'Johnson', 7) ,
    (16, 'Mic', 8) ,
    (17, 'Stev', 8) ,
    (18, 'Paulson', 9) ,
    (19, 'Jessica', 10)

[SQL] Store Procedure - 參數傳遞

記錄 Store Procedure（以下簡稱 SP）的三種常用的參數傳遞方式

• Sample Data

IF OBJECT_ID('Salary') IS NOT NULL
    DROP TABLE Salary

IF OBJECT_ID('Employ') IS NOT NULL
    DROP TABLE Employ

IF OBJECT_ID('uspInput') IS NOT NULL
    DROP PROCEDURE uspInput

IF OBJECT_ID('uspOutput') IS NOT NULL
    DROP PROCEDURE uspOutput

IF OBJECT_ID('uspReturn') IS NOT NULL
    DROP PROCEDURE uspReturn

CREATE TABLE Employ (EmpNO char(3) , EmpName nvarchar(8) , Leavedate date)
INSERT INTO Employ VALUES('001',N'張三','20130801')
INSERT INTO Employ VALUES('002',N'李四',NULL)
INSERT INTO Employ VALUES('003',N'王五',NULL)

CREATE TABLE Salary (EmpNO char(3),PayMonth datetime,bonus money,MonthSalary money)
INSERT INTO Salary VALUES('001','20130731',0,22000)
INSERT INTO Salary VALUES('002','20130630',1000,25000)
INSERT INTO Salary VALUES('002','20130731',3000,27000)
INSERT INTO Salary VALUES('003','20130531',5000,40000)
INSERT INTO Salary VALUES('003','20130630',7000,42000)
INSERT INTO Salary VALUES('003','20130630',8000,45000)

[Win] 啟動 Win7 Administrator

Windows 7 Administrator 帳戶預設是停用（Disable）狀態，記錄兩種開啟的方法。

圖形介面開啟

控制台 => 系統及安全性 => 系統管理工具 => 電腦管理 => 本機使用者和群組 => 使用者 => Administrator => 取消［帳戶已停用］