通用查询图表配置示例

zhouyangping

简单图表

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

图表混排示例

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

图表混排重叠效果

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
    

复制代码

表格设置


柱线图设置


显示效果