<em class='bbc'><span class='bbc_underline'><strong class='bbc'>how to call stored procedures in birt that are already made in the database for eg...</strong></span></em><br />
<br />
CREATE PROCEDURE [dbo].[AssetAMCExpiredReport_SP](
@Date_Param DATETIME = NULL)<br />
<br />
<br />
-- Procedure Name : AssetAMCExpiredReport_SP<br />
-- Developed By : Parveen Verma<br />
-- Developed on : <07-Mar-2011><br />
-- Developed for : Asset_AMC_Expired_Report.rpt<br />
<br />
<br />
AS<br />
BEGIN<br />
<br />
-- Stat: Set Current Date as default in case Null is passed to
@Date_Param<br />
<br />
IF
@Date_Param IS NULL<br />
SET
@Date_Param = GETDATE()<br />
<br />
-- End: Set Current Date as default in case Null is passed to
@Date_Param<br />
<br />
<br />
<br />
-- Start: Main SQL Query<br />
<br />
SELECT ISNULL(AST.zADR_Number, ISNULL(AST.zPO_Number, AST.zInvoiceNo1)) AS ADR ,<br />
BR.Name AS [Make_Name],<br />
MO.Name AS [Model],<br />
PO.AssetTag,<br />
AST.SerialNo,<br />
LO.Name AS [Location_Name],<br />
LO.FullName as [Full_Name], <br />
LO.Field1 AS [Circel],<br />
AST.dAcquisition AS [Purchase_Date],<br />
AST.dWarrEnd,<br />
AST.dAMCStart,<br />
AST.dAMCEnd,<br />
(CASE charindex('-', AST.AssetTag,1)<br />
WHEN 3 THEN SUBSTRING( AST.assettag, 13, 2)<br />
WHEN 4 THEN SUBSTRING( AST.assettag, 14, 2)<br />
ELSE NULL<br />
END) AS Asset_Type<br />
INTO #tempTable<br />
FROM amPORTFOLIO PO<br />
INNER JOIN amASSET AST ON PO.LAstId = AST.LAstId<br />
LEFT JOIN amLOCATION LO ON LO.LLocaID = PO.LLocaID<br />
LEFT JOIN amMODEL MO ON MO.LModelId = PO.LModelId<br />
LEFT JOIN amBRAND BR ON BR.LBrandID = MO.LBrandID<br />
WHERE (CASE WHEN AST.dWarrEnd >= ISNULL(AST.dAMCEnd, AST.dWarrEnd) THEN AST.dWarrEnd<br />
WHEN AST.dAMCEnd >= ISNULL(AST.dWarrEnd, AST.dAMCEnd) THEN AST.dAMCEnd<br />
END) <=
@Date_Param<br />
/*(CASE WHEN AST.dWarrEnd >= ISNULL(AST.dAMCEnd,
@Date_Param) THEN AST.dWarrEnd<br />
WHEN AST.dAMCEnd > ISNULL(AST.dWarrEnd,
@Date_Param) THEN AST.dAMCEnd<br />
END) <=
@Date_Param */<br />
AND CASE charindex('-', AST.AssetTag,1)<br />
WHEN 3 THEN SUBSTRING( AST.assettag, 13, 2) <br />
WHEN 4 THEN SUBSTRING( AST.assettag, 14, 2) <br />
ELSE NULL<br />
END IN ('SR','DT','LT','RT','SW','PR','SC','ST', 'HU', 'BC') <br />
<br />
-- Start: Main SQL Query<br />
<br />
<br />
<br />
--Start: Query to add the Row Number<br />
<br />
SELECT ROW_Number() <br />
OVER(ORDER BY (CASE WHEN dWarrEnd >= ISNULL(dAMCEnd,
@Date_Param) THEN dWarrEnd<br />
WHEN dAMCEnd > ISNULL(dWarrEnd,
@Date_Param) THEN dAMCEnd<br />
END) ASC) AS 'Row_Number',<br />
T.*<br />
FROM #tempTable T<br />
ORDER BY (CASE WHEN dWarrEnd >= ISNULL(dAMCEnd,
@Date_Param) THEN dWarrEnd<br />
WHEN dAMCEnd > ISNULL(dWarrEnd,
@Date_Param) THEN dAMCEnd<br />
END)<br />
<br />
--End: Query to add the Row Number<br />
<br />
END
