September 4, 2023 at 12:36 pm
This is some what weired issue. I am not able resolve
to the existing stored procedure i have added new table UM_Risk_ICRA_FI_Rating_ICRA_ST_LT_Mapping_CP_CD_Table FF
and added new field in the stored procedure
CASE WHEN FF.[Final LT Rating_Rating] IS NOT NULL THEN FF.[Final LT Rating_Rating]
WHEN FF.[Final LT Rating_Rating] IS NULL AND B.AssetTypeName IN ('COMMERCIAL PAPER','CERTIFICATE OF DEPOSITS') THEN 'NA'
ELSE COALESCE(UPPER(rtrim(ltrim(replace(replace(h.Rating,'(CE)',''),'(SO)','')))),b.low_rating,'Unrated') END as RATING
when i refresh the fields in dataset properties it throws Define Query Parameters window. Not sure what could be issue below is the complete stored procedure. please let me know what could be issue
alter PROCEDURE [dbo].[RPT_IssuerRatingNew_Ra]
/* Issuer Rating */
--Declare @NAVDATE as date
--set @NAVDATe = '2018-07-07'
@NAVDATE DATE,
@MATDATE DATE,
@Scheme VARCHAR (MAX),
@TYPE VARCHAR(MAX) ,
@SectorCode Varchar(MAX)
AS
;WITH MAIN_CTE
AS (SELECT D.NAME AS ManagementGroupName,
C.ISSUER_NAME AS Issuer_Name,
/* CASE WHEN h.Rating = 'AA(SO)' THEN 'AA'
WHEN h.Rating = 'AA-(SO)' THEN 'AA-'
WHEN h.Rating = 'A(SO)' THEN 'A'
WHEN h.Rating = 'A+(SO)' THEN 'A+'
WHEN h.Rating = 'AA+(SO)' THEN 'AA+'
WHEN h.Rating = 'AAA(SO)' THEN 'AAA'
WHEN h.Rating = 'A1+(SO)' THEN 'A1+'
WHEN h.Rating = 'A3(SO)' THEN 'A3'
WHEN h.Rating = 'BBB(SO)' THEN 'BBB'
WHEN h.Rating IS NULL THEN 'UnRated'
ELSE h.Rating END AS Rating,*/
/*cast(CASE
WHEN h.Rating IS NULL THEN 'UnRated'
ELSE replace(replace(rtrim(ltrim(h.Rating)),'(CE)',''),'(SO)','')
END as nvarchar(8)) AS Rating,
*/ -- Commented on Sep 01 2023 due to incorrect unrated ratings are appearing
CASE WHEN FF.[Final LT Rating_Rating] IS NOT NULL THEN FF.[Final LT Rating_Rating]
WHEN FF.[Final LT Rating_Rating] IS NULL AND B.AssetTypeName IN ('COMMERCIAL PAPER','CERTIFICATE OF DEPOSITS') THEN 'NA'
ELSE COALESCE(UPPER(rtrim(ltrim(replace(replace(h.Rating,'(CE)',''),'(SO)','')))),b.low_rating,'Unrated') END as RATING,
E.SCHEME_CODE AS Scheme_Code,
I.SECURITY,
a.ASSET_TYPE,
E.SCHEME_NAME AS Scheme_Name,
C.ISSTYPECODE AS ISSUERTYPE,
C.IssTypeName,
C.SectorName AS SectorName,
B.AssetTypeName AS SOVRatings,
Round(abs(a.AMOUNT) / 10000000,7) Marketvalue,
-- ROUND((abs(a.AMOUNT) / SUM(ABS(a.AMOUNT)) OVER ()), 5) * 100 AS Exposure,
-- I.VALUE_DATE,
i.FA_YTM_MAT_DATE CLPT_Date,
b.MAT_DATE,
--CASE WHEN ISNULL(I.VALUE_DATE,B.Mat_Date)>= @NAVDATE
-- AND ISNULL(I.VALUE_DATE,B.Mat_Date)<= DATEADD(MM, 3, @NAVDATE) THEN Round(abs(a.AMOUNT) / 10000000, 2) END AS Exposure_lessthan3Months,
--CASE WHEN ISNULL(I.VALUE_DATE,B.Mat_Date) > DATEADD(MM, 3, @NAVDATE)
-- AND ISNULL(I.VALUE_DATE,B.Mat_Date) <= DATEADD(MM, 12, @NAVDATE) THEN Round(abs(a.AMOUNT) / 10000000, 2) END AS Exposure_3to12Months,
--CASE WHEN ISNULL(I.VALUE_DATE,B.Mat_Date) > DATEADD(MM, 12, @NAVDATE) THEN Round(abs(a.AMOUNT) / 10000000, 2) END AS Exposure_Morethan12Months,
ISNULL(i.FA_YTM_MAT_DATE,b.MAT_DATE) AS Maturity,
ABS(datediff(D,@NAVDATE,ISNULL(i.FA_YTM_MAT_DATE,b.MAT_DATE))) AS Mat_NavDiff,
h.YIELD
FROM stg_mf_schnavbd AS A
LEFT OUTER JOIN stg_mf_Security AS B ON A.security = B.SecCode
INNER JOIN stg_mf_YTM_CallPutDate I ON a.NAV_DATE=I.VALUE_DATE and A.SCHEME=I.SCHEME AND B.SecCode = I.[SECURITY]
LEFT OUTER JOIN stg_mf_Issuer AS C ON B.issuer = C.ISSUER_ID
LEFT OUTER JOIN stg_mf_Mgmt AS D ON C.MGMTGRPCODE = D.MGMT_GROUP AND D.RECTYPE = 'L'
LEFT OUTER JOIN stg_mf_SchemeMaster AS E ON A.SCHEME = E.SCHEME_CODE
LEFT OUTER JOIN stg_mf_Holding AS h ON A.NAV_DATE = h.NAV_DATE
AND A.SCHEME = h.SCHEME
AND a.SECURITY = h.SECURITY
AND a.CUSTODIAN = h.CUSTODIAN
AND a.ASSET_TYPE = h.ASSET_TYPE
AND a.AMC_CODE = h.AMC_CODE
LEFT JOIN UM_Risk_ICRA_FI_Rating_ICRA_ST_LT_Mapping_CP_CD_Table FF ON FF.FileDate = A.NAV_DATE AND FF.ISIN = B.ISIN_CODE
WHERE B.AssetGrpCode IN ('DB','MM')
AND B.RECTYPE = 'L'
AND C.Rectype = 'L'
AND A.NAV_DATE = @NAVDATE
AND i.FA_YTM_MAT_DATE <= @MATDATE
AND A.SCHEME in (select * from string_split(@Scheme,','))
AND C.ISSTYPECODE in (select * from string_split(@TYPE,','))
AND C.SectorCode in (select * from string_split(@SectorCode,','))
)
--select * from MAIN_CTE
SELECT
a.Rating AS WorstRating,
a.Issuer_Name,
a.ManagementGroupName,
a.Scheme_Code,
a.Scheme_Name,
a.IssTypeName,
a.SectorName,
a.SOVRatings,
a.Marketvalue,
--a.Exposure,
a.Maturity,
a.Mat_NavDiff,
-- a.VALUE_DATE,
-- a.MAT_DATE1,
--abs(a.Marketvalue)/SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating,ISSUER_NAME,ManagementGroupName,SCHEME_CODE,ISSUERTYPE,SectorName,ASSET_TYPE ORDER BY Rating,ISSUER_NAME,ManagementGroupName,SCHEME_CODE,ISSUERTYPE,SectorName,ASSET_TYPE) *100 Exposure,
Round((abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER ()),7)*100 Exposure,
CASE WHEN ISNULL(a.CLPT_Date,a.MAT_DATE)>= @NAVDATE
AND ISNULL(a.CLPT_Date,a.MAT_DATE)<= DATEADD(MM, 3, @NAVDATE) THEN a.Marketvalue END AS Exposure_lessthan3Months,
CASE WHEN ISNULL(a.CLPT_Date,a.MAT_DATE)> DATEADD(MM, 3, @NAVDATE)
AND ISNULL(a.CLPT_Date,a.MAT_DATE)<= DATEADD(MM, 12, @NAVDATE) THEN a.Marketvalue END AS Exposure_3to12Months,
CASE WHEN ISNULL(a.CLPT_Date,a.MAT_DATE)> DATEADD(MM, 12, @NAVDATE) THEN a.Marketvalue END AS Exposure_Morethan12Months,
CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME, ManagementGroupName,SCHEME_CODE ORDER BY Rating, ISSUER_NAME, ManagementGroupName,SCHEME_CODE) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME, ManagementGroupName,SCHEME_CODE ORDER BY Rating, ISSUER_NAME, ManagementGroupName,SCHEME_CODE) * a.Mat_NavDiff) / 365 ELSE 0 END AS SCHEME_WeightedAvgMaturity,
CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME ORDER BY Rating, ISSUER_NAME) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME ORDER BY Rating, ISSUER_NAME) * a.Mat_NavDiff) / 365 ELSE 0 END AS ISSUER_WeightedAvgMaturity,
CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating ORDER BY Rating) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating ORDER BY Rating) * a.Mat_NavDiff) / 365 ELSE 0 END AS Rating_WeightedAvgMaturity,
CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME, ManagementGroupName, SCHEME_CODE ORDER BY Rating, ISSUER_NAME, ManagementGroupName, SCHEME_CODE) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME, ManagementGroupName,SCHEME_CODE ORDER BY Rating, ISSUER_NAME, ManagementGroupName,SCHEME_CODE) * a.YIELD) ELSE 0 END AS SCHEME_Yield,
CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME ORDER BY Rating, ISSUER_NAME) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME ORDER BY Rating, ISSUER_NAME) * YIELD) ELSE 0 END AS Issuer_Yield,
CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating ORDER BY Rating) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating ORDER BY Rating) * YIELD) ELSE 0 END AS Rating_Yield
FROM MAIN_CTE AS a
September 4, 2023 at 12:47 pm
Just enter some dummy (but valid) values for the parameters and it should work. SSRS does this when it believes that things have become too complicated for it to guess (at least, that's the reason I tell myself when it happens!)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply