简单报表多表头汇总示例原创
865次浏览
编辑于2022年10月12日 20:22:39
简单报表支持多级表头展示,报表可以通过SQL对字段进行分组合计。
本示例展示采购订单按照供应商分组,展示供应商每个月的采购数量,报表效果如下:
多级表头是通过 header.AddChild来实现,分组合计是通过SQL实现,如果想实现多个表数据联合展示,需要写不同的子查询来关联。但是需要注意,多个分组合计子查询,查询性能比较低,需要注意筛选数据
具体代码如下:
using Kingdee.BOS; using Kingdee.BOS.App.Data; using Kingdee.BOS.Contracts.Report; using Kingdee.BOS.Core.Report; using Kingdee.BOS.Util; using System; using System.Collections.Generic; using System.ComponentModel; using System.Linq; using System.Text; namespace Witt.Cloud.PlugIn.Report { [HotUpdate] [Description("多表头合计报表")] public class MulHeadrSumRptPlugIn : SysReportBaseService { public override void Initialize() { base.Initialize(); this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL; this.IsCreateTempTableByPlugin = true; this.ReportProperty.IsUIDesignerColumns = false; this.ReportProperty.IsGroupSummary = true; } /// <summary> /// 向报表临时表,插入报表数据 /// </summary> /// <param name="filter"></param> /// <param name="tableName"></param> public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName) { //gen sql : ROW_NUMBER() OVER(ORDER BY t1.FMATERIALID asc ) FIDENTITYID if (filter.FilterParameter.SortString.IsNullOrEmpty()) { this.KSQL_SEQ = string.Format(this.KSQL_SEQ, " t1.FSupName asc"); } else { this.KSQL_SEQ = string.Format(this.KSQL_SEQ, filter.FilterParameter.SortString); } // 取数SQL string sql = string.Format(@"/*dialect*/ select t1.* ,{0} into {1} from (select t3.FSUPPLIERID,isnull(t3_L.FNAME,'Unknow') as FSupName, SUM(CASE WHEN MONTH(T1.FDATE) = 1 THEN T2.FQTY ELSE 0 END) AS M1, SUM(CASE WHEN MONTH(T1.FDATE) = 2 THEN T2.FQTY ELSE 0 END) AS M2, SUM(CASE WHEN MONTH(T1.FDATE) = 3 THEN T2.FQTY ELSE 0 END) AS M3, SUM(CASE WHEN MONTH(T1.FDATE) = 4 THEN T2.FQTY ELSE 0 END) AS M4, SUM(CASE WHEN MONTH(T1.FDATE) = 5 THEN T2.FQTY ELSE 0 END) AS M5 from t_PUR_POOrder t1 left join t_PUR_POOrderEntry t2 on t1.FID = t2.FID left join T_BD_SUPPLIER t3 on t1.FSUPPLIERID = t3.FSUPPLIERID left join T_BD_SUPPLIER_L t3_L on t3.FSUPPLIERID = t3_L.FSUPPLIERID and t3_L.FLOCALEID=2052 where FDATE>='2022-01-01' and FDATE <='2022-12-31 23:59:59' GROUP BY t3.FSUPPLIERID, t3_L.FNAME) t1 --可以join其他关联表 ", KSQL_SEQ, tableName); DBUtils.ExecuteDynamicObject(this.Context, sql); } public override ReportHeader GetReportHeaders(IRptParams filter) { // FID, FEntryId, 编号、状态、物料、数量、单位、单位精度、单价、价税合计 ReportHeader header = new ReportHeader(); // 编号 var supName = header.AddChild("FSupName", new LocaleValue("供应商")); supName.ColIndex = 0; var month = header.AddChild("FMonth", new LocaleValue("月份")); month.ColIndex = 1; var m1 = month.AddChild("M1", new LocaleValue("1月")); m1.SubColIndex = 1; var m2 = month.AddChild("M2", new LocaleValue("2月")); m1.SubColIndex = 1; var m3 = month.AddChild("M3", new LocaleValue("3月")); m1.SubColIndex = 1; var m4 = month.AddChild("M4", new LocaleValue("4月")); m1.SubColIndex = 1; var m5 = month.AddChild("M5", new LocaleValue("5月")); m1.SubColIndex = 1; return header; } } }
推荐阅读