I have a Crystal Report that I'm converting to SSRS currently. This report has 3 columns for MTD(Month to Date values) and 3 columns for YTD(Year to Date values). I'm able to get those 3 columns separate but for the data accuracy I need to combine it in a one SQL statement to output in one row. I was able to get it almost close to it using some coaleasce functions in SQL. The issue is it would not let me use group by commands and throws me a error. What should I edit in my SQL query to the way that shows in the below Crystal Report?
Error :
Msg 512, Level 16, State 1, Line 106
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Current Crystal Report that I'm converting into SSRS
EDIT : I just created a sample dbfiddle :
DB Fiddle - SQL Database Playground[
^]
In the Code 2 I have tried to use some with and unions but it is giving me some error as
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
What I have tried:
Code 1
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT coalesce((Select ((SUM( "IS_vwSalesbyProductLineComp"."sls_amt")-SUM( "IS_vwSalesbyProductLineComp"."cost_amt"))/SUM( "IS_vwSalesbyProductLineComp"."sls_amt"))*100 ),0) AS G1MTD,
"IS_vwSalesbyProductLineComp"."Mainslspsn_name",
"IS_vwSalesbyProductLineComp"."PCSummarized",
coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."qty_to_ship"-"IS_vwSalesbyProductLineComp"."qty_return_to_stk")FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
where Year = 2021
and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS Qty_Sold,
coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."sls_amt")FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
where Year = 2021
and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS YTD_Sales,
coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."cost_amt")FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
where Year = 2021
and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS YTD_COGS,
coalesce((Select ((SUM( "IS_vwSalesbyProductLineComp"."sls_amt")-SUM( "IS_vwSalesbyProductLineComp"."cost_amt"))/SUM( "IS_vwSalesbyProductLineComp"."sls_amt"))*100
FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
where Year = 2021
and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS G2YTD,
SUM("IS_vwSalesbyProductLineComp"."qty_to_ship"-"IS_vwSalesbyProductLineComp"."qty_return_to_stk")
AS Qty_Sold,
SUM("IS_vwSalesbyProductLineComp"."sls_amt")
AS MTD_Sales,
SUM("IS_vwSalesbyProductLineComp"."cost_amt")
AS MTD_COGS
FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
where Year = 2021
and Month = 12
Group by Mainslspsn_name,PCSummarized
order by PCSummarized
Code 2
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
WITH
T1 AS
(
SELECT (
SELECT((SUM(IS_vwSalesbyProductLineComp.sls_amt) - SUM(IS_vwSalesbyProductLineComp.cost_amt)) / SUM(IS_vwSalesbyProductLineComp.sls_amt)) * 100) AS G1MTD,
IS_vwSalesbyProductLineComp.Mainslspsn_name,
IS_vwSalesbyProductLineComp.PCSummarized,
(
SELECT SUM(IS_vwSalesbyProductLineComp.qty_to_ship - IS_vwSalesbyProductLineComp.qty_return_to_stk)
FROM "100".dbo.IS_vwSalesbyProductLineComp
WHERE Year = 2021
AND Month BETWEEN 1 AND 12
GROUP BY Mainslspsn_name,
PCSummarized) AS Qty_Sold,
(
SELECT SUM(IS_vwSalesbyProductLineComp.sls_amt)
FROM "100".dbo.IS_vwSalesbyProductLineComp
WHERE Year = 2021
AND Month BETWEEN 1 AND 12
GROUP BY Mainslspsn_name,
PCSummarized) AS YTD_Sales,
(
SELECT SUM(IS_vwSalesbyProductLineComp.cost_amt)
FROM "100".dbo.IS_vwSalesbyProductLineComp
WHERE Year = 2021
AND Month BETWEEN 1 AND 12
GROUP BY Mainslspsn_name,
PCSummarized) AS YTD_COGS,
(
SELECT((SUM(IS_vwSalesbyProductLineComp.sls_amt) - SUM(IS_vwSalesbyProductLineComp.cost_amt)) / SUM(IS_vwSalesbyProductLineComp.sls_amt)) * 100
FROM "100".dbo.IS_vwSalesbyProductLineComp
WHERE Year = 2021
AND Month BETWEEN 1 AND 12
GROUP BY Mainslspsn_name,
PCSummarized) AS G2YTD,
SUM(IS_vwSalesbyProductLineComp.qty_to_ship - IS_vwSalesbyProductLineComp.qty_return_to_stk) AS Qty_Sold2, --> renamed because ambigous
SUM(IS_vwSalesbyProductLineComp.sls_amt) AS MTD_Sales,
SUM(IS_vwSalesbyProductLineComp.cost_amt) AS MTD_COGS
FROM "100".dbo.IS_vwSalesbyProductLineComp
WHERE Year = 2021
AND Month = 12
GROUP BY Mainslspsn_name,
PCSummarized
),
T2 AS
(
SELECT 0 AS C1, '' AS C2, '' AS C3, 0 AS C4, 0 AS C5, 0 AS C6, 0 AS C7, 0 AS C8, 0 AS C9, 0 AS C10
),
T3 AS
(
SELECT * FROM T1
UNION
SELECT * FROM T2
)
SELECT SUM(G1MTD) AS G1MTD,
(SELECT [text()] = Mainslspsn_name
FROM "100".dbo.IS_vwSalesbyProductLineComp
FOR XML
PATH(''),
TYPE
).value
(
'./text()[1]',
'nvarchar(max)'
),
(SELECT [text()] = PCSummarized
FROM "100".dbo.IS_vwSalesbyProductLineComp
FOR XML
PATH(''),
TYPE
).value
(
'./text()[1]',
'nvarchar(max)'
),
SUM(Qty_Sold) AS Qty_Sold,
SUM(YTD_Sales) AS YTD_Sales,
SUM(YTD_COGS) AS YTD_COGS,
SUM(G2YTD) AS G2YTD,
SUM(Qty_Sold2) AS Qty_Sold2,
SUM(MTD_Sales) AS MTD_Sales,
SUM(MTD_COGS) AS MTD_COGS
FROM T3
GROUP BY Mainslspsn_name,
PCSummarized
ORDER BY PCSummarized;