环思产品论坛

标题: 通用查询图表配置示例 [打印本页]
作者: zhouyangping    时间: 2016-4-14 10:04
标题: 通用查询图表配置示例
本帖最后由 zhouyangping 于 2016-4-14 10:06 编辑

简单图表
  1. SELECT sMonth=CONVERT(NVARCHAR(7),a.dReceivedDate,120),
  2. nQty=SUM(c.nQty)
  3. FROM dbo.sdOrderHdr a
  4. JOIN dbo.sdOrderDtl b ON b.usdOrderHdrGUID = a.uGUID
  5. JOIN dbo.sdOrderLot c ON c.usdOrderDtlGUID = b.uGUID
  6. JOIN dbo.pbSales d ON d.uGUID = a.upbSalesGUID
  7. WHERE dReceivedDate BETWEEN '20130101' AND '20131231'
  8. GROUP BY CONVERT(NVARCHAR(7),a.dReceivedDate,120)
  9. ORDER BY 1
复制代码

显示效果


多系列图表示例一
  1. SELECT sSalesName=LEFT(d.sSalesName,1)+'**'
  2. ,sMonth=CONVERT(NVARCHAR(7),a.dReceivedDate,120),nQty=SUM(c.nQty)
  3. FROM dbo.sdOrderHdr a
  4. JOIN dbo.sdOrderDtl b ON b.usdOrderHdrGUID = a.uGUID
  5. JOIN dbo.sdOrderLot c ON c.usdOrderDtlGUID = b.uGUID
  6. JOIN dbo.pbSales d ON d.uGUID = a.upbSalesGUID
  7. WHERE dReceivedDate BETWEEN '20130101' AND '20131231'
  8. GROUP BY LEFT(d.sSalesName,1)+'**',CONVERT(NVARCHAR(7),a.dReceivedDate,120)
  9. ORDER BY 1,2,3 desc
复制代码



显示效果

多系列图表示例二


  1. SELECT sMonth=CONVERT(NVARCHAR(7),a.dReceivedDate,120),
  2. 业务员_陈=SUM(CASE WHEN d.sSalesName  LIKE '陈%' then c.nQty ELSE 0 END ) ,
  3. 业务员_方=SUM(CASE WHEN d.sSalesName  LIKE '方%' then c.nQty ELSE 0 END ) ,
  4. 业务员_高=SUM(CASE WHEN d.sSalesName  LIKE '高%' then c.nQty ELSE 0 END )
  5. FROM dbo.sdOrderHdr a
  6. JOIN dbo.sdOrderDtl b ON b.usdOrderHdrGUID = a.uGUID
  7. JOIN dbo.sdOrderLot c ON c.usdOrderDtlGUID = b.uGUID
  8. JOIN dbo.pbSales d ON d.uGUID = a.upbSalesGUID
  9. WHERE dReceivedDate BETWEEN '20130101' AND '20131231'
  10. GROUP BY CONVERT(NVARCHAR(7),a.dReceivedDate,120)
  11. ORDER BY 1
复制代码



显示效果
堆叠效果




作者: zhouyangping    时间: 2016-4-14 10:30
本帖最后由 zhouyangping 于 2016-4-14 10:41 编辑

图表混排示例

  1. SELECT 年月=CONVERT(NVARCHAR(7),a.dReceivedDate,120),
  2. 数量=SUM(c.nQty),
  3. 金额=SUM(c.nAmount)
  4. FROM dbo.sdOrderHdr a
  5. JOIN dbo.sdOrderDtl b ON b.usdOrderHdrGUID = a.uGUID
  6. JOIN dbo.sdOrderLot c ON c.usdOrderDtlGUID = b.uGUID
  7. JOIN dbo.pbSales d ON d.uGUID = a.upbSalesGUID
  8. WHERE dReceivedDate BETWEEN '20130101' AND '20131231'
  9. GROUP BY CONVERT(NVARCHAR(7),a.dReceivedDate,120)
  10. ORDER BY 1
复制代码

表格设置


柱状图设置


线状图设置


显示效果


作者: zhouyangping    时间: 2016-4-14 10:41
图表混排重叠效果

  1. SELECT 年月=CONVERT(NVARCHAR(7),a.dReceivedDate,120),
  2. 数量=SUM(c.nQty),
  3. 金额=SUM(c.nAmount)
  4. FROM dbo.sdOrderHdr a
  5. JOIN dbo.sdOrderDtl b ON b.usdOrderHdrGUID = a.uGUID
  6. JOIN dbo.sdOrderLot c ON c.usdOrderDtlGUID = b.uGUID
  7. JOIN dbo.pbSales d ON d.uGUID = a.upbSalesGUID
  8. WHERE dReceivedDate BETWEEN '20130101' AND '20131231'
  9. GROUP BY CONVERT(NVARCHAR(7),a.dReceivedDate,120)
  10. ORDER BY 1
复制代码



表格设置


柱线图设置


显示效果





欢迎光临 环思产品论坛 (http://bbs.huansi.cn/) Powered by Discuz! X3.2