When i connect this to my crystal report 11, the error shown:

 

Failed to retrive data from the database.

Details: 21000:[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than one value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when subquery is used as an expression.

[Database vendor code:512]

 

This is the sql i do, i dono how to correct it.....Please advise...thanks

 

-------------------------------------------------------------------------------------------

SELECT     TOP 100 PERCENT *,
(SELECT     f.[Costing sum 1 (WCS16/KOCSU1)]
 FROM          (SELECT     [Item number], [Costing Type], MAX(CostingDate) AS Maxcostingdate
FROM          dbo.FT_KJCF_ProdCosting6
WHERE      ([Item number] = T1.[Item Number]) AND (CostingDate <= T1.[Accounting Date]) AND Division = T1.[Division]
GROUP BY [Item number], [Costing type]
HAVING      ([Costing type] = '3')) x INNER JOIN
dbo.FT_KJCF_ProdCosting6 f ON f.[Item number] = x.[Item number] AND f.CostingDate = x.Maxcostingdate AND
f.[Costing type] = x.[Costing Type]) AS Material_Cost,
(SELECT     f.[Value component B01] + f.[Value component B02] + f.[Value component B03]
FROM          (SELECT     [Item number], [Costing Type], MAX(CostingDate) AS Maxcostingdate
FROM          dbo.FT_KJCF_ProdCosting6
WHERE      ([Item number] = T1.[Item Number]) AND (CostingDate <= T1.[Accounting Date]) AND Division = T1.[Division]
GROUP BY [Item number], [Costing type]
HAVING      ([Costing type] = '3')) x INNER JOIN
dbo.FT_KJCF_ProdCosting6 f ON f.[Item number] = x.[Item number] AND f.CostingDate = x.Maxcostingdate AND 
f.[Costing type] = x.[Costing Type]) AS Labor_Cost,
 (SELECT     f.[Value component B04] + f.[Value component B05] + f.[Value component B06] + f.[Value component B07] + f.[Value component B08] + f.[Value component B09]
 + f.[Value component B10] + f.[Value component B11] + f.[Value component B12]
FROM   (SELECT     [Item number], [Costing Type], MAX(CostingDate) AS Maxcostingdate
FROM          dbo.FT_KJCF_ProdCosting6
WHERE      ([Item number] = T1.[Item Number]) AND (CostingDate <= T1.[Accounting Date]) AND Division = T1.[Division]
 GROUP BY [Item number], [Costing type]
 HAVING      ([Costing type] = '3')) x INNER JOIN
  dbo.FT_KJCF_ProdCosting6 f ON f.[Item number] = x.[Item number] AND f.CostingDate = x.Maxcostingdate AND
f.[Costing type] = x.[Costing Type]) AS OverHead_Cost
FROM         dbo.FT_KJCF_GP_Margin T1
ORDER BY Division, [Item number]

 

----------------------------------------------------------------------------------------

 

 

 

The report has a supressed header that also includes a subreport.  The subreport is a conditional suppression, but prints a static report that is 2 pages when it does run.  There was a reset page number after the report header (causing page 1 to print on physical page 1 and physical page 2).  I just added conditions to the reset to only reset when the subreport prints.

 

Thanks again for your time this afternoon.

A few things that come to mind here.

1. When I have a known error I always break my formulas into each piece and display them seperately to make sure I am getting what I expect from each piece first. So does the sum({@frmMargin}) display the correct amount and then does sum({@frmSales}) display the correct amount?

2. I see that one value has 3 decimal places. Could some rounding be causing you problems?

Thanks for the reply,

 

@frmsales is unit sales * unit price, and @frmmargin is @frmsales - total cost. There's nothing between the 'details' and this first grouping, and no duplicates.

 

You will have to get the '4'  seperate from the '8 and 9' and you will have to convert them all to text becasue the 8-9 will be in the same text display (same as the example but with dates).