UNION and Reporting Services 2008

I have used the UNION operator a few times in the past and during a recent Business Intelligence project I quite possibly used it more then I had in the past combined. Typically you can merge queries together simply by placing a UNION operator between two queries. Well it’s not that simple there are some restrictions.

• the number of fields must be the same in both queries
• the order of the fields must be the same in both queries
• data types must be compatible

What you don’t believe me? Well then visit the TechNet site: UNION Transact-SQL and see it for yourself.

syntax

The syntax is really nothing…

SELECT Column1, Column2 FROM table1
UNION
SELECT Column1, Column2 FROM table1

That’s the gist of the it. Kind of boring right? Yeah a little bit. When we incorporate it within a stored procedure that is used for Reporting Services it becomes a tad more interesting. I can’t speak for you but at least it is for me. Now let’s get into some scenarios where it comes handy.

Let’s say I want to offer a report that uses a dropdown list for selecting and passing the parameters. Well typically in a select list you can only choose one, but what if you wanted an option to select all? This is where the UNION comes in handy.

procedure

Typically you would construct your procedure similarly to the one below.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_GetProspectBuyerData'))
DROP PROCEDURE sp_RP_GetProspectBuyerData
GO

CREATE PROCEDURE sp_RP_GetProspectBuyerData
(
	@id INT
)
AS
SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id)

The basics of the procedure is a simple SELECT statement. So let’s take a look at the results when I just run (lines: 12-18) only.

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

The only problem with this procedure is with (line: 19). The choice is either one value or another. There is not option for a “show all value” to be passed.

WHERE (ProspectiveBuyerKey = @id)

This is where the UNION operators comes in. Not necessarily in the stored procedure per se but in the dataset you create to populate the select list. However you still need to modify the stored procedure to prepare for whats to come. This is a slight modification to the WHERE clause of the stored procedure.

WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Here is the final base for the “sp_RP_GetProspectBuyerData” stored procedure with the added OR operator.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_GetProspectBuyerData'))
DROP PROCEDURE sp_RP_GetProspectBuyerData
GO

CREATE PROCEDURE sp_RP_GetProspectBuyerData
(
	@id INT
)
AS
SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

dataset

Now we need to construct the dataset stored procedure that we will use to populate the select list which will be used in our report. This is the basics of the stored procedure that we will be using to drive the select list on the report.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_ProspectBuyerDDL'))
DROP PROCEDURE sp_RP_ProspectBuyerDDL
GO

CREATE PROCEDURE sp_RP_ProspectBuyerDDL
AS
SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

Well the above procedure is cool and all but it does not meet our needs as we need to have an “ALL” option listed as a selectable item in the select list. In order to achieve this let’s add another SELECT statement that will correspond to the changes we made to the first procedure “sp_RP_GetProspectBuyerData” where we added the following: ( OR @id = -1 ) and use UNION operator to merge them.

To list the “ALL” in our select list we need to add it using the same data type, order and we must have the same amount of fields. So here is what we need.

SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'

So now we need to merge the two SELECT statements and this is how its done.

Step 1: First we take…

SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'

Step 2: Then we add

UNION

Step 3: And finally we append…

SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

Step 4: We wrap them into a stored procedure

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_ProspectBuyerDDL'))
DROP PROCEDURE sp_RP_ProspectBuyerDDL
GO

CREATE PROCEDURE sp_RP_ProspectBuyerDDL
AS
SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'
UNION
SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

Now if we run (lines: 9-15) only here is what the results look like.

As you can see the “ALL” is on top which is what we need and for the sake of testing. Let’s run through two quick scenario of passing a value of 1 and a value of -1 to the @id parameter just to see what the end results look like.

Here I set the @id variable to 1…

USE [AdventureWorksDW2008]

DECLARE @id INT = 1

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Results

Now let’s see what happens when we pass a value of -1…

USE [AdventureWorksDW2008]

DECLARE @id INT = -1

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Results

Boom! All records are returned. Exactly what I needed. Now let’s run through the report! I already set the available values for the parameter properties so we can just jump right into the report and see it in action.

Business Intelligence Development Studio

Step 1: Select All

Step 2: Run Report

Results when value other than All is selected