菜单代码是：

MainTable = Tables("订单")

Dim Cmd As new SQLCommand

Cmd.C

Cmd.CommandText = "select distinct 分区,客户编码,客户,年,月份,目标 from 专卖店目标 where " & CustomFilter

Dim dt1 As DataTable = Cmd.ExecuteReader

Dim dtb As New DataTableBuilder("统计")

dtb.AddDef("分区", Gettype(String), 50)

dtb.AddDef("客户编码", Gettype(String), 150)

dtb.AddDef("客户", Gettype(String), 150)

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(0)

    dr("客户编码") = cus(1)

    dr("客户") = cus(2)

    For Each prd As String() In prds

        dr(prd(0) & "_" & prd(1) & "_目标") = dt1.Compute("Sum(目标)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\' And [年] = \'" & prd(0) & "\' and 月份 = \'"& prd(1) &"\'")

        dr(prd(0) & "_" & prd(1) & "_销售") = dt2.Compute("Sum(销售)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\' And [年] = \'" & prd(0) & "\' and 月份 = \'"& prd(1) &"\'")

    Next

    dr("目标总额") = dt1.Compute("Sum(目标)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\'")

    dr("销售总额") = dt2.Compute("Sum(销售)","分区 = \'" & cus(0) & "\' And 客户编码 = \'" & cus(1) & "\'")

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
此主题相关图片如下：360截图20140502130308092.jpg

如果订单表是空的话，点击菜单就会出现错误。。。。。。。。。。。。。。。。。。。。。