# 第一步 新建一个简单账表加上表头标签  # 第二步 新建一个过滤框  # 第三步 修改过滤窗口标识  # 第四步 注册插件  # python代码实现 ```python import clr clr.AddReference("System") clr.AddReference("System.Core") clr.AddReference("Kingdee.BOS") clr.AddReference("Kingdee.BOS.App") clr.AddReference("Kingdee.BOS.Core") clr.AddReference("Kingdee.BOS.Contracts") clr.AddReference("Kingdee.BOS.DataEntity") #引入命名空间 from Kingdee.BOS import * from Kingdee.BOS.App import * from Kingdee.BOS.App.Data import * from Kingdee.BOS.Contracts import * from Kingdee.BOS.Contracts.Report import * from Kingdee.BOS.Core.Report.PlugIn import * from Kingdee.BOS.Core.Report import * from Kingdee.BOS.Orm.DataEntity import * from Kingdee.BOS.Util import * from System import * #import datetime #全局变量 global sBillDataTempTable; sBillDataTempTable = ""; global _dtDateFrom; #存放开始日期数据 global _dtDateTo;#存放结束日期数据 global _matter;#存放物料标识数据 def Initialize(): this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL; this.ReportProperty.ReportName = LocaleValue("老李头的简单报表示例Python", this.Context.UserLocale.LCID); this.ReportProperty.IsGroupSummary = False; #合计分组 this.ReportProperty.IsUIDesignerColumns = False; #获取过滤条件 用于数据源过滤 def GetFilter(filter): dyFilter = filter.FilterParameter.CustomFilter; global _dtDateFrom; global _dtDateTo; global _matter; #获取过滤框里的数据 #GetBaseDataByKey 单选基础资料取值 #GetMulBaseDataByKey 多选基础资料 #GetDataByKey 文本，日期 取值 _dtDateFrom = GetDataByKey(dyFilter, "FBeginDate").ToString() if GetDataByKey(dyFilter, "FBeginDate") != "" else ""; _dtDateTo = GetDataByKey(dyFilter, "FEndDate").ToString() if GetDataByKey(dyFilter, "FEndDate") != "" else ""; _matter = GetBaseDataByKey(dyFilter,"F_MATTER","Id") if GetBaseDataByKey(dyFilter,"F_MATTER","Id") != "" else ""; return dyFilter; #构造取数Sql，取数据填充到临时表：tableName def BuilderReportSqlAndTempTable(filter,tableName): dyFilter = GetFilter(filter); global sBillDataTempTable sBillDataTempTable = tableName; CreatBillDataTempTable(); SetDataToRptTable(filter); #创建【动态构建列存储过程】数据临时表 def CreatBillDataTempTable(): sql = ""; #数据源设置 使用存储过程后期可以不需要修改代码直接修改存储过程实现动态列展示 #使用存储过程获取数据参数：临时表名称，开始时间，结束时间，物料标识 sql =("/*dialect*/ EXEC MindeeSimpleTable '{0}','{1}','{2}','{3}'").format(sBillDataTempTable,_dtDateFrom,_dtDateTo,_matter); #使用SQL语句获取数据 #sql =("""/*dialect*/SELECT T1.FBILLNO 单据号 ,T2.FQTY 数量 ,T3.FNUMBER 物料代码 ,T4.FNAME 物料名称 ,T4.FDESCRIPTION 描述 ,ROW_NUMBER() OVER(ORDER BY T1.FID,T2.FENTRYID) FIDENTITYID into {0} FROM T_PUR_POORDER t1 INNER JOIN T_PUR_POORDERentry t2 ON T2.FID=T1.FID INNER JOIN T_BD_MATERIAL T3 ON T3.FMATERIALID=T2.FMATERIALID INNER JOIN T_BD_MATERIAL_L T4 ON T4.FMATERIALID=T3.FMASTERID WHERE 1=1 AND 1 = CASE WHEN ISNULL('{1}','') = '' THEN 1 ELSE CASE WHEN ISNULL(T1.FDATE,'') >= '{1}' THEN 1 ELSE 0 END END AND 1 = CASE WHEN ISNULL('{2}','') = '' THEN 1 ELSE CASE WHEN ISNULL(T1.FDATE,'') <= '{2}' THEN 1 ELSE 0 END END""").format(sBillDataTempTable,_dtDateFrom,_dtDateTo); DBUtils.ExecuteDynamicObject(this.Context, sql); #动态构建列 def GetReportHeaders(filter): header = ReportHeader(); localEid=this.Context.UserLocale.LCID; sql = ("""SELECT T1.system_type_id as FDateTypeId , t3.name AS FDateType,t1.name AS FName FROM sys.columns t1 INNER JOIN sys.objects t2 ON t2.object_id = t1.object_id INNER JOIN sys.types t3 ON t3.user_type_id=t1.user_type_id WHERE t2.name='{0}' AND t2.type='u' """).format(sBillDataTempTable); RecordData = DBUtils.ExecuteDynamicObject(this.Context, sql); for item in RecordData: #隐藏不显示这个字段 FIDENTITYID #不知为什么报表格式会显示格式是 FIDENTITYID,FIDENTITYID (求解答) if item["FName"].ToString() == str("FIDENTITYID"): continue header.AddChild(item["FName"].ToString(),LocaleValue(item["FName"].ToString(), localEid), GetEnumByValue(SqlStorageType,item["FDateTypeId"].ToString()), True); return header; #设置报表头 def GetReportTitles(filter): titles = ReportTitles(); #把过滤框的日期放到报表头 titles.AddTitle("FBeginDate", str(filter.FilterParameter.CustomFilter["FBeginDate"])); titles.AddTitle("FEndDate", str(filter.FilterParameter.CustomFilter["FEndDate"])); return titles; #展示数据表到前台 def SetDataToRptTable(filter): sqlstr=("SELECT * FROM {0}").format(sBillDataTempTable); sqlstr2=("SELECT COUNT(*) AS Frow FROM sys.objects WHERE name='{0}' AND type='u'").format(sBillDataTempTable); ROWS = DBUtils.ExecuteDynamicObject(this.Context,sqlstr2); if str(ROWS[0]["Frow"]) != "0": DBUtils.Execute(this.Context, sqlstr); #获取DynamicObject数据包中指定key的值 def GetDataByKey(doFilter, sKey): sReturnValue = ""; if doFilter is not None and doFilter[sKey] is not None and len(str(doFilter[sKey])) > 0: sReturnValue = doFilter[sKey].ToString(); return sReturnValue; #获取DynamicObject数据包中指定key的基础资料的指定属性的值 def GetBaseDataByKey(doFilter,sKey,sItem): sReturnValue=""; if doFilter is not None and doFilter[sKey] is not None and len(str(doFilter[sKey])) > 0: doTemp = doFilter[sKey]; sReturnValue = doTemp[sItem].ToString(); return sReturnValue; #获取DynamicObject数据包中指定key的基础资料的指定属性的值 def GetMulBaseDataByKey(doFilter,sKey,sItem): sReturnValue = ""; if doFilter is not None and doFilter[sKey] is not None and len(str(doFilter[sKey])) > 0: for item in doFilter[sKey]: if len(str(GetBaseDataByKey(item,sKey,sItem)))>0: sReturnValue = "{0}{1}".format(sReturnValue, GetBaseDataByKey(item,sKey,sItem)+","); return sReturnValue.TrimEnd(',') #replace()代替C# TrimEnd(',')函数 #枚举操作 def GetEnumByValue(enumType,value): return Enum.Parse(enumType, value); ``` # SQLSERVER 存储过程 ```sql -- ============================================= -- Author: 敏蝶老李头 -- Create date: 2023-04-11 -- Description: 简单报表演示 -- ============================================= ALTER PROCEDURE [dbo].[MindeeSimpleTable] -- Add the parameters for the stored procedure here @TableName varchar(50), @FBeginDate varchar(50), @FEndDate varchar(50), @Matter int AS BEGIN SELECT T1.FBILLNO 单据号 ,T2.FQTY 数量 ,T3.FNUMBER 物料代码 ,T4.FNAME 物料名称 ,T4.FDESCRIPTION 描述 ,ROW_NUMBER() OVER(ORDER BY T1.FID,T2.FENTRYID) FIDENTITYID INTO #DATA FROM T_PUR_POORDER t1 INNER JOIN T_PUR_POORDERentry t2 ON T2.FID=T1.FID INNER JOIN T_BD_MATERIAL T3 ON T3.FMATERIALID=T2.FMATERIALID INNER JOIN T_BD_MATERIAL_L T4 ON T4.FMATERIALID=T3.FMASTERID WHERE 1=1 AND 1 = CASE WHEN ISNULL(@FBeginDate,'') = '' THEN 1 ELSE CASE WHEN ISNULL(T1.FDATE,'') >= @FBeginDate THEN 1 ELSE 0 END END AND 1 = CASE WHEN ISNULL(@FEndDate,'') = '' THEN 1 ELSE CASE WHEN ISNULL(T1.FDATE,'') <= @FEndDate THEN 1 ELSE 0 END END AND 1 = CASE WHEN ISNULL(@Matter,0) = 0 THEN 1 ELSE CASE WHEN ISNULL(T3.FMATERIALID,'') = @Matter THEN 1 ELSE 0 END END IF OBJECT_ID(@TableName,N'U') is null BEGIN --不存在 新增表 DECLARE @NEWTABLESQL VARCHAR(100)=N'SELECT * INTO '+@TableName+' FROM #DATA' EXEC(@NEWTABLESQL) END ELSE BEGIN --存在 删除数据表重新插入 EXEC(N'DROP TABLE '+ @TableName+'') DECLARE @UPDATESQL VARCHAR(100)=N'SELECT * INTO '+@TableName+' FROM #DATA' EXEC(@UPDATESQL) END SELECT * FROM #DATA DROP TABLE #DATA END ``` # 功能实现截图  ***

报错这样的错，调试过程发现存储过程里面，没有把数据插入到对应的临时表，把sql改为select into，可正常执行，请教大佬这是什么原因呢？

如何实现单据号小计，总计呢？

我做了一个按天展来生成动态列的一个分析表，打开报这个错，请教一下大佬们原因出来哪里？

列名不能出现数字吗

过滤框怎么与参数对应referenced before assignment

走过路过的大佬可否解答一下 FIDENTITYID 这个字段为何这样