CODEWITHSUNDEEP
sqlreporting-servicesparametersssrs-2008cascading
Aruna Raghunam
Aruna Raghunam

Reputation: 921

Cascading parameters not working as expected

I have a report with 4 parameters which need to be populated automatically when a report opens.

Cascading Parameters

2 parameters work fine but Lecturer parameter is not selecting any values.

Queries:

Year:
SELECT REGT_Year
FROM CurrentAttendance_New

CourseType:
SELECT DISTINCT CourseType
FROM            CurrentAttendance_New
WHERE        (REGT_Year = @Year)

Lecturer:
SELECT DISTINCT LectName
FROM            CurrentAttendance_New
WHERE        (CourseType IN (@CourseType))
ORDER BY LectName

        Register:

        SELECT DISTINCT Class_Register COLLATE 
    DATABASE_DEFAULT + ' - ' + Register_Title 
    COLLATE DATABASE_DEFAULT + ' - ' + 
    Register_Day COLLATE DATABASE_DEFAULT + '  ' + 
    CONVERT(char(5),StartTime, 108) + ' - ' 
        + CONVERT(char(5), EndTime, 108) AS Register
        FROM            CurrentAttendance_New
        WHERE         (REGT_Year = @Year) 
AND (CourseType IN (@CourseType)) 
AND (LectName IN (@Lecturer))   (LectName IN (@Lecturer))

Available values

Default Values

Any idea where am I going wrong?

Upvotes: 0

Views: 841

Answers (1)

StevenWhite
StevenWhite

Reputation: 6034

The problem here is that in your Lecturer query you're using an "in" statement, but you're passing it a single string of values which won't exist (you are probably Joining the values of the parameter for that). One option is to use a Split function to pass all the Course Types into it. This can be done in the query or as a DataSet filter. There are several posts out there if you search for that, but this should at least point you in the right direction.

Upvotes: 1

Related Questions