Variables linking

Hi Kathi, having just watched week 2 i have a question on the variables. I have a scenario where i need to use my first drop-down to be selective of one of 2 sources of info. lets say Departure Date or Return date. These would be 2 different columns in a table as the source of the data, to dictate the rest of the data for that day only, depending on whether it was a departure or a return. The second drop-down would be a date selector to be fed into the first parameter. The date of the Departure or Return. How could these 2 parameters be linked, when the source of the first is changing. 

Best Answer

  • KathiKKathiK Posts: 15 Bronze 2
    Answer ✓
    Assuming that the first parameter is called @Type and it returns a string of either "DepartureDate" or "ReturnDate" then this query would work: 

    SELECT DepartureDate AS TheDate
    FROM Schedule 
    WHERE 'DepartureDate' = @Type
    UNION ALL 
    SELECT ReturnDate 
    FROM Schedule 
    WHERE 'ReturnDate' = @Type

Answers

  • Are you saying that the dataset of the first dropdown changes? I'm not sure how that would work.
  • Can you upload an image of what this report looks like?
  • I'm thinking that you probably want to use a stored procedure for the dataset if your data is in SQL Server.
  • TLATLA Posts: 3 New member
    Hi Kathi,

    The first dropdown would dictate the whether we wanted to know the data for departures, or returns for a certain date, fed in by another parameter date dropdown. let's say it's an Holiday Scheduler. And i want to know the Airport, Airline, departure time, passenger info etc for all holidays for a date. But depending on the first dropdown, the reports is going to give me departures for that date, or returns for that date. 

    So the query would be 

    Select DepartureDate From Schedule 
    Or 
    Select ReturnDate From Schedule

    Hope that makes it clearer.

    Thanks
  • Probably the easiest way to do this would be to have the second drop down be based on a stored procedure that has an IF so that the correct query runs. But, you might be able to do something with a UNION of the two queries. I have a meeting in a couple of minutes, but I'll get back to you with some pseudocode after that.

Sign In or Register to comment.