quote:

---

Originally posted by jtrapat1

Im having a tough time refreshing the drop down values of my Reporting Services parameter box;
I think the problem is more on the type of join in the sql rather than the reporting services IDE, but Ill post it here;
Basically, this is a calendar application which takes multiple stored procedures to refresh the parameter boxes on the reports screen;
The first two parameters are a start and end date-
these refresh the dropdown on the next line which is populated with a Client name (and id selected in the sql)
Next I am passing this start and end date and clientid to get the available values for this clientid from two tables: Reservations AND Events. (The Events table is actually a view.)
My Start Date and End Date are OK and Im trying to pass to the second stored procedure to get All of the Clients (with IDs) who have reservations OR events in that Date Range-
Hers my query so far -
But, Im not getting all of the events and/or Reservations for the specific clientid;
-------------------------
SELECT DISTINCT R.[General/EventID], E.[EventName] --, R.[General/ClientID]
FROM Reservations R
LEFT OUTER JOIN
[vw_Events] E
ON E.[EventID]=R.[General/EventID]
where R.[General/EventID] is not null and E.[EventName] is not null
and (R.[General/ClientID]=@clientID)
AND (CONVERT(VARCHAR(10),R.[actual end],101) >= @startdate)
AND (CONVERT(VARCHAR(10),R.[actual end],101) <= @enddate)
------------------------
So, in the case where General/EventID is NULL for certain values, I don't get returned the three records for this ClientID from the Reservations table.
Is there something I could do differently to get all of the records in these two tables for this client regardless, And not show NULL in the drop down?

Thanks
Please Help.
John

---

SELECT DISTINCT R.[General/EventID], E.[EventName] --, R.[General/ClientID]  
        FROM Reservations R 
      	LEFT OUTER JOIN 
        [vw_Events] E 
        ON E.[EventID]=R.[General/EventID] 
    where (R.[General/EventID] is not null or E.[EventName] is not null)
 and (R.[General/ClientID]=@clientID)
			AND (CONVERT(VARCHAR(10),R.[actual end],101) >= @startdate) 
            AND (CONVERT(VARCHAR(10),R.[actual end],101) <= @enddate)