Solved

UNION with CASE or IF ELSE block with Reporting Services

Posted on 2008-06-18
13
2,973 Views
Last Modified: 2008-06-20
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]

Open in new window

0
Comment
Question by:jtrapat1
  • 5
  • 4
  • 4
13 Comments
 
LVL 14

Expert Comment

by:rob_farley
ID: 21818298
Use WHERE to get rid of the bit you don't want:

...
UNION
ALL
SELECT   0,'All',0  
WHERE EXISTS (SELECT * FROM ....)

ORDER BY E.[EventName]

Hope this helps - I totally get that using WHERE without a FROM clause feels a bit alien.

Rob
0
 
LVL 21

Expert Comment

by:Yurich
ID: 21818306
Hello,

I'm not sure if it's the most elegant piece of code, but it will work. This test script will work as is in any database, just give it a good look. It first checks for the number returned values and if it's more than one, it will do a normal processing, but otherwise, it will return your "no events" value.

Good luck,
Yurich


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

Open in new window

0
 
LVL 14

Expert Comment

by:rob_farley
ID: 21818490
Incidentally - my method will give you a blank recordset for that field. If you have marked the parameter as not required then you should be able to get away with this as your "No entries" option.
0
 

Author Comment

by:jtrapat1
ID: 21822477
rob,
I tried what you suggested but I got confused -
Could you expand a little on your answer?

I think it will work but I think my join is confusing me.

Thanks
John
0
 
LVL 21

Expert Comment

by:Yurich
ID: 21824886
Have you tried mine?? If you just copy paste the whole thing into a query analizer or management studio it will run just fine. THen you'll just have to change bits to your script.
0
 

Author Comment

by:jtrapat1
ID: 21826315
Yurich,
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 Yurich.
John
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 21

Accepted Solution

by:
Yurich earned 250 total points
ID: 21826593
I have modified my script somewhat and incorporated your code in it, so if it was working before, you should be able just copy-paste what's below and it should produce what you're looking for.

Cheers,
Yurich
DECLARE @param int

SELECT @param =

( 

        SELECT count(*) from 

        (

			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) 

        ) as t1

)

 

IF( @param > 0 )

BEGIN

	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]

END

 

ELSE

BEGIN

        SELECT 0, 'NO ENTRIES', 0

END

Open in new window

0
 

Author Comment

by:jtrapat1
ID: 21827554
Yurich,
Thank You.
i started out with something like this; a temporary table, but dropped it because I thought it would slow  performance.
I will test this code out tomorrow.
Do you think my database design is incorrect?
Thanks again.
John
0
 
LVL 21

Expert Comment

by:Yurich
ID: 21827606
You're welcome.
It's really hard to say anything about your database design just on the basis of what I know.

Good luck,
Yurich
0
 
LVL 14

Expert Comment

by:rob_farley
ID: 21827644
My bit of script was just introducing the idea of using a WHERE clause to stop the 'All' scenario appearing if there are no entries.

Many people don't realise that you can have:

SELECT 'All'
WHERE ...

because in their heads, WHERE goes in 'proper' queries, which use a FROM clause. But with UNION, it becomes a really nice option, because you don't need to use IF statements with repeated code.
0
 
LVL 21

Expert Comment

by:Yurich
ID: 21827710
2rob: yes, your way will work but it will not put "No Entries" where there is no entries as John asked; it will just show nothing. A good  idea of using the Where clause anyway.

I think there is a way of doing it without IFs but that would involve rather complex manipulations with CASEs, so I'd just let John to try to get it working in as simple way as possible.

Cheers,
Yurich
0
 
LVL 14

Expert Comment

by:rob_farley
ID: 21827732
Sure, but you can easily have SSRS handle the NoEntries scenario.

His question states:

(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.

So I was providing a method for not returning the "All" case, which would produce the required effect in SSRS.
0
 

Author Comment

by:jtrapat1
ID: 21833753
Thanks for all the help-
I think I have it working with the code from Yurich-
My big problem:
Since Im using three stored procedures as datasets for the report and each build upon the previous values
BUT all use the initial set start and end date ranges, I had trouble passing this date range throughout the report.

It looks right now but I wanted to thank you again.
John
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now