C# Winform 使用SUM CASE WHEN实现动态列交叉数据报表

  C# Winform 使用SUM CASE WHEN实现动态列交叉数据报表

C# Winform 使用SUM CASE WHEN实现动态列交叉数据报表


使用SUM CASE WHEN实现动态列交叉报表


a) 根据查询条件返回不同字段的结果数据。

b) 根据查询条件自动创建表格的列。




1. 按年度生成动态列的SQL脚本


SQL Code:

SELECT Account,OperationType,
SUM(CASE WHEN YEAR(OperationTime)=2015 THEN 1 ELSE 0 END) AS Y2015,
SUM(CASE WHEN YEAR(OperationTime)= 2016 THEN 1 ELSE 0 END) AS Y2016,
SUM(CASE WHEN YEAR(OperationTime)= 2017 THEN 1 ELSE 0 END) AS Y2017,
SUM(CASE WHEN YEAR(OperationTime)= 2018 THEN 1 ELSE 0 END) AS Y2018,
SUM(CASE WHEN YEAR(OperationTime)= 2019 THEN 1 ELSE 0 END) AS Y2019,
SUM(CASE WHEN YEAR(OperationTime)= 2020 THEN 1 ELSE 0 END) AS Y2020,
SUM(CASE WHEN YEAR(OperationTime)= 2021 THEN 1 ELSE 0 END) AS Y2021
FROM sys_LogOperation
GROUP BY Account,OperationType;

//来源:C/S框架网 | www.csframework.com | QQ:23404761




2. 按固定项目生成动态列的SQL脚本


SQL Code:

SELECT YEAR(OperationTime) AS YearItem,
SUM(CASE WHEN OperationType = 'OpenForm' THEN 1 ELSE 0 END) AS OpenForm,
SUM(CASE WHEN OperationType = 'Exception' THEN 1 ELSE 0 END) AS Exception,
SUM(CASE WHEN OperationType = 'CustomError' THEN 1 ELSE 0 END) AS CustomError
FROM sys_LogOperation
GROUP BY YEAR(OperationTime);

//来源:C/S框架网 | www.csframework.com | QQ:23404761




3. 界面设计参考


贴图图片-动态列2



4. 查询结果,动态创建列


贴图图片-动态列1




C# Code:


/// <summary>
/// 动态窗体GridColumn实例
/// </summary>
/// <param name="years"></param>
private void CreateDynamicColumns(string[] years)
{
  
//清除所有列
  
gridView1.Columns.Clear();
  
  
//添加默认列OperationType,Account,左侧固定
  
gridView1.Columns.Add(colAccount);
  gridView1.Columns.Add(colType);
  
  colAccount.VisibleIndex
= 0;
  colType.VisibleIndex
= 1;
  
  
int v = 2;
  
foreach (var year in years)
  {
    GridColumn col
= new GridColumn();
    col.Name
= "col" + year;
    col.Caption
= year;
    col.FieldName
= "Y" + year;//字段名
    
col.Width = 100;
    col.SummaryItem.SummaryType
= DevExpress.Data.SummaryItemType.Sum;//数字合计
    
col.VisibleIndex = v;
    col.Visible
= true;
    gridView1.Columns.Add(col);
    v++;
  }
  
}

//来源:C/S框架网 | www.csframework.com | QQ:23404761





5. 演示界面源码位置

CSFrameworkV5-ClientFoundation
\CSFrameworkV5.Report.frmDynamicReport


贴图图片-动态列3



6. DAL层源码


C# Code:


public DataSet GetDynamicReport(string[] years)
{
  
//组合SQL1
  
StringBuilder sb = new StringBuilder();
  sb.AppendLine($
"SELECT Account,OperationType, ");
  
for (int i = 0; i <= years.Length - 1; i++)
  {
    
string year = years[i].Trim();//必须去空格!
    
string item = $" SUM(CASE WHEN YEAR(OperationTime)={year} THEN 1 ELSE 0 END) AS Y{year} ";
    
if (i < years.Length - 1) item += ", ";
    sb.AppendLine(item);
  }
  
  sb.AppendLine(
"FROM sys_LogOperation ");
  sb.AppendLine(
"GROUP BY Account,OperationType; ");
  
string sql1 = sb.ToString();
  
  
//组合SQL2
  
string sql2 = "SELECT YEAR(OperationTime) AS YearItem, " +
  
" SUM(CASE WHEN OperationType = 'OpenForm' THEN 1 ELSE 0 END) AS OpenForm, " +
  
" SUM(CASE WHEN OperationType = 'Exception' THEN 1 ELSE 0 END) AS Exception, " +
  
" SUM(CASE WHEN OperationType = 'CustomError' THEN 1 ELSE 0 END) AS CustomError " +
  
"FROM sys_LogOperation " +
  
"GROUP BY YEAR(OperationTime); ";
  
  DataSet ds
= DatabaseProvider.SystemDatabase.GetDataSet(sql1 + sql2);
  
return ds;
}

//来源:C/S框架网 | www.csframework.com | QQ:23404761




C/S框架网|原创精神.创造价值.打造精品


扫一扫加作者微信
C/S框架网作者微信 C/S框架网|原创作品.质量保障.竭诚为您服务



版权声明:本文为开发框架文库发布内容,转载请附上原文出处连接
C/S框架网
发表评论

评论内容
昵称:
关联文章

C# Winform 使用SUM CASE WHEN实现动态交叉数据报表
动态交叉报表
C#多线程处理多个队数据(交叉线程访问及Invoke方法使用)
FastReport.NET制作动态栏位列报表C#实例
Winform软件快速开发平台 - 关于数据权限,表格权限控制两种解决方案
动态对象ExpandoObject实现动态函数调用
Winform查询数据对话窗体与选择资料窗体实现|C/S框架网
C# GridView头添加CheckBox控件实现全选功能
Winform程序扫码条码读取商品数据简单实现|C/S框架网
DevExpress XtraReport - 动态加载报表布局模板
Web端使用VUE调用WebApi接口实现用户登录及采用Token方式数据交互
C# Combox组件动态下拉数据
Winform Html Editor 使用kindeditor组件实现winform Html 编辑器
使用FastReport制做报表实战-报表模块开发框架
C#数组,多维数组,动态数组
C# Winform软件快速开发框架|软件开发平台 - 界面多语言实现原理
C# Winform动态设置控件的值及反射技术应用
企业级数据权限框架 - 集团组织架构数据权限开发框架(C/S+Winform+DevExpress)
水晶报表Pull模式的两种方法和Push模式的实现
C# Winform项目多语言实现(支持简/繁/英三种语言)