Group-based paging and rdlc reports under the rdlc report Matrix Control

Group-based paging and rdlc reports under the rdlc report Matrix Control
Scenario:Use rdlc to develop reports. For example, the Order product report shows multiple orders. An order consists of multiple products that are dynamically generated, and the total number of products in each order is counted. Database-level analysis: this report is a cross report. For example, for five orders and three products, a total of 15 database records should be generated, rather than five.Technical difficulties:(1) Dynamic Column generation using the Matrix control (2) count the total number of multiple products in each order to ensure that the first data of each order is correct, it does not matter if the second or third data is NULL (3) control the display of 33 records per page, rather than control the page by default height (4) because the matrix control is used, therefore, the serial number on the page must be properly controlled from the database.Final effect: (1) database:

  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 waac100                       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_id108                                FROM   ws_account_request ware109                                       LEFT JOIN ws_item wite110                                            ON  ware.item_id = wite.item_id111                                WHERE  wite.enable_flg = 1112                            ) AS wite113                            ON  wite.action_id = waac.action_id114                UNION115                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 ware2125                                  LEFT JOIN ws_item wite2126                                       ON  ware2.item_id = wite2.item_id127                           WHERE  wite2.enable_flg = 1128                                  AND wite2.item_id = wite.item_id129                                  and waac.action_id = ware2.action_id130                           GROUP BY131                                  wite2.item_id132                       ) AS quantity_issued_total133                FROM   ws_item wite,134                       (135                           SELECT waus.user_id,waac.action_id136                           FROM   ws_account_action waac137                                  JOIN ws_account_user waus138                                       ON  waac.ws_uid = waus.ws_uid139                           WHERE  waac.parent_action_id IS NULL140                                  AND waac.ws_action_type = 'BI'141                                  AND waac.the_year = @year142                                  AND waac.the_month = @month143                                  AND waac.action_id NOT IN (SELECT DISTINCT 144                                                                    action_id145                                                             FROM   146                                                                    ws_account_request)147                       ) AS waac148                WHERE  wite.enable_flg = 1149            ) alldata150            LEFT JOIN table_group tg151                 ON  alldata.item_id = tg.item_id152 end153 go

SQL query result:

  (2) rdlc report Configuration:

• Matrix Control
• Add groups and control pagination using grouping expressions
• Use of rdlc built-in functions

= IIF (Fields! Quantity_issued_count.Value <> 0, Fields! Quantity_issued_count.Value, "---") = IIf (IsNothing (Fields! Quantity_issued_total.Value), 0, Fields! Quantity_issued_total.Value) (3) page background processing:

 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 g14             select new15             {16                 UserId = g.Key17             };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 right40     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 }