多维报表,呈现复杂,因为列是动态的,列是可以分组的,行肯定也是动态的,也是可以分组的。我们程序员在写动态报表的是的时候是很辛苦的,需要考虑太多条件,特别是在多维的报表的情况下,记得有一次客户需要一个二维报表,把一个小姑娘累哭了!当时还苦于框架设计开发中,没有时间帮她,也没有管。现在框架完成了,该来整理思路了。
有没有办法,用简单的sql查询出一维数据,通过自定义方式(可以是最终用户方式,也可以程序员代码定义形式),动态显示多维数据呢?我想肯定有办法的。下面我写个列子。
一个项目,需要一个报表,需要多维方式统计各个分部的要货情况。如果用sql语句实现二维也可以,但太复杂了,还有需要大量时间。我想sql就简单写吧,直接查询出一维数据:
select PS.Name StockName,PD.Name DepName,PMT.Name MatTypeName,PM.Name PName,B.Qty,B.Amt
from PurorderDtl B
Left join PubStock PS on PS.ID=B.StockID
left Join Pubmaterial Pm on Pm.ID=B.MatID
Left join PubMatType PMT on PMT.ID=PM.MatTypeID
, Purorder H
Left join PubDep PD ON PD.ID=H.DepID
where B.ID=H.ID order by StockName,DepName
查询结果就是仓库名称、分部名称、货品类别名称、数量、金额。 这种Sql估计菜鸟级的程序员都能写吧!下面怎么实现动态程序呢?首先得根据查询结果集能用户设置如何多维吧。我设计图如下:
分部对应我们代码中的XFields(列头)和YFields(行分组),合计列对应SumFields.
定义好了以后,我们看报表效果:
如果这样定义样式(同一个数据集合):
那么显示报表效果如下图:
代码实现很简单,核心代码不到300行,大家可以参考:
private void ConvertToCrossTable(DataTable dtFrom,string[] XFields, string[] YFields,string[] SumFields )
{
StringBuilder sdbXorder = new StringBuilder();
foreach (string OneX in XFields)
{
if (sdbXorder.Length > 0)
sdbXorder.Append(",");
sdbXorder.Append(OneX);
}
DataTable dtXGroup = dtFrom.DefaultView.ToTable(true, XFields);
dtXGroup.DefaultView.Sort = sdbXorder.ToString();//排序
List<string> FieldXList = new List<string>();
StringBuilder sdb = new StringBuilder();
string OneXGrpValue = "";
foreach (DataRowView row in dtXGroup.DefaultView)
{
sdb.Clear();
foreach (string OneX in XFields)
{
if (sdb.Length > 0)
sdb.Append("|");
sdb.Append(row[OneX].ToString());
}
foreach (string SumField in SumFields)
{
OneXGrpValue = sdb.ToString() + "|" + SumField;
if (!FieldXList.Contains(OneXGrpValue))
FieldXList.Add(OneXGrpValue);
}
}
DataTable UserReport = new DataTable("Report");
StringBuilder sdbYorder = new StringBuilder();
StringBuilder sdbFieldRes = new StringBuilder();
foreach (string OneY in YFields)
{
if (sdbYorder.Length > 0)
sdbYorder.Append(",");
UserReport.Columns.Add(OneY, typeof(string));
sdbYorder.Append(OneY);
sdbFieldRes.Append("\n" + OneY + "=" + OneY);
}
StringBuilder sbdFooterSum = new StringBuilder();
foreach (string One in FieldXList)
{
UserReport.Columns.Add(One, typeof(decimal));
sdbFieldRes.Append("\n");
sdbFieldRes.Append(One);
sdbFieldRes.Append("=");
string[] OneYs = One.Split('|');
string OneY = OneYs[OneYs.Length - 1];
sdbFieldRes.Append(One);
sbdFooterSum.Append("\n");
sbdFooterSum.Append(One);
}
DataTable dtYGroup = dtFrom.DefaultView.ToTable(true, YFields);
dtYGroup.DefaultView.Sort = sdbYorder.ToString();//排序
foreach (DataRow dr in dtYGroup.Rows)
{
DataRow drN = UserReport.NewRow();
foreach (string OneY in YFields)
{
drN[OneY] = dr[OneY];
}
UserReport.Rows.Add(drN);
}
this.QryGrid.FieldDisplayRes = sdbFieldRes.ToString();
this.QryGrid.FieldSums = sbdFooterSum.ToString();
foreach (string OneY in YFields)
{
if (sdbXorder.Length > 0)
sdbXorder.Append(",");
sdbXorder.Append(OneY);
}
StringBuilder SdbGroupFields = new StringBuilder();
foreach (string One in XFields)
{
if (SdbGroupFields.Length > 0)
SdbGroupFields.Append(',');
SdbGroupFields.Append(One);
}
foreach (string One in YFields)
{
if (SdbGroupFields.Length > 0)
SdbGroupFields.Append(',');
SdbGroupFields.Append(One);
}
StringBuilder SdbSumFields = new StringBuilder();
foreach (string One in SumFields)
{
if (SdbSumFields.Length > 0)
SdbSumFields.Append(',');
SdbSumFields.Append(One);
}
DataTable dtGroupSum = GetGroupedBy(dtFrom, SdbSumFields.ToString(), SdbGroupFields.ToString(), "Sum");
StringBuilder sbdSort = new StringBuilder();
foreach (string One in YFields)
{
if (sbdSort.Length > 0)
sbdSort.Append(',');
sbdSort.Append(One);
}
dtGroupSum.DefaultView.Sort = sbdSort.ToString();
//填充
StringBuilder sbdfind = new StringBuilder();
StringBuilder sbdMyField = new StringBuilder();
foreach (DataRow row in dtGroupSum.Rows)
{
sbdfind.Clear();
foreach (string OneY in YFields)
{
if (sbdfind.Length > 0)
sbdfind.Append(" and ");
sbdfind.Append(OneY + "='" + row[OneY] + "'");
}
DataRow[] drs = UserReport.Select(sbdfind.ToString());
if (drs.Length == 0)
continue;
drs[0].BeginEdit();
sbdMyField.Clear();
foreach (string One in XFields)
{
if (sbdMyField.Length > 0)
sbdMyField.Append('|');
sbdMyField.Append(row[One]);
}
string OneMyField = sbdMyField.ToString();
foreach (string One in SumFields)
{
string OneField = OneMyField + "|" + One;
drs[0][OneField] = row[One];
}
drs[0].EndEdit();
}
}
上面代码供大家参考,也请大家指教。