水晶报表多表查询 数据重复问题,急呀!!
请教各位高手,我做水晶报表多表查询 数据重复问题,一直未能解决。
代码如下:
//客户业务员显示
main.p_Suppliers.Visible = true; //客户业务员往来帐显示
main.p_Account.Visible = false; //供应商业务员往来帐隐藏
main.p_Suppliers.Dock = DockStyle.Fill;
//绑定列表控件
sql_select_table = string.Format(" select dbill_date,ino_id,cname,ccus_id,cdigest,md,mc,cdept_id,iperiod from GL_Accvouch where ccode='{0}' and cname='{1}' and (GL_Accvouch.iperiod between {2} and {3}) group by dbill_date,ino_id,cname,ccus_id,cdigest,md,mc ,cdept_id,iperiod union select dbill_date,ino_id,cname,ccus_id,cdigest,md,mc,cdept_id,iperiod from GL_Accvouch where ccode='{0}' and cname='{1}' and GL_Accvouch.iperiod=0", cb_CCode.SelectedValue, cb_Person.Text, num_MonthStart.Value.ToString(), num_MonthEnd.Value.ToString());
sql_select_lastyear = string.Format(" select cname,ccus_id,cdigest,cdept_id,iperiod,ccode,cCusName from GL_Accvouch join Customer on Customer.cCusCode=ccus_id where ccode='{0}' and cname='{1}' and GL_Accvouch.iperiod=0 ", cb_CCode.SelectedValue, cb_Person.Text);
sql_select_accass = string.Format("select mb,me,cbegind_c,cendd_c,ccus_id,cdept_id,iperiod,ccode from GL_accass where ccode='{0}' and iperiod between {1} and {2}", cb_CCode.SelectedValue, num_MonthStart.Value.ToString(), num_MonthEnd.Value.ToString());
sql_select_Customer = "select cCusName,cCusCode from Customer";
sql_select_Mc = string.Format("select sum(mc) as a from GL_Accvouch where ccode='{0}' and cname='{1}' and GL_Accvouch.iperiod=0 and ccus_id=@CCus_Id", cb_CCode.SelectedValue, cb_Person.Text);
//构建DataTable
dt = new DataTable("LastYear");
dt.Columns.Add("cname",typeof(string));
dt.Columns.Add("ccus_id", typeof(string));
dt.Columns.Add("mc", typeof(decimal));
dt.Columns.Add("cdigest",typeof(string));
dt.Columns.Add("cdept_id", typeof(string));
dt.Columns.Add("iperiod", typeof(int));
dt.Columns.Add("ccode", typeof(string));
dt.Columns.Add("cCusName", typeof(string));
DataSet ds = new DataSet();
SqlDataReader sdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql_select_lastyear, null);
try
{
while (sdr.Read())
{
DataRow dr = dt.NewRow();
dr[0] = sdr[0];
dr[1] = sdr[1];
dr[3] = sdr[2];
dr[4] = sdr[3];
dr[5] = sdr[4];
dr[6] = sdr[5];
dr[7] = sdr[6];
object obj = SQLHelper.ExecuteScalar(CommandType.Text, sql_select_Mc, new SqlParameter("@CCus_Id",sdr[1]));
dr[2] = (obj != null && DBNull.Value != obj) ? obj : 0;
dt.Rows.Add(dr);
}
}
catch
{
}
finally
{
sdr.Close();
SQLHelper.closeConn();
}
//查询余额表
sda = SQLHelper.ExecuteDataAdapter(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql_select_table, null);
sda.Fill(ds, "ClerkA");
//查询客户
sda = SQLHelper.ExecuteDataAdapter(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql_select_Customer, null);
sda.Fill(ds, "Customer");
//查询科目总账
sda = SQLHelper.ExecuteDataAdapter(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql_select_accass, null);
sda.Fill(ds, "GL_accass");
sda.Dispose();
SQLHelper.closeConn();
ds.Tables.Add(dt); //将上年结转余额表添加到数据集
/////////////////////////////////////////添加到报表中/////////////////////////////////////
Clerk_A document = new Clerk_A();
document.Load(AppDomain.CurrentDomain.BaseDirectory + "Clerk_A.rpt");
document.SetDataSource(ds);
main.crv_Clerk_A.ReportSource = document;
//////////////////////////////////////////////////////////////////////////////////////////////////////////
数据集共有四个表:LastYear,ClerkA,Customer,GL_accass 查询结果数据重复。我也设置了表之间的关系了,但是仍然数据重复。