I am using sql server 2005 with Reporting Services -
I have a report which uses two stored procedures in drop down selections: client in one; event in the second.
The second drop down is dependent on the first drop down selection.
If the client selected has event(s) the second drop down will be refreshed and display 1 or more events for that client.
If the client does not have any events attached, I would like to return a default drop down selection of "No Events" along with the default in SSRS of '<Select a Value>'
(Or, disable the drop down entirely, if possible.)
Attached is my union query which always brings back the "All" case regardless if the client has an attached event or not.
I tried a CASE statement around the UNION; an IF..ELSE...block around the UNION.
and I was going to try a cursor to select a count before executing the UNION.
ALTER PROCEDURE [dbo].[rsp_cc_clientevents]
--show event,eventid in drop down
--case where client has more than one reservation
SELECT R.[general/EventID], E.[EventName], R.[General/ClientID]
FROM Reservations R
ON R.[General/EventID]= E.[EventID]
AND (R.[actual start] BETWEEN @startdate AND @enddate)
ORDER BY E.[EventName]