Solved

UNION with CASE or IF ELSE block with Reporting Services

Posted on 2008-06-18
13
2,979 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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