SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Report Preview Error


Report Preview Error

Author
Message
sife1965
sife1965
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search