1 --Finally page procedure 2 create procedure RP_BIREPORTSO 3 @year INT, 4 @month INT 5 as 6 begin 7 WITH table_group AS ( 8 SELECT ROW_NUMBER() OVER(ORDER BY a.item_id) num_groupby, 9 a.item_id 10 FROM ( 11 SELECT wite.item_id, 12 wite.create_datetime 13 FROM ( 14 SELECT waus.user_id, 15 waac.action_id 16 FROM ws_account_action waac 17 JOIN ws_account_user waus 18 ON waac.ws_uid = waus.ws_uid 19 WHERE waac.parent_action_id IS NULL 20 AND waac.ws_action_type = 'BI' 21 AND waac.the_year = @year 22 AND waac.the_month = @month 23 ) AS waac 24 JOIN ( 25 SELECT wite.item_id, 26 ware.action_id, 27 wite.create_datetime 28 FROM ws_account_request ware 29 LEFT JOIN ws_item wite 30 ON ware.item_id = wite.item_id 31 WHERE wite.enable_flg = 1 32 33 ) AS wite 34 ON wite.action_id = waac.action_id 35 UNION 36 SELECT wite.item_id, 37 wite.create_datetime 38 FROM ws_item wite, 39 ( 40 SELECT waus.user_id 41 FROM ws_account_action waac 42 JOIN ws_account_user waus 43 ON waac.ws_uid = waus.ws_uid 44 WHERE waac.parent_action_id IS NULL 45 AND waac.ws_action_type = 'BI' 46 AND waac.the_year = @year 47 AND waac.the_month = @month 48 AND waac.action_id NOT IN (SELECT DISTINCT 49 action_id 50 FROM 51 ws_account_request) 52 ) AS waac 53 WHERE wite.enable_flg = 1 54 55 ) a 56 GROUP BY 57 a.item_id 58 ) 59 --Union exist relation data and other need display data 60 SELECT 61 tg.num_groupby, 62 alldata.item_id, 63 alldata.ledge_folio, 64 alldata.item_desc_en, 65 alldata.unit, 66 alldata.create_datetime, 67 alldata.quantity_issued_count, 68 alldata.user_id, 69 alldata.quantity_issued_total 70 71 FROM ( 72 SELECT wite.item_id, 73 wite.ledge_folio, 74 wite.item_desc_en, 75 wite.unit, 76 wite.create_datetime, 77 wite.approved_qty AS quantity_issued_count, 78 waac.user_id, 79 ( 80 SELECT SUM(ware2.approved_qty) 81 FROM ws_account_request ware2 82 LEFT JOIN ws_item wite2 83 ON ware2.item_id = wite2.item_id 84 WHERE wite2.enable_flg = 1 85 AND wite2.item_id = wite.item_id 86 GROUP BY 87 wite2.item_id 88 ) AS quantity_issued_total 89 FROM ( 90 SELECT waus.user_id, 91 waac.action_id 92 FROM ws_account_action waac 93 JOIN ws_account_user waus 94 ON waac.ws_uid = waus.ws_uid 95 WHERE waac.parent_action_id IS NULL 96 AND waac.ws_action_type = 'BI' 97 AND waac.the_year = @year 98 AND waac.the_month = @month 99 ) AS waac 100 JOIN ( 101 SELECT wite.item_id, 102 wite.ledge_folio, 103 wite.item_desc_en, 104 wite.unit, 105 wite.create_datetime, 106 ware.approved_qty, 107 ware.action_id 108 FROM ws_account_request ware 109 LEFT JOIN ws_item wite 110 ON ware.item_id = wite.item_id 111 WHERE wite.enable_flg = 1 112 ) AS wite 113 ON wite.action_id = waac.action_id 114 UNION 115 SELECT wite.item_id, 116 wite.ledge_folio, 117 wite.item_desc_en, 118 wite.unit, 119 wite.create_datetime, 120 0 AS quantity_issued_count, 121 waac.user_id, 122 ( 123 SELECT SUM(ware2.approved_qty) 124 FROM ws_account_request ware2 125 LEFT JOIN ws_item wite2 126 ON ware2.item_id = wite2.item_id 127 WHERE wite2.enable_flg = 1 128 AND wite2.item_id = wite.item_id 129 and waac.action_id = ware2.action_id 130 GROUP BY 131 wite2.item_id 132 ) AS quantity_issued_total 133 FROM ws_item wite, 134 ( 135 SELECT waus.user_id,waac.action_id 136 FROM ws_account_action waac 137 JOIN ws_account_user waus 138 ON waac.ws_uid = waus.ws_uid 139 WHERE waac.parent_action_id IS NULL 140 AND waac.ws_action_type = 'BI' 141 AND waac.the_year = @year 142 AND waac.the_month = @month 143 AND waac.action_id NOT IN (SELECT DISTINCT 144 action_id 145 FROM 146 ws_account_request) 147 ) AS waac 148 WHERE wite.enable_flg = 1 149 ) alldata 150 LEFT JOIN table_group tg 151 ON alldata.item_id = tg.item_id 152 end 153 go
SQL查詢結果：
（2）rdlc報表配置方面：
1 private void InitData()
2 {
3 int year = int.Parse(Request["year"]);
4 int month = int.Parse(Request["month"]);
5 DataTable dt = Bll.Report.BiReport.GetReportBySO(year, month);
6 int count = dt.Rows.Count;
7 if (count == 0)
8 {
9 this.RegisterJS("alert('Without relevant data!');window.opener=null;window.open('','_self');window.close();");
10 return;
11 }
12 var q = from p in dt.AsEnumerable()
13 group p by p["user_id"].ToString() into g
14 select new
15 {
16 UserId = g.Key
17 };
18 //int columnCount = q.Count<object>();
19 int allDataCount = int.Parse(dt.Rows[count - 1]["num_groupby"].ToString());
20 int rowCount = 33;
21 if (allDataCount % rowCount != 0)
22 {
23 int addRowCount = rowCount - allDataCount % rowCount;
24 DataRow dr;
25 for (int i = 0; i < addRowCount; i++)
26 {
27 ++allDataCount;
28 foreach (var item in q)
29 {
30 dr = dt.NewRow();
31 dr["user_id"] = item.UserId;
32 dr["num_groupby"] = allDataCount;
33 dr["item_id"] = -i;
34 dr["create_datetime"] = "1900/1/1";
35 dt.Rows.Add(dr);
36 }
37 }
38 }
39 //Control every first record is right
40 DataView dv = dt.DefaultView;
41 dv.Sort = "num_groupby,quantity_issued_total desc";
42 dt = dv.ToTable();
43 DateTime startDate = DateTime.MinValue, endDate = DateTime.MinValue;
44 StartDateAndEndDate(ref startDate, ref endDate);
45 ReportViewer1.Visible = true;
46 ReportViewer1.LocalReport.ReportPath = MapPath("BiReportSo.rdlc");
47 ReportDataSource rds = new ReportDataSource("dsBiReportSo", dt);
48 this.ReportViewer1.LocalReport.SetParameters(new ReportParameter("parmDept", BelongUnitName()));
49 this.ReportViewer1.LocalReport.SetParameters(new ReportParameter("parmStartDate", string.Format("{0:d}", startDate)));
50 this.ReportViewer1.LocalReport.SetParameters(new ReportParameter("parmEndDate", string.Format("{0:d}", endDate)));
51 ReportViewer1.LocalReport.DataSources.Clear();
52 ReportViewer1.LocalReport.DataSources.Add(rds);
53 }
