SSRS Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SSRS Question

Author

Topic

bvanyangu
Starting Member

20 Posts

Posted - 2014-07-10 : 07:17:02

Below is what I get when I preview my report.

The report parameter "Demographics1" has a defaultValue or a Validvalue that depends on the report parameter "Demographics".Forward dependencies are not valid..

I googled it and got only 1 respond..Here is the respond... I'm not asking you to delete the parameters altogether. Just the linking of those parameters to the DocDate Dataset. That's what is wrong with you report definition. The DocData dataset depends on the startdate parameter and the startdate parameter depends on the DocData dataset. This causes a circular reference and that causes the error...

The video provided on how to do that is not opening.Anyone to explain how to solve this Issue.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-10 : 07:40:47

You've not really posted enough info to work with. Do you have a parameter "Demographics1"? If so, look at its definition. Does either the default value or valid value refer to the parameter "Demographics"? If so, that's your problem. It's not allowed (That's what "Forward dependencies are not valid..") means

bvanyangu
Starting Member

20 Posts

Posted - 2014-07-10 : 09:16:59

Here is the whole query with the Parameters, so In the Parameter properties,I select "Get values from this query",I am still confused on what to do then to avoid getting this error.

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-10 : 10:48:31

Based on the error message you posted, I don't think your problem is the query. I think it's the report definition

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources