July 10, 2014 at 7:51 am
When I preview my report, I get this message error."The report parameter "Demographics1" has a defaultValue or a Valid value that depends on the report parameter "Demographics1".Forward dependencies are not valid..How to I solve this issue.
Below in my query
create PROCEDURE[dbo].[proc_LoansProcessed_ToDate2014] --EXEC proc_LoansProcessed_ToDate '06/25/2012'
@ReportDate Datetime,
@LoanAmount varchar(max) = null,
@LoanPurpose varchar(max) = null,
@PropertyUsage varchar(max) = null,
@Demographics1 varchar(max)= null
AS
BEGIN
DECLARE --@ReportDate datetime = '6/27/2013',
@DayOfWeek varchar(100),
@BeginOfWeek Datetime,
@BeginOfMonth Datetime,
@BeginOfYear Datetime
SET @DayOfWeek = (SELECT DATENAME(dw,@ReportDate))
SET @BeginOfWeek =(
SELECT CASEWHEN @DayOfWeek = 'Sunday' THEN DATEADD(dd,0,@ReportDate)
WHEN @DayOfWeek = 'Monday' THEN DATEADD(dd,-1,@ReportDate)
WHEN @DayOfWeek = 'Tuesday' THEN DATEADD(dd,-2,@ReportDate)
WHEN @DayOfWeek = 'Wednesday'THEN DATEADD(dd,-3,@ReportDate)
WHEN @DayOfWeek = 'Thursday'THEN DATEADD(dd,-4,@ReportDate)
WHEN @DayOfWeek = 'Friday' THEN DATEADD(dd,-5,@ReportDate)
WHEN @DayOfWeek = 'Saturday'THEN DATEADD(dd,-6,@ReportDate) END BeginOfWeek
)
SET @BeginOfMonth = (
SELECT CONVERT(DATETIME,CONVERT(VARCHAR(100),DATEPART(MM,@ReportDate)) + '/1/' + CONVERT(VARCHAR(100),DATEPART(YY,@ReportDate)))
)
SET @BeginOfYear = (
SELECT '1/1/' + CONVERT(VARCHAR(100),DATEPART(YY,@ReportDate))
)
SELECT b.*,c.[Property Usage],d.[Marital Status],d.Sex,d.Race,DATEDIFF(YY,d.[Date of Birth],@ReportDate) Age,d.[Borrower FirstName] + ' ' + d.[Borrower LastName] BorrowerName,a.TotalMonthlyIncome,
DATENAME(MONTH,LoanDate) + ' ' + CONVERT(VARCHAR(10),DATEPART(YY,LoanDate)) FormatedReportMonth,a.LoanAmount as Loan_Amount,
CASE WHEN b.LoanDate >= @BeginOfWeekAND b.LoanDate <= @ReportDate THEN 'WeekToDate'
WHEN b.LoanDate >= @BeginOfMonth AND b.LoanDate <= @ReportDate THEN 'MonthToDate'
WHEN b.LoanDate >= @BeginOfYear AND b.LoanDate <= @ReportDate THEN 'YearToDate' ELSE NULL END ToDate,
CASE WHEN b.LoanDate >= @BeginOfWeekAND b.LoanDate <= @ReportDate THEN 1
WHEN b.LoanDate >= @BeginOfMonth AND b.LoanDate <= @ReportDate THEN 2
WHEN b.LoanDate >= @BeginOfYear AND b.LoanDate <= @ReportDate THEN 3 ELSE NULL END ToDateOrder,
CASE WHEN a.[LoanAmount] <= 100000 THEN 'Less Than $100k'
WHEN a.LoanAmount between 100000 AND 200000 THEN '$100k to $200k'
WHEN a.LoanAmount > 200000 THEN 'More Than $200k' END LoanAmountGroup,
CASE WHEN a.LoanAmount <= 100000 THEN 1
WHEN a.LoanAmount between 100000 AND 200000 THEN 2
WHEN a.LoanAmount > 200000 THEN 3 END LoanAmountGroupOrder,
CASE WHEN DATEDIFF(YY,d.[Date of Birth],@ReportDate) < 25 THEN '<=25'
WHEN DATEDIFF(YY,d.[Date of Birth],@ReportDate) between 25 and 35 THEN '26-35'
WHEN DATEDIFF(YY,d.[Date of Birth],@ReportDate) between 36 and 45 THEN '36-45'
WHEN DATEDIFF(YY,d.[Date of Birth],@ReportDate) > 45 THEN '46+' END AgeGroup,
CASE WHEN DATEDIFF(YY,d.[Date of Birth],@ReportDate) < 25 THEN 1
WHEN DATEDIFF(YY,d.[Date of Birth],@ReportDate) between 25 and 35 THEN 2
WHEN DATEDIFF(YY,d.[Date of Birth],@ReportDate) between 36 and 45 THEN 3
WHEN DATEDIFF(YY,d.[Date of Birth],@ReportDate) > 45 THEN 4 END AgeGroupOrder
INTO #Financials
FROMFactFin_Staging a
LEFT JOINDimLoanStaging b
ON a.Loan_Key = b.Loan_Key
LEFT JOIN DimProperty_Staging c
ON a.Property_Key = c.Property_Key
LEFT JOINBorrowerDim_Staging d
ON a.Borrower_Key = d.Borrower_Key
WHERE LoanDate <= @ReportDate
--Select * From [dbo].[Dim_Borrower] where ssn='123234578'
SELECT *
FROM #Financials
WHERE (@LoanAmount is NULL or LoanAmountGroup IN (SELECT ID FrOM dbo.split(@LoanAmount,',')))
AND (@LoanPurpose is NULL or [Purpose of Loan] IN (SELECT ID FROM dbo.split(@LoanPurpose,',')))
AND (@PropertyUsage is NULL or [Property Usage] IN (SELECT ID FROM dbo.split(@PropertyUsage,',')))
AND (@Demographics1 is NULL or (AgeGroup IN (SELECT ID FROM dbo.split(@Demographics1,','))
OR [Marital Status] IN (SELECT ID FROM dbo.split(@Demographics1,','))
OR Race IN (SELECT ID FROM dbo.split(@Demographics1,','))
OR Sex IN (SELECT ID FROM dbo.split(@Demographics1,','))))
END
--EXEC proc_LoansProcessed_ToDate @ReportDate='09/09/2012'
GO
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply