UNION with CASE or IF ELSE block with Reporting Se

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

UNION with CASE or IF ELSE block with Reporting Se

Author

Topic

jtrapat1
Starting Member

43 Posts

Posted - 2008-06-18 : 19:45:59

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.
Any ideas?
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.
Thanks
John
----------------------------------------------
ALTER PROCEDURE [dbo].[rsp_cc_clientevents]
(
@clientID int,
@startdate DATETIME=NULL,
@enddate DATETIME=NULL
)
AS
BEGIN
--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
INNER JOIN
[vw_Events] E
ON R.[General/EventID]= E.[EventID]
WHERE (R.[General/ClientID]=@clientID)
AND (R.[actual start] BETWEEN @startdate AND @enddate)
UNION
ALL
SELECT 0,'All',0
ORDER BY E.[EventName]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 03:16:19

The sp looks ok. Didint understand the problem you're having. Are you passing the parameter client ids value to this dataset?

jtrapat1
Starting Member

43 Posts

Posted - 2008-06-19 : 09:36:02

Yes,
I am passing the clientid to this dataset.
the second drop down is dependent on this clientid.
A clientid could have one or many events, therefore displaying multiple events in the drop down;
Or, a client could have no events, in which case, I would like to show "No Events" as a selection in the drop down.
Thanks
John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 11:03:44

Try setting No Events as default value for event parameter.

jtrapat1
Starting Member

43 Posts

Posted - 2008-06-19 : 12:02:09

I tried that but the event parameter is the wrong data type.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:09:16

quote:
Originally posted by jtrapat1

I tried that but the event parameter is the wrong data type.

is it integer type?

jtrapat1
Starting Member

43 Posts

Posted - 2008-06-19 : 12:34:47

Yes, I cant declare an int in the default value of SSRS.
Is there some way I could cast this value?
---
Another attempt was to do this if else block to search ahead but I cant get it to work, either...
I cant find how to incorporate it into my coding.
---
DECLARE @param int
SELECT @param =
(
SELECT count(*) from
(
SELECT 'one' as col1
UNION
SELECT 'all'
) as t1
)

IF( @param > 1 )
BEGIN
SELECT 'one' as col1
UNION
SELECT 'two'
END

ELSE
BEGIN
SELECT 'no entries'
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:45:20

quote:
Originally posted by jtrapat1

Yes, I cant declare an int in the default value of SSRS.
Is there some way I could cast this value?
---
Another attempt was to do this if else block to search ahead but I cant get it to work, either...
I cant find how to incorporate it into my coding.
---
DECLARE @param int
SELECT @param =
(
SELECT count(*) from
(
SELECT 'one' as col1
UNION
SELECT 'all'
) as t1
)

IF( @param > 1 )
BEGIN
SELECT 'one' as col1
UNION
SELECT 'two'
END

ELSE
BEGIN
SELECT 'no entries'
END

You can try another method too. Just create another dataset with the following query as the source statement.

SELECT 'No Events' AS Label, -1 AS Value

and then use this dataset for setting default value of EventID parameter. jsut choose option From Query under default values and select this dataset as source. And Value for value field and Label for display field in parameters window.
Now preview the report in preview tab and see if it works.

jtrapat1
Starting Member

43 Posts

Posted - 2008-06-19 : 16:51:13

I was trying to use your code and I think it will work but I think my join with the events table may be wrong;

You see, eventid is not a required field and it can be NULL or 0.
Is there any way to correct this, either by changing the join or changing the table design?
For example, I have a ClientID =475, who has three records in the Reservations table 22764,22766,and 22765.
But the EventID in the Reservations table for these three records are: 0,0, and NULL;
since the Event field is not required.
So, when I join the Reservation table and the Event table on EventID, I dont get any matches;
Even though, I would still like to return 'No Events' in the drop down.
This is very confusing.
Thanks for the help.
John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 06:01:17

quote:
Originally posted by jtrapat1

I was trying to use your code and I think it will work but I think my join with the events table may be wrong;

You see, eventid is not a required field and it can be NULL or 0.
Is there any way to correct this, either by changing the join or changing the table design?
For example, I have a ClientID =475, who has three records in the Reservations table 22764,22766,and 22765.
But the EventID in the Reservations table for these three records are: 0,0, and NULL;
since the Event field is not required.
So, when I join the Reservation table and the Event table on EventID, I dont get any matches;
Even though, I would still like to return 'No Events' in the drop down.
This is very confusing.
Thanks for the help.
John

even if you dont get any matching rows from reservation table, you'll still have this single row no event,-1 which is set to default value and hence no events will show on your drop down.

jtrapat1
Starting Member

43 Posts

Posted - 2008-06-20 : 14:28:57

visakh -
Thanks for the help.
That worked for my selection criteria.
Report is working correctly now.
Thanks again.

John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 14:37:46

quote:
Originally posted by jtrapat1

visakh -
Thanks for the help.
That worked for my selection criteria.
Report is working correctly now.
Thanks again.

John

no worries. you're always welcome. Glad that i could help you out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources