Report Preview Error

  • 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