Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Report Preview Error Expand / Collapse
Author
Message
Posted Thursday, July 10, 2014 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 7:47 AM
Points: 1, Visits: 0
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 CASE WHEN @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 >= @BeginOfWeek AND 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 >= @BeginOfWeek AND 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
FROM FactFin_Staging a
LEFT JOIN DimLoanStaging b
ON a.Loan_Key = b.Loan_Key
LEFT JOIN DimProperty_Staging c
ON a.Property_Key = c.Property_Key
LEFT JOIN BorrowerDim_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
Post #1591162
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse