待解决问题:
1.如何在下面这份表中增加“合计”项;(图表1)
2.增加完合计项目后,并用这份临时表,生成类似的图表(按月);(图表2)
现在用原代码是
MainTable = Tables("订单")
Dim Cmd As new SQLCommand
Cmd.ConnectionName = "foxsale"
Cmd.CommandText = "select distinct 分区,年,月份,目标 from 目标 where " & CustomFilter
Dim dt1 As DataTable = Cmd.ExecuteReader
Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("分区", Gettype(String), 50)
Dim prds As List(of String()) = dt1.GetUniqueValues("","年","月份")
Dim exp As String
dtb.AddDef("目标总额", Gettype(Double))
dtb.AddDef("销售总额", Gettype(Double))
dtb.AddDef("达成率", Gettype(Double),"[销售总额]/[目标总额]")
For Each prd As String() In prds
dtb.AddDef(prd(0) & "_" & prd(1) & "_目标", Gettype(Double))
dtb.AddDef(prd(0) & "_" & prd(1) & "_销售", Gettype(Double))
exp = String.format("[{0}]/[{1}]",prd(0) & "_" & prd(1) & "_销售" ,prd(0) & "_" & prd(1) & "_目标" )
dtb.AddDef(prd(0) & "_" & prd(1) & "_达成率", Gettype(Double),exp)
Next
dtb.Build()
Cmd.CommandText = "select 分区,year(日期) as 年,month(日期) as 月份,sum(金额) as 销售 from 订单 where " & CustomFilter & " group by 分区,year(日期),month(日期)"
Dim dt2 As DataTable = Cmd.ExecuteReader
For Each cus As String In dt1.GetUniqueValues("","分区")
Dim dr As DataRow = DataTables("统计").AddNew()
dr("分区") = cus
For Each prd As String() In prds
dr(prd(0) & "_" & prd(1) & "_目标") = dt1.Compute("Sum(目标)","分区 = '" & cus & "' And [年] = '" & prd(0) & "' and 月份 = '"& prd(1) &"'")
dr(prd(0) & "_" & prd(1) & "_销售") = dt2.Compute("Sum(销售)","分区 = '" & cus & "' And [年] = '" & prd(0) & "' and 月份 = '"& prd(1) &"'")
Next
dr("目标总额") = dt1.Compute("Sum(目标)","分区 = '" & cus & "'")
dr("销售总额") = dt2.Compute("Sum(销售)","分区 = '" & cus & "'")
Next
For Each prd As String() In prds
DataTables("统计").DataCols(prd(0) & "_" & prd(1) & "_达成率").SetFormat("0.00%")
Next
DataTables("统计").DataCols("达成率").SetFormat("0.00%")
MainTable = Tables("统计")
Tables("统计").AutoSizeCols
![dvubb 图片点击可在新窗口打开查看](skins/default/filetype/jpg.gif)
此主题相关图片如下:qq图片20140414223648.jpg
![dvubb 图片点击可在新窗口打开查看](UploadFile/2014-4/201441422382823617.jpg)
![dvubb 图片点击可在新窗口打开查看](skins/default/filetype/jpg.gif)
此主题相关图片如下:qq图片20140414223655.jpg