Hi
I am getting above error. It is Subreport. 2 parameters are to be passed from Main report
Dockey , Object ID
DECLARE @TipoObjeto VARCHAR(10) DECLARE @DocEntry INT DECLARE @Comando VARCHAR(MAX) DECLARE @Tabela VARCHAR(3) SET @TipoObjeto = {?ObjectId@} SET @DocEntry = {?DocKey@} IF @TipoObjeto = '112' -- Draft BEGIN SET @Tabela = 'DRF' -- Draft/Rascunhos - ODRF END ELSE BEGIN SET @Tabela = 'INV' -- Pedido de compra - O[TABELA] END SET @COMANDO ='
SELECT CONVERT(VARCHAR,O[TABELA].DOCDATE,105) DOCDATE, CONVERT(VARCHAR,O[TABELA].DocDueDate,105) DocDueDate,O[TABELA].ObjType, (SELECT Case When O[TABELA].DOCCUR = ''INR'' Then SUM(LineTotal) Else Sum(totalFrgn) End FROM [TABELA]1 T WHERE T.DocEntry = O[TABELA].DocEntry) Taxable , (SELECT SUM(VatSum) FROM O[TABELA] WHERE T.DocEntry = O[TABELA].DocEntry) GSTTotal , (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-100'' AND T.TaxRate = 2.50 AND T.DocEntry = O[TABELA].DocEntry) CGST5, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-100'' AND T.TaxRate = 6.0 AND T.DocEntry = O[TABELA].DocEntry) CGST12, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-100'' AND T.TaxRate = 9.0 AND T.DocEntry = O[TABELA].DocEntry) CGST18, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-100'' AND T.TaxRate = 14.0 AND T.DocEntry = O[TABELA].DocEntry) CGST28, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-150'' AND T.TaxRate = 2.50 AND T.DocEntry = O[TABELA].DocEntry) UTGST5, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-150'' AND T.TaxRate = 6.0 AND T.DocEntry = O[TABELA].DocEntry) UTGST12, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-150'' AND T.TaxRate = 9.0 AND T.DocEntry = O[TABELA].DocEntry) UTGST18, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-150'' AND T.TaxRate = 14.0 AND T.DocEntry = O[TABELA].DocEntry) UTGST28, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-110'' AND T.TaxRate = 2.50 AND T.DocEntry = O[TABELA].DocEntry) SGST5, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-110'' AND T.TaxRate = 6.0 AND T.DocEntry = O[TABELA].DocEntry) SGST12, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-110'' AND T.TaxRate = 9.0 AND T.DocEntry = O[TABELA].DocEntry) SGST18, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-110'' AND T.TaxRate = 14.0 AND T.DocEntry = O[TABELA].DocEntry) SGST28, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-120'' AND T.TaxRate = 5.0 AND T.DocEntry = O[TABELA].DocEntry) IGST5, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-120'' AND T.TaxRate = 12.0 AND T.DocEntry = O[TABELA].DocEntry) IGST12, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-120'' AND T.TaxRate = 18.0 AND T.DocEntry = O[TABELA].DocEntry) IGST18, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-120'' AND T.TaxRate = 28.0 AND T.DocEntry = O[TABELA].DocEntry) IGST28, ISNULL((SELECT TOP 1 ISNULL(TAXRATE,0) FROM [TABELA]4 WHERE [TABELA]4.DOCENTRY = [TABELA]1.DOCENTRY AND [TABELA]4.STATYPE IN (''-100'') AND [TABELA]4.RELATETYPE = 1 and [TABELA]4.LINENUM=[TABELA]1.LINENUM) ,0) AS CGSTRATE, ISNULL((SELECT TOP 1 ISNULL(TAXRATE,0) FROM [TABELA]4 WHERE [TABELA]4.DOCENTRY = [TABELA]1.DOCENTRY AND [TABELA]4.STATYPE IN (''-110'') AND [TABELA]4.RELATETYPE = 1 and [TABELA]4 .LINENUM=[TABELA]1 .LINENUM) ,0) AS SGSTRATE, ISNULL((SELECT TOP 1 ISNULL(TAXRATE,0) FROM [TABELA]4 WHERE [TABELA]4.DOCENTRY = [TABELA]1.DOCENTRY AND [TABELA]4.STATYPE IN (''-120'') AND [TABELA]4.RELATETYPE = 1 and [TABELA]4.LINENUM=[TABELA]1.LINENUM) ,0) AS IGSTRATE, ISNULL((SELECT TOP 1 ISNULL(TAXRATE,0) FROM [TABELA]4 WHERE [TABELA]4.DOCENTRY = [TABELA]1.DOCENTRY AND [TABELA]4.STATYPE IN (''-150'') AND [TABELA]4.RELATETYPE = 1 and [TABELA]4.LINENUM=[TABELA]1.LINENUM) ,0) AS UTGSTRATE, (SELECT ISNULL(SUM([TABELA]4.TaxSum),0.0) FROM [TABELA]4 WHERE [TABELA]4.DocEntry = O[TABELA].DocEntry AND RelateType > 1 AND [TABELA]4.staType IN (''-100'')) As CGSTFreight, (SELECT ISNULL(SUM([TABELA]4.TaxSum),0.0) FROM [TABELA]4 WHERE [TABELA]4.DocEntry = O[TABELA].DocEntry AND RelateType > 1 AND [TABELA]4.staType IN (''-110'')) As SGSTFreight, (SELECT ISNULL(SUM([TABELA]4.TaxSum),0.0) FROM [TABELA]4 WHERE [TABELA]4.DocEntry = O[TABELA].DocEntry AND RelateType > 1 AND [TABELA]4.staType IN (''-120'')) As IGSTFreight, ISNULL((SELECT R4.TaxSum FROM [TABELA]4 R4 WHERE R4.staType IN (''-100'') AND RelateType = 1 AND R4.DocEntry = O[TABELA].DocEntry AND R4.LineNum = [TABELA]1.LineNum),0.0) AS CGSTAMNT, ISNULL((SELECT R4.TaxSum FROM [TABELA]4 R4 WHERE R4.staType IN (''-110'') AND RelateType = 1 AND R4.DocEntry = O[TABELA].DocEntry AND R4.LineNum = [TABELA]1.LineNum),0.0) AS SGSTAMNT, ISNULL((SELECT R4.TaxSum FROM [TABELA]4 R4 WHERE R4.staType IN (''-120'') AND RelateType = 1 AND R4.DocEntry = O[TABELA].DocEntry AND R4.LineNum = [TABELA]1.LineNum),0.0) AS IGSTAMNT, ISNULL((SELECT R4.TaxSum FROM [TABELA]4 R4 WHERE R4.staType IN (''-150'') AND RelateType = 1 AND R4.DocEntry = O[TABELA].DocEntry AND R4.LineNum = [TABELA]1.LineNum),0.0) AS UTGSTAMNT, ISNULL((SELECT R4.TaxSum FROM [TABELA]4 R4 WHERE R4.staType IN (''7'') AND RelateType = 1 AND R4.DocEntry = O[TABELA].DocEntry AND R4.LineNum = [TABELA]1.LineNum),0.0) AS TCS, CASE WHEN [TABELA]1.itemcode in (''SERVICES'',''NI20000'') THEN (select REPLACE(OCHP.CHAPTERID,''.'','''') FROM OCHP WHERE OCHP.ABSENTRY =[TABELA]1.hsnentry ) ELSE (SELECT REPLACE(OCHP.CHAPTERID,''.'','''') FROM OCHP WHERE OCHP.ABSENTRY = OITM.CHAPTERID ) END As ''TARIFF HEADING'', (select ServCode FROM OSAC WHERE OSAC.ABSENTRY =[TABELA]1.SacEntry) As ''TARIFF HEADING1''
FROM O[TABELA] INNER JOIN [TABELA]1 ON O[TABELA].DOCENTRY=[TABELA]1.DOCENTRY LEFT JOIN OCRD ON O[TABELA].CARDCODE= OCRD.CARDCODE LEFT JOIN [TABELA]26 on O[TABELA].DocEntry=[TABELA]26.DocEntry LEFT JOIN OETM ON OETM.ModeCode = [TABELA]26.TransMode LEFT JOIN [TABELA]12 T on O[TABELA].DocEntry=T.DocEntry LEFT JOIN DLN1 ON [TABELA]1.BaseEntry=DLN1.DOCENTRY AND [TABELA]1.BaseLine=DLN1.LineNum AND [TABELA]1.BaseType = ''15'' LEFT JOIN ODLN ON DLN1.DOCENTRY=ODLN.DOCENTRY LEFT JOIN RDR1 ON DLN1.BaseEntry=RDR1.DOCENTRY AND DLN1.BaseLine=RDR1.LineNum LEFT JOIN ORDR ON ORDR.DocEntry=RDR1.DOCENTRY --LEFT JOIN CRD1 ON O[TABELA].CARDCODE= CRD1.CARDCODE AND CRD1.ADRESTYPE=''S'' LEFT JOIN CRD7 on O[TABELA].CardCode=CRD7.CardCode AND CRD7.ADdrTYPE=''S'' and O[TABELA].shiptocode=crd7.address INNER JOIN OITM ON OITM.ITEMCODE = [TABELA]1.ITEMCODE LEFT JOIN OWHS ON OWHS.WhsCode =[TABELA]1.WhsCode LEFT JOIN OLCT ON OLCT.CODE =[TABELA]1.LOCCODE LEFT JOIN OCST ON OCST.CODE =T.STATEB AND OCST.COUNTRY = ''IN'' LEFT JOIN OCRY ON OCRY.CODE =T.COUNTRYB LEFT JOIN NNM1 ON O[TABELA].SERIES=NNM1.SERIES --LEFT JOIN CRD1 T3 ON O[TABELA].CARDCODE= T3.CARDCODE AND O[TABELA].PayToCode=T3.ADDRESS AND T3.ADRESTYPE=''B'' LEFT JOIN IBT1 B1 ON B1.ItemCode = DLN1.ItemCode AND B1.BaseType = ''15'' AND B1.BaseEntry = DLN1.DocEntry AND B1.BaseLinNum = DLN1.LineNum LEFT JOIN OBTN B0 ON B0.DistNumber = B1.BatchNum AND B0.ItemCode = B1.ItemCode LEFT JOIN OSHP ON O[TABELA].TrnspCode=OSHP.TrnspCode LEFT JOIN OCRN ON O[TABELA].DOCCUR = OCRN.CurrCode
where o[TABELA].docentry=@docentry' SELECT @COMANDO = REPLACE(@COMANDO, '[TABELA]', @Tabela) SELECT @COMANDO = REPLACE(@COMANDO, '@DocEntry', @DocEntry) EXEC(@COMANDO)
Thanks