CODEWITHSUNDEEP
reporting-services
wafw1971
wafw1971

Reputation: 361

Reporting and Datasets and Parameters

I have created a report in SSRS and it works fine if I want to type in the 2 parameters, but what I want is to create a dropdown for each of the parameters instead. So I created the main dataset and the created two additional datasets so my parameter can point at them to create the list, but Its not working, can you figure out why by looking at my datasets below?

Main Dataset

SELECT       Racecourse.RacecourseID, Racecourse.Name AS Racecourse, Horses.Name AS Horse, RaceResults.Place AS Results, Jockeys.FirstName + ' ' + Jockeys.LastName As 'Jockey', Race.TimeofRace
FROM            Race INNER JOIN
                         Racecourse ON Race.RacecourseID = Racecourse.RacecourseID INNER JOIN
                         RaceResults ON Race.RaceID = RaceResults.RaceID INNER JOIN
                         Horses ON RaceResults.RacehorseID = Horses.RacehorseID INNER JOIN
                         Jockeys ON RaceResults.JockeysID = Jockeys.JockeysID

WHERE @Racecourse = Racecourse.Name AND @TimeofRace =  Race.TimeofRace


ORDER BY Place

Racecourse Dataset

SELECT
  Racecourse.RacecourseID
  ,Racecourse.Name
FROM
  Racecourse
WHERE @Racecourse = 'Racecourse.Name'

TimeofRace Dataset

SELECT
  Race.RaceID
  ,Race.TimeofRace
FROM
  Race

WHERE @TimeofRace = 'Race.TimeofRace'

Thanks in advance.

Wayne

Upvotes: 1

Views: 186

Answers (1)

djangojazz
djangojazz

Reputation: 13242

You are doing it backward. Your datasets for parameters cannot be defined by your main dataset because they are DEPENDENT on the values.

EG: You cannot ask for @Racecourse in two datasets before it has been defined. It needs to be defined as a value.

I am guessing if you remove the predicates ('Where' clauses) from your two other datasets it will work just fine. That is if you are populating the values of the variables with the option: "Get values from a query".

Upvotes: 3

Related Questions