iT邦幫忙

0

[Report] SQL Report製作報表 (ReportView /.rdlc/DataSet)-群組小計

環境
VS2013

步驟
1.新增webform 拉reportview & ScriptManager & txtID & Button

2.新增資料集 命名為myDS.xsd
右鍵 加入/DataTable 命名為cust
插入資料行ID/NAME/TEL/ADDR

3.新增報表rdlc 命名為rp.rdlc
I.新增dataset **如果沒有看到,可至檢視/報表資料,把它打開 (Ctrl + Alt + D)
命名為data / DataSource 選myDS / Available dataset 選cust
II.新增變數ID 勾選allow 空白
https://ithelp.ithome.com.tw/upload/images/20181109/20106764z0vdQ4AGID.pnghttps://ithelp.ithome.com.tw/upload/images/20181109/20106764PiVEKF4W5e.png
https://ithelp.ithome.com.tw/upload/images/20181109/20106764MeDK58L64u.png
https://ithelp.ithome.com.tw/upload/images/20181109/20106764DUubqnmgqu.pnghttps://ithelp.ithome.com.tw/upload/images/20181109/20106764u4I3r4rx9M.png
Add Param
https://ithelp.ithome.com.tw/upload/images/20181109/20106764EmhyBx8vl9.png
https://ithelp.ithome.com.tw/upload/images/20181109/20106764nG220qHKdd.png https://ithelp.ithome.com.tw/upload/images/20181109/20106764laH5BmSSZp.png

III.由工具箱拉datatable到畫面上,再把報表資料/DataTable的 field拉到畫面上 

https://ithelp.ithome.com.tw/upload/images/20181109/20106764C6R0c0swLw.png

4.編輯webform.cs 將reportview和rdlc串起來

        protected void Button1_Click(object sender, EventArgs e)
        {
            ReportViewer rv = ReportViewer1;
            DataTable dt = new DataTable();
            dt = getDT();
            rv.LocalReport.ReportEmbeddedResource = "namespace.Report.rp.rdlc";//namespace.document.reportname.rdlc

            rv.LocalReport.SetParameters(new ReportParameter[] { new ReportParameter("ID", txtID.Text.Trim()) });

            rv.LocalReport.DataSources.Clear();
            //int cnt = dt.Rows.Count;

            rv.LocalReport.DataSources.Add(new Microsoft.Reporting.WebForms.ReportDataSource("data", dt));//第一個變數,就是報表資料集的名稱
            rv.LocalReport.Refresh();
        }
private DataTable getDT()
{
string sConnection = "data source = 127.0.0.1;initial catalog= DB; user id = sa ;password= sa ";
DataTable dt = new DataTable();
using (SqlConnection sqlConn = new SqlConnection( sConnection))
{
sqlConn.Open();
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlConn;
sqlComm.CommandTimeout = 3000;
sqlComm.CommandText = @"
select *   
from 
(
select ID='1',NAME='廖添丁',TEL='05',ADDR='雲林縣' 
union
select ID='2',NAME='黃俊雄',TEL='05',ADDR='雲林縣' 
union
select ID='3',NAME='廖峻',TEL='05',ADDR='雲林縣' 
union
select ID='4',NAME='蔡秋鳳',TEL='05',ADDR='雲林縣' 
union
select ID='5',NAME='鄭豐喜',TEL='05',ADDR='雲林縣' 
union
select ID='5',NAME='張信哲',TEL='05',ADDR='雲林縣' 
) a
where (@ID='' or ID=@ID)
";

sqlComm.Parameters.Add("@ID", SqlDbType.VarChar).Value = txtID.Text.Trim(); 

SqlDataAdapter adapter = new SqlDataAdapter(sqlComm);
adapter.Fill(dt);
return dt;
}

5.呈現
https://ithelp.ithome.com.tw/upload/images/20181109/20106764CuRigmrei2.png

加入群組小計

選取 detail row ->右鍵 Add Group /Parent Group /Group by 選column xxx >>table就會多出column xxx
重新命名group name be gp_xxx
在xxx那個column的cell,按右鍵 Insert Row/Inside Group - Below >>table就會在detail下方,多出row
在那row 的cell fx 給 ="筆數:"+ RowNumber("gp_xxx").ToString()
如果不想要群組colun xxx的顯示,可以把它刪掉

路徑1

https://ithelp.ithome.com.tw/upload/images/20181109/20106764DdKJOyrJsS.png

路徑2

https://ithelp.ithome.com.tw/upload/images/20181109/20106764s8M9H1FflP.png

右鍵/屬性/重新命名為gp_ADDR & 刪除排序

新增群組小計 在群組CELL 按右鍵 Insert Row / Inside Group Below
="筆數:"+ RowNumber("gp_ADDR").ToString()
https://ithelp.ithome.com.tw/upload/images/20181109/20106764WTkZbfUvRP.png
https://ithelp.ithome.com.tw/upload/images/20181109/20106764f69laOVFoq.png

成果

https://ithelp.ithome.com.tw/upload/images/20181109/20106764eZ1p6LZra6.png

如果不想addr column show出來,可以刪掉

https://ithelp.ithome.com.tw/upload/images/20181109/20106764m1duRqU577.pnghttps://ithelp.ithome.com.tw/upload/images/20181109/20106764UGaply8Ku5.png

群組 (小小計/小計/總計)

選取 detail row ->右鍵 Add Group /Parent Group /Group by 選column ADDR >>table就會多出column ADDR
重新命名group name be gp_ADDR
在[ADDR]那個column的cell,按右鍵 Add Group /Child Group /Group by 選column ID >>table就會多出column ID

在[ID]那個column的cell,按右鍵 Insert Row/Inside Group - Below >>table就會在下方,多出row
在那row 的cell fx 給 ="小小計:"+ RowNumber("gp_ID").ToString()
在[ADDR]那個column的cell,按右鍵 Insert Row/Inside Group - Below >>table就會在下方,多出row
在那row 的cell fx 給 ="小計:"+ RowNumber("gp_ADDR").ToString()
在[ADDR]那個column的cell,按右鍵 加入總計/ Below >>table就會在最後,多出row
在那row 的cell fx 給 ="總計:"+ RowNumber(nothing).ToString()

如果不想要群組colun ADDR的顯示,可以把它刪掉
如果不想要群組colun ID的顯示,可以把它刪掉

https://ithelp.ithome.com.tw/upload/images/20181109/20106764YdvsrEHkZr.png

###bak

將reportview和rdlc串起來(內嵌)

using Microsoft.Reporting.WebForms;//for report

ReportViewer rv = ReportViewer1; 
DataTable dt = new DataTable(); 
dt = getDT();         
rv.LocalReport.ReportEmbeddedResource = "ICRM.Report.rp.rdlc";

rv.LocalReport.SetParameters(new ReportParameter[] { new ReportParameter("ID", (txtID.Text.Trim()!='' ? txtID.Text.Trim() : "不限")) }); 

rv.LocalReport.DataSources.Clear();
//int cnt = dt.Rows.Count;

rv.LocalReport.DataSources.Add(new Microsoft.Reporting.WebForms.ReportDataSource("data", dt));//第一個變數,就是報表資料集的名稱
rv.LocalReport.Refresh();

將reportview和rdlc串起來(dataset & 複製)

using Microsoft.Reporting.WebForms;//for report

ReportViewer rv = ReportViewer1;
DataSet myds = getDateSet(YMDs );//dataset for gridview

GridView1.DataSource = myds ;
GridView1.DataBind();

rv.LocalReport.ReportPath = @"Report\rp.rdlc";

rv.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;
rv.LocalReport.DataSources.Clear();
//int cnt = myds.Tables[0].Rows.Count;

rv.LocalReport.DataSources.Add(new Microsoft.Reporting.WebForms.ReportDataSource("data", myds.Tables[0]));
rv.LocalReport.Refresh();
    

報表設計

起手式
建立datatable >加入資料表 >拉datatable field to table

順序
1.先拉出資料
2.再依群組分頁
3.再做沒資料,要顯示文字

怎麼傳入參數
1.在報表新增參數DATE1
2.程式碼
rv.LocalReport.SetParameters(new ReportParameter[] { new ReportParameter("ID", (txtID.Text.Trim()!='' ? txtID.Text.Trim() : "不限")) });

#Format #CINT #Cdate #AddYears #cstr #mid #left

fx運算式/欄位/點選值
fx運算式/參數/點選值 ID

Parameters 字串 20181101 變成107/11/01
=cstr(left(Parameters!DATE1.Value,4)-1911)+"/"+ mid(Parameters!DATE1.Value ,5,2)+ "/"+mid(Parameters!DATE1.Value ,7,2)

Parameter日期變yyyyMMdd
=Cint(Format(Parameters!YourDate.Value, "yyyyMMdd"))
https://stackoverflow.com/questions/31253180/convert-a-date-to-an-integer-in-yyyymmdd-format-in-ssrs

列印日期 本來是 yyyy/MM/dd hh:mm:ss >>> yyyy/MM/dd
="列印日期:"+Format(Globals!ExecutionTime,"yyyy/MM/dd")
="列印日期:"+Format(Cdate(Globals!ExecutionTime),"yyyy/MM/dd")

列印日期(日期變民國年)
="列印日期:"+Format(Globals!ExecutionTime.AddYears (-1911),"yyy/MM/dd")
="列印日期:"+Format(Cdate(Globals!ExecutionTime).AddYears (-1911),"yyy/MM/dd")

頁次
="頁  次:"+(Globals!PageNumber).ToSTRing() +"-"+ (Globals!TotalPages).tostring()

怎樣讓每頁都有表頭
Column Groups /Advanced Mode
Row Groups {static}屬性 設定
RepeatOnNewPage = True
KeepWithGroup = After
FixedData = True
https://blog.darkthread.net/blog/rdlc-repeat-header-each-page/

群組 分頁 (小計/總計)
https://tpu.thinkpower.com.tw/tpu/File/html/201712/20171228102952_f.html?f=3dj6j8kd38895ksgtdddd93865jhr9sn3rqkh

怎麼判斷沒資料
=IIf(RowNumber(Nothing)=0,"**無資料**","")

這個頁面(群組)的總計
="**合計** 筆數:"+RowNumber("gp_point").ToString()+" 點數:"+sum(Fields!point.Value).ToString()

如果是1就顯示logo,如果不是就不顯示
插入影像/一般 :選檔案
可見性:依據運算式顯示或隱藏 *編輯的話,在圖像按右鍵/影像屬性/可見性/依據運算式顯示或隱藏
=IIF(Parameters!TradeType.Value="1",false,true)

如果1,就顯示xxxx,如果2就顯示aaaa,如果3就顯示bbb
=IIF(Parameters!SYS.Value="1","xxxx","")
+IIF(Parameters!SYS.Value="2","aaaa","")
+IIF(Parameters!SYS.Value="3","bbb","")

奇偶列相間
=Iif(RowNumber(Nothing) Mod 2, "LemonChiffon", "White")

Q reportview為什麼沒有列印icon

因為它要activeX,要用IE才有

Q:report有空白頁

PageSize超過大小
檢查 PageSize A4 #width 29.7 #height 21
檢查 Margin #left 1cm #right 1cm
檢查報表框


尚未有邦友留言

立即登入留言