过滤界面点击查询后出现如上图错误，调试了下，报表插件中并未发现异常，求大神指点！（对了我的sql中有用WITH ROLLUP进行分组汇总不知道和这个有没有关系）
[code]using Kingdee.BOS;
using Kingdee.BOS.App;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.CommonFilter;
using Kingdee.BOS.Core.List;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Resource;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using Kingdee.BOS.App.Data;
namespace HMXJ1.Bussiness.PlugIn
{
[Description("销售业绩对比表")]
public class SalArchievementComparisonRptServicePlugIn : SysReportBaseService
{
//过滤字段
long _cusNumStart = 0L; //客户代码起
long _cusNumEnd = 0L; //客户代码至
string _year = string.Empty; //年度
string _month = string.Empty; //期间
DateTime _timeStart = new DateTime(); //开始日期
DateTime _timeEnd = new DateTime(); //结束日期
DateTime _lastTimeStart = new DateTime(); //上年开始日期
DateTime _lastTimeEnd = new DateTime(); //上年结束日期
string _attribute = string.Empty; //产品属性
//构建表头
public override ReportHeader GetReportHeaders(IRptParams filter)
{
ReportHeader header = new ReportHeader();
ListHeader headChild1 = header.AddChild("CNUMBER", new LocaleValue("客户代码"));
headChild1.Width = 100;
ListHeader headChild2 = header.AddChild("CNAME", new LocaleValue("客户名称"));
headChild2.Width = 100;
ListHeader headChild3 = header.AddChild("ATTRIBUTE", new LocaleValue("产品属性"));
headChild3.Width = 100;
ListHeader headChild4 = header.AddChild("MNUMBER", new LocaleValue("物料代码"));
headChild4.Width = 100;
ListHeader headChild5 = header.AddChild("MNAME", new LocaleValue("物料名称"));
headChild5.Width = 100;
ListHeader headChild6 = header.AddChild("QTY", new LocaleValue("数量"));
headChild6.Width = 100;
ListHeader headChild7 = header.AddChild("AMT", new LocaleValue("金额"), SqlStorageType.SqlDecimal);
headChild7.Width = 100;
ListHeader headChild8 = header.AddChild("LASTYEARQTY", new LocaleValue("上年同期数量"));
headChild8.Width = 100;
ListHeader headChild9 = header.AddChild("LASTYEARAMT", new LocaleValue("上年同期金额"), SqlStorageType.SqlDecimal);
headChild9.Width = 100;
ListHeader headChild10 = header.AddChild("QTYRATE", new LocaleValue("增长比例（数量）"));
headChild10.Width = 100;
ListHeader headChild11 = header.AddChild("AMTRATE", new LocaleValue("增长比例（金额）"));
headChild11.Width = 100;
return header;
}
//设置精度
private void SetDecimalControl()
{
List
list.Add(new DecimalControlField
{
ByDecimalControlFieldName = "AMT",
DecimalControlFieldName = "FPRECISION"
});
list.Add(new DecimalControlField
{
ByDecimalControlFieldName = "LASTYEARAMT",
DecimalControlFieldName = "FPRECISION"
});
this.ReportProperty.DecimalControlFieldList = list;
}
private string sqlBilder()
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT ROW_NUMBER() over(order by grouping(CUSTOMER.FNUMBER),CUSTOMER.FNUMBER) SEQ");
sql.AppendLine(" ,CASE");
sql.AppendLine(" WHEN GROUPING(CUSTOMER.FNUMBER) = 1 THEN '合计'");
sql.AppendLine(" WHEN GROUPING(ASSISTANTDATAENTRY_L.FDATAVALUE)=1 AND GROUPING(MATERIAL.FNUMBER)=1 THEN '小计'");
sql.AppendLine(" ELSE CUSTOMER.FNUMBER ");
sql.AppendLine(" END CNUMBER --客户编码");
sql.AppendLine(" ,CASE");
sql.AppendLine(" WHEN GROUPING(ASSISTANTDATAENTRY_L.FDATAVALUE)=1 AND GROUPING(MATERIAL.FNUMBER)=1 THEN NULL");
sql.AppendLine(" ELSE MAX(CUSTOMER_L.FNAME)");
sql.AppendLine(" END CNAME --客户名称");
sql.AppendLine(" ,CASE");
sql.AppendLine(" WHEN GROUPING(CUSTOMER.FNUMBER) = 1 THEN NULL");
sql.AppendLine(" WHEN GROUPING(ASSISTANTDATAENTRY_L.FDATAVALUE) = 1 THEN NULL");
sql.AppendLine(" WHEN GROUPING(ASSISTANTDATAENTRY_L.FDATAVALUE) = 0 AND GROUPING(MATERIAL.FNUMBER)=1 THEN '小计'");
sql.AppendLine(" ELSE ASSISTANTDATAENTRY_L.FDATAVALUE");
sql.AppendLine(" END ATTRIBUTE --产品属性");
sql.AppendLine(" ,MATERIAL.FNUMBER MNUMBER --物料代码");
sql.AppendLine(" ,CASE");
sql.AppendLine(" WHEN GROUPING(MATERIAL.FNUMBER)=1 THEN NULL");
sql.AppendLine(" ELSE MAX(MATERIAL_L.FNAME) END MNAME --物料名称");
sql.AppendLine(" ,SUM(OUTSTOCKENTRY.FREALQTY) QTY --数量");
sql.AppendLine(" ,SUM(OUTSTOCKENTRY_F.FALLAMOUNT) AMT --金额");
sql.AppendLine(" ,SUM(LASTYEAR.FREALQTY) LASTYEARQTY --上年同期数量");
sql.AppendLine(" ,SUM(LASTYEAR.FALLAMOUNT) LASTYEARAMT --上年同期金额");
sql.AppendLine(" ,CASE");
sql.AppendLine(" WHEN SUM(LASTYEAR.FREALQTY) = 0 THEN NULL");
sql.AppendLine(" WHEN GROUPING(CUSTOMER.FNUMBER) = 1 OR GROUPING(ASSISTANTDATAENTRY_L.FDATAVALUE) = 1 OR GROUPING(MATERIAL.FNUMBER) = 1 THEN NULL");
sql.AppendLine(" ELSE (SUM(OUTSTOCKENTRY.FREALQTY)-SUM(LASTYEAR.FREALQTY))/SUM(LASTYEAR.FREALQTY)");
sql.AppendLine(" END QTYRATE --增长比例（数量）");
sql.AppendLine(" ,CASE");
sql.AppendLine(" WHEN SUM(LASTYEAR.FALLAMOUNT) = 0 THEN NULL");
sql.AppendLine(" WHEN GROUPING(CUSTOMER.FNUMBER) = 1 OR GROUPING(ASSISTANTDATAENTRY_L.FDATAVALUE) = 1 OR GROUPING(MATERIAL.FNUMBER) = 1 THEN NULL");
sql.AppendLine(" ELSE(SUM(OUTSTOCKENTRY_F.FALLAMOUNT)-SUM(LASTYEAR.FALLAMOUNT))/SUM(LASTYEAR.FALLAMOUNT)");
sql.AppendLine(" END AMTRATE --增长比例（金额）");
sql.AppendLine(" ,MAX(CURRENCY.FAMOUNTDIGITS) FPRECISION --金额精度");
sql.AppendLine(" FROM T_SAL_OUTSTOCK OUTSTOCK --销售出库单");
sql.AppendLine(" LEFT JOIN T_SAL_OUTSTOCKFIN OUTSTOCKFIN ON OUTSTOCK.FID = OUTSTOCKFIN.FID --财务信息");
sql.AppendLine(" LEFT JOIN T_SAL_OUTSTOCKENTRY OUTSTOCKENTRY ON OUTSTOCK.FID = OUTSTOCKENTRY.FID");
sql.AppendLine(" LEFT JOIN T_SAL_OUTSTOCKENTRY_F OUTSTOCKENTRY_F ON OUTSTOCKENTRY.FENTRYID = OUTSTOCKENTRY_F.FENTRYID");
sql.AppendLine(" --客户");
sql.AppendLine(" LEFT JOIN T_BD_CUSTOMER CUSTOMER ON OUTSTOCK.FCUSTOMERID = CUSTOMER.FCUSTID");
sql.AppendLine(" LEFT JOIN T_BD_CUSTOMER_L CUSTOMER_L ON CUSTOMER.FCUSTID = CUSTOMER_L.FCUSTID");
sql.AppendLine(" --物料");
sql.AppendLine(" LEFT JOIN T_BD_MATERIAL MATERIAL ON MATERIAL.FMATERIALID = OUTSTOCKENTRY.FMATERIALID");
sql.AppendLine(" LEFT JOIN T_BD_MATERIAL_L MATERIAL_L ON MATERIAL.FMATERIALID = MATERIAL_L.FMATERIALID");
sql.AppendLine(" --产品属性");
sql.AppendLine(" LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L ASSISTANTDATAENTRY_L ON MATERIAL.FASSISTANT1 = ASSISTANTDATAENTRY_L.FENTRYID");
sql.AppendLine(" --上一年");
sql.AppendLine(" LEFT JOIN (");
sql.AppendLine(" SELECT T1.FCUSTOMERID,T2.FMATERIALID,T2.FREALQTY,T3.FALLAMOUNT");
sql.AppendLine(" FROM T_SAL_OUTSTOCK T1");
sql.AppendLine(" LEFT JOIN T_SAL_OUTSTOCKENTRY T2 ON T1.FID = T2.FID");
sql.AppendLine(" LEFT JOIN T_SAL_OUTSTOCKENTRY_F T3 ON T2.FENTRYID = T3.FENTRYID");
//sql.AppendLine(" WHERE T1.FDATE >= '" + _lastTimeStart + "' AND T1.FDATE < '" + _lastTimeEnd + "'" );
sql.AppendLine(" ) LASTYEAR ON LASTYEAR.FCUSTOMERID = OUTSTOCK.FCUSTOMERID AND LASTYEAR.FMATERIALID = OUTSTOCKENTRY.FMATERIALID");
sql.AppendLine(" --币别");
sql.AppendLine(" LEFT JOIN T_BD_CURRENCY CURRENCY ON CURRENCY.FCURRENCYID = OUTSTOCKFIN.FSETTLECURRID ");
sql.AppendLine(" LEFT JOIN T_BD_CURRENCY_L CURRENCY_L ON CURRENCY_L.FCURRENCYID = CURRENCY.FCURRENCYID AND CURRENCY_L.FLOCALEID = 2052 ");
//sql.AppendLine(" WHERE OUTSTOCK.FDATE >= '" + _timeStart + "' AND OUTSTOCK.FDATE < '" + _timeEnd + "'");
//sql.AppendLine(" AND CUSTOMER.FNUMBER >= '" + _cusNumStart + "' AND CUSTOMER.FNUMBER < '" + _cusNumEnd + "'");
//sql.AppendLine(" AND MATERIAL.FASSISTANT1 = '" + _attribute + "'");
sql.AppendLine(" GROUP BY CUSTOMER.FNUMBER,ASSISTANTDATAENTRY_L.FDATAVALUE,MATERIAL.FNUMBER WITH ROLLUP");
//sql.AppendLine(" ORDER BY GROUPING(CUSTOMER.FNUMBER),CUSTOMER.FNUMBER,GROUPING(ASSISTANTDATAENTRY_L.FDATAVALUE),ASSISTANTDATAENTRY_L.FDATAVALUE,GROUPING(MATERIAL.FNUMBER),MATERIAL.FNUMBER");
return Convert.ToString(sql);
}
public override void Initialize()
{
base.Initialize();
this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
this.ReportProperty.ReportName = new LocaleValue("销售业绩对比表", base.Context.UserLocale.LCID);
this.ReportProperty.IsUIDesignerColumns = false;
this.ReportProperty.IsGroupSummary = true;
this.ReportProperty.SimpleAllCols = true;
this.SetDecimalControl();
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
this.FilterParameter(filter);
base.BuilderReportSqlAndTempTable(filter, tableName);
string sql = string.Format(@"/*dialect*/
SELECT
SEQ
,CNUMBER --客户编码
,CNAME --客户名称
,ATTRIBUTE --产品属性
,MNUMBER --物料代码
,MNAME --物料名称
,QTY --数量
,AMT --金额
,LASTYEARQTY --上年同期数量
,LASTYEARAMT --上年同期金额
,QTYRATE --增长比例（数量）
,AMTRATE --增长比例（金额）
,FPRECISION --金额精度
INTO {0}
FROM ({1}) T
ORDER BY SEQ
", tableName, this.sqlBilder());
DBUtils.ExecuteDynamicObject(this.Context, sql);
}
private void FilterParameter(IRptParams filter)
{
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
if (filter.FilterParameter.CustomFilter != null)
{
DynamicObject cusObjStart = dyFilter["CusIdStart"] as DynamicObject;
DynamicObject cusObjEnd = dyFilter["CusIdEnd"] as DynamicObject;
_cusNumStart = Convert.ToInt64(cusObjStart["Number"]); //客户编号起
_cusNumEnd = Convert.ToInt64(cusObjEnd["Number"]); //客户编号至
_attribute = Convert.ToString(dyFilter["Attribute_Id"]); //产品属性
_year = Convert.ToString(dyFilter["Year"]); //年度
_month = Convert.ToString(dyFilter["Month"]); //期间
_timeStart = DateTime.Parse(_year + "/" + _month); //开始日期
_timeEnd = _timeStart.AddMonths(1); //结束日期
_lastTimeStart = _timeStart.AddYears(-1); //上年开始日期
_lastTimeEnd = _timeEnd.AddYears(-1); //上年结束日期
}
}
///
/// 报表单据头
///
///
///
public override ReportTitles GetReportTitles(IRptParams filter)
{
ReportTitles title = new ReportTitles();
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
if (dyFilter != null)
{
title.AddTitle("CusIdStart", Convert.ToString(_cusNumStart));
title.AddTitle("CusIdEnd", Convert.ToString(_cusNumEnd));
title.AddTitle("Year", _year);
title.AddTitle("Month", _month);
}
return title;
}
}
}
[/code]
推荐阅读