Solved

UNION with CASE or IF ELSE block with Reporting Services

Posted on 2008-06-18
13
2,976 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 15

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 15

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 15

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 15

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 35
Need help with a query 3 44
report returning null 21 79
upgrade sql 2005 32bit to sql 2008 32 or 64bit on a server 2008 r2 box 6 53
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A short film showing how OnPage and Connectwise integration works.

914 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

16 Experts available now in Live!

Get 1:1 Help Now