金蝶系统中提供的报表不能满足客户的需要时, 可以通过
查询分析工具
进行自定义报表的二次开发查询分析工具
相比其他第三方工具集成度更高, 权限管理设置也非常方便, 在需求比较简单的情况下, 极力推荐
需求展示
客户需要按照年度/月份统计店铺的销售业绩
店铺 | 月份 | 2015 | 2016 | 2017 | 2018 |
---|---|---|---|---|---|
a店 | 1月 | 100 | 200 | 150 | 130 |
a店 | 2月 | 200 | 210 | 300 | 400 |
a店 | ... | ... | ... | ... | ... |
a店 | 12月 | 112 | 200 | 300 | 100 |
直接查询分析代码
直接写到金蝶系统里面
SET nocount ON CREATE TABLE #SellAmount (FNumber varchar(55),
FName VARCHAR ( 55 ),
FYear INT,
FMonth VARCHAR ( 55 ),
FAmount DECIMAL ( 28, 10 )
) INSERT INTO #SellAmount
SELECT
*
FROM
(
SELECT
t3.FNumber,
t3.FName,
YEAR ( t1.FDate ) AS FYear,
( SELECT RIGHT ( 100+ cast( MONTH ( t1.FDate ) AS VARCHAR ( 2 ) ), 2 ) ) AS FMonth,
sum( t2.FConsignAmount ) AS FAmount
FROM
ICStockBill T1
LEFT JOIN icstockbillentry T2 ON t1.finterid = t2.finterid
LEFT JOIN t_Department T3 ON t1.FDeptID = t3.FItemID
WHERE
t1.FTranType = 21
AND t1.FDeptID IN ( SELECT fitemid FROM t_Department WHERE FParentID = 11114 )
AND t3.FName LIKE '%@DeptName@%'
GROUP BY
t3.fnumber,
t3.FName,
YEAR ( t1.FDate ),
MONTH ( t1.FDate ) WITH ROLLUP
) x1
WHERE
x1.FMonth IS NOT NULL UNION
SELECT
x2.FNumber,
x2.FName,
x2.FYear,
'合计:',
x2.FAmount
FROM
(
SELECT
t3.FNumber,
t3.FName,
YEAR ( t1.FDate ) AS FYear,
( SELECT RIGHT ( 100+ cast( MONTH ( t1.FDate ) AS VARCHAR ( 2 ) ), 2 ) ) AS FMonth,
sum( t2.FConsignAmount ) AS FAmount
FROM
ICStockBill T1
LEFT JOIN icstockbillentry T2 ON t1.finterid = t2.finterid
LEFT JOIN t_Department T3 ON t1.FDeptID = t3.FItemID
WHERE
t1.FTranType = 21
AND t1.FDeptID IN ( SELECT fitemid FROM t_Department WHERE FParentID = 11114 )
AND t3.FName LIKE '%@DeptName@%'
GROUP BY
t3.fnumber,
t3.FName,
YEAR ( t1.FDate ),
MONTH ( t1.FDate ) WITH ROLLUP
) x2
WHERE
x2.FYear IS NOT NULL
AND x2.FMonth IS NULL SELECT
FNumber AS '代码',
FName AS '部门',
FMonth AS '月份',
sum( CASE WHEN FYear = 2015 THEN FAmount ELSE NULL END ) AS '2015年',
sum( CASE WHEN FYear = 2016 THEN FAmount ELSE NULL END ) AS '2016年',
sum( CASE WHEN FYear = 2017 THEN FAmount ELSE NULL END ) AS '2017年',
SUM( CASE WHEN FYear = 2018 THEN FAmount ELSE NULL END ) AS '2018年',
SUM( CASE WHEN FYear = 2019 THEN FAmount ELSE NULL END ) AS '2019年',
SUM( CASE WHEN FYear = 2020 THEN FAmount ELSE NULL END ) AS '2020年'
FROM #SellAmount
GROUP BY
FNumber,
FName,
FMonth
ORDER BY
FNumber ASC DROP TABLE #SellAmount
SQL查询代码
写道sql查询分析器里面, 便于调试
select * from ICTransactionType --单据类型表
select * from t_Department where FParentID = 11114 --部门表
Select Right(100+Month(GetDate()),2)
select right(100+cast(month(GETDATE()) as varchar(2)),2) --月份前+0
/*查询
SELECT
t1.fbillno,
t3.FName,
fdate,
YEAR ( t1.FDate ),
( SELECT RIGHT ( 100+ cast( MONTH ( t1.FDate ) AS VARCHAR ( 2 ) ), 2 ) ),
t2.fqty,
t2.FPrice,
T2.FAmount,
t2.FConsignPrice,
T2.FConsignAmount,*
FROM
ICStockBill T1
LEFT JOIN icstockbillentry T2 ON t1.finterid = t2.finterid
LEFT JOIN t_Department T3 ON t1.FDeptID = t3.FItemID
WHERE
t1.FTranType = 21
AND t1.FDeptID IN ( SELECT fitemid FROM t_Department WHERE FParentID = 11114 )
AND t3.FName LIKE '%网络-阿里澳沃%'
*/
/*-------↓实现过程↓-------*/
CREATE TABLE #SellAmount (FNumber varchar(55),
FName VARCHAR ( 55 ),
FYear INT,
FMonth VARCHAR ( 55 ),
FAmount DECIMAL ( 28, 10 )
) INSERT INTO #SellAmount
SELECT
*
FROM
(
SELECT
t3.FNumber,
t3.FName,
YEAR ( t1.FDate ) AS FYear,
( SELECT RIGHT ( 100+ cast( MONTH ( t1.FDate ) AS VARCHAR ( 2 ) ), 2 ) ) AS FMonth,
sum( t2.FConsignAmount ) AS FAmount
FROM
ICStockBill T1
LEFT JOIN icstockbillentry T2 ON t1.finterid = t2.finterid
LEFT JOIN t_Department T3 ON t1.FDeptID = t3.FItemID
WHERE
t1.FTranType = 21
AND t1.FDeptID IN ( SELECT fitemid FROM t_Department WHERE FParentID = 11114 )
AND t3.FName LIKE '%%'
GROUP BY
t3.fnumber,
t3.FName,
YEAR ( t1.FDate ),
MONTH ( t1.FDate ) WITH ROLLUP
) x1
WHERE
x1.FMonth IS NOT NULL UNION
SELECT
x2.FNumber,
x2.FName,
x2.FYear,
'合计:',
x2.FAmount
FROM
(
SELECT
t3.FNumber,
t3.FName,
YEAR ( t1.FDate ) AS FYear,
( SELECT RIGHT ( 100+ cast( MONTH ( t1.FDate ) AS VARCHAR ( 2 ) ), 2 ) ) AS FMonth,
sum( t2.FConsignAmount ) AS FAmount
FROM
ICStockBill T1
LEFT JOIN icstockbillentry T2 ON t1.finterid = t2.finterid
LEFT JOIN t_Department T3 ON t1.FDeptID = t3.FItemID
WHERE
t1.FTranType = 21
AND t1.FDeptID IN ( SELECT fitemid FROM t_Department WHERE FParentID = 11114 )
AND t3.FName LIKE '%%'
GROUP BY
t3.fnumber,
t3.FName,
YEAR ( t1.FDate ),
MONTH ( t1.FDate ) WITH ROLLUP
) x2
WHERE
x2.FYear IS NOT NULL
AND x2.FMonth IS NULL SELECT
FNumber AS '代码',
FName AS '部门',
FMonth AS '月份',
sum( CASE WHEN FYear = 2015 THEN FAmount ELSE NULL END ) AS '2015年',
sum( CASE WHEN FYear = 2016 THEN FAmount ELSE NULL END ) AS '2016年',
sum( CASE WHEN FYear = 2017 THEN FAmount ELSE NULL END ) AS '2017年',
SUM( CASE WHEN FYear = 2018 THEN FAmount ELSE NULL END ) AS '2018年',
SUM( CASE WHEN FYear = 2019 THEN FAmount ELSE NULL END ) AS '2019年',
SUM( CASE WHEN FYear = 2020 THEN FAmount ELSE NULL END ) AS '2020年'
FROM #SellAmount
GROUP BY
FNumber,
FName,
FMonth
ORDER BY
FNumber ASC DROP TABLE #SellAmount