-- 作者：bm
-- 发布时间：2014/12/15 20:39:00
-- 需要年和月份统计
Dim F As String
With e.Form.Controls("开始日期2")
If .Value IsNot Nothing Then
If F >"" Then
F = F & " And "
End If
F = F & "作业日期 >= #" & .Value & "#"
End If
End With
With e.Form.Controls("结束日期2")
If .Value IsNot Nothing Then
If F >"" Then
F = F & " And "
End If
F = F & "作业日期 <= #" & .Value & "#"
End If
End With
Dim dt As DataTable
Dim jb As new SQLJoinTableBuilder("查询表1","业务大表")
jb.C
jb.Filter = F
jb.AddCols("作业日期", "客户")
jb.AddExp("利润分析","iif(利润分析 Is null,0,利润分析)")
dt = jb.Build(True)
Dim cb As new CrossTableBuilder("客户欠款",jb.BuildSQL)
cb.C
cb.VGroups.AddDef("客户")
cb.HGroups.AddDef("作业日期","月")
cb.Totals.AddDef("利润分析")
cb.Build()
Dim Chart As WinForm.Chart = e.Form.Controls("Chart4")
Dim Series As WinForm.ChartSeries
Chart.DataSource = "客户欠款"
Chart.ChartType = ChartTypeEnum.Bar
Chart.SeriesList.Clear()
For Each i As Col In Tables("客户欠款").Cols
If i.Name <> "月" Then
Series = Chart.SeriesList.Add()
Series.Text = i.Caption
Series.X.DataField = "月"
Series.Y.DataField = i.Name
Series.DataLabelText = "{#YVAL}"
Series.DataLabelBackColor = Color.DimGray
Series.DataLabelForeColor = Color.white
Series.DataLabelCompass = LabelCompassEnum.West
Series.Y.DataType = Gettype(Double)
End If
Next
Chart.Axisx.Major = 1
Chart.Inverted = True
Chart.Stacked = True
Chart.LegendVisible = True
Chart.LegendCompass = CompassEnum.East
Dim g As String = cint(dt.Compute("Sum(利润分析)"))
上面的代码只能统计到月，能否按年月来统计
|