Date Parameters and Things to Consider

I touched on Reporting Services Date Parameters, but what I did not cover is the underlying importance of understanding how TSQL works in terms of date parameters. You need to know that there is an implicit midnight time-stamp of 00:00:00 when a date is only supplied. So when you are dealing with date parameters in terms of FROM and TO dates, the TO will need or should include all records for that date as well. However, since a date is only supplied your chances of including all records for that end date are very slim. I was able to correct this by adding a modifier in stored procedures that takes the input date and increments it by one day.

For illustration purposes here are some TSQL scripts that will allow you to test a few queries to see what is actually begin returned when you only pass a date to a DATETIME field. This will help you understand what the results of your report will be as well.

Step 1: Create Table

CREATE TABLE #TestDate
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[StandardDT] [datetime] NOT NULL,
	[MyKey] [int] NOT NULL,
	[Notes] [nvarchar](100) NULL,
)

Step 2: Populate Table

INSERT INTO #TestDate (MyKey, StandardDT, Notes) 
SELECT 1, '1/1/2010', 'Note 1'
UNION ALL
SELECT 2, '2/1/2010', 'Note 2'
UNION ALL
SELECT 3, '3/1/2010', 'Note 3'
UNION ALL
SELECT 4, '4/1/2010 10:42:22', 'Note 4'
UNION ALL
SELECT 5, '4/1/2010 22:42:22', 'Note 5'
UNION ALL
SELECT 6, '6/1/2010', 'Note 6'
UNION ALL
SELECT 7, '7/1/2010', 'Note 7'
UNION ALL
SELECT 8, '7/1/2010 01:10:19', 'Note 8'
UNION ALL
SELECT 9, '7/1/2010 02:11:20', 'Note 9'
UNION ALL
SELECT 10, '7/1/2010 21:21:22', 'Note 10'

Step 3: Verify Data

SELECT * FROM #TestDate

As you can see we have 10 records total with some records that have a time-stamp associated that are beyond midnight specifically April and July while the the other months are midnight.

Step 4: Test Queries

Let’s run through some test queries to help better illustrate my point. Let’s query the data using a FROM date of 3/1/2010 and a TO date of 4/1/2010. Looking at the data there is one record for 3/1/2010 and two records from 4/1/2010. How many records do you think will be returned? Well let’s just see for ourselves.

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '3/1/2010'
SET @EndDate = '4/1/2010'

SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

Interesting… there is only one record returned even though 4/1/2010 is within our query parameters. Take a look at lines 8 & 9 from the query above and pay attention to the time-stamps. You can see in the screen cast below that the TO date which is represented by @EndDate parameter is 4/1/2010 00:00:00 which is 4/1/2010 midnight. Well looking through the data proves we do not have any records that meet that specific criteria because there are no records for 4/1/2010 that have a midnight time-stamp associated.

Let’s try a few more test queries just to get a clearer picture. Let’s see how many records are returned when we search FROM 7/1/2010 TO 7/1/2010. We know that our data set contains four records for 7/1/2010, but can you guess how many records will be returned if we want to search FROM 7/1/2010 TO 7/1/2010?

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '7/1/2010'
SET @EndDate = '7/1/2010'

SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

There was only one record returned because there was only one record that met the criteria 100%. If you look at the values for @StartDate and @EndDate they both are returning 7/1/2010 00:00:00.

Now let’s run through one last query, because practice makes perfect. If I were to search our data set for all records from 4/1/2010 and supply a FROM date of 4/1/2010 and a TO date of 4/1/2010 how many records would be returned? The answer is zero, because both you and I know there are no records within the data set that have a time-stamp of 00:00:00 for 4/1/2010.

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '4/1/2010'
SET @EndDate = '4/1/2010'

SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

Perfect. No records returned just as we thought.

Step 5: Solution

The easiest way to rectify this is to ask the end users to use the next day, but that is not a practical answer because you and I both know after the first weekend or vacation the end users will return to their normal mode and begin searching through the data using the date they did before. So in order to satisfy the requirements I decided to add a parameter modifier to my procedure which takes the received input value for the @EndDate and increments the day by one. Meaning if 7/1/2010 were passed as the TO or @EndDate value it would become 7/2/2010 00:00:00 and all records from 7/1/2010 will be returned. Pretty simple.

ALTER PROCEDURE sp_RP_MyReport
(
  @StartDate DATETIME
  ,@EndDate DATETIME
)

AS 

SET @EndDate = DATEADD(d,1,@EndDate)

Line #: 9 in the above syntax is what I implemented which makes the adjustment to the @EndDate parameter.

Step 6: Validate Solution

Let’s add the modifier just below the parameter declarations and above the SELECT statement and see what we get when we execute the script. Can you guess what will be returned?

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '3/1/2010'
SET @EndDate = '4/1/2010'

SET @EndDate = DATEADD(d,1,@EndDate)
SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

Success! Three records returned. By adding the modifier (Line #: 5) the value was accepted, incremented by 1 and supplied back to the SELECT statement and the records which fell into the criteria were returned.

Step 7: Cleanup

Now that we are all squared away, let’s try not forget that we still need to drop the temp table.

DROP TABLE #TestDate

Side Note


You can always use the DATEADD in the BETWEEN statement like below in line 06.

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '3/1/2010'
SET @EndDate = '4/1/2010'

SELECT * FROM #TestDate
WHERE StandardDT BETWEEN @StartDate AND DATEADD(d,1,@EndDate)
Advertisements

Delivering Subscriptions Outside of the Ogranization

Recently I faced an issue with reporting services 2005 when attempting to deliver subscriptions to addresses outside of the organization. Internal addresses received the email based subscription deliveries
without any questions. I must have checked and re-checked the settings using RSConfigTool about million times, looking for anything I might have overlooked. The error message I received was, “The e-mail address of one or more recipients is not valid”. After some research (which lead me to a lot of dead ended forums) I read the phrase “email relay”, that’s when the gears started spinning.

I realized that the issue had nothing to do with the configuration of SQL Server Reporting Services; rather, the SMTP server! In order for the messages to be delivered outside of the organization the Reporting Services Server needed to be authorized so-to-speak. Unfortunately I don’t have access to Exchange 2003 so I cannot provide screen shots, but for 2007 all you need to do is add the server’s IP Address to the SMTP server’s receiver group in the HUB Transport configs.

Then to test your subscription without tweaking the schedule execution time just run the SQL job! To find out the name of the job use the attached sql script. If you have a named instance append $instancename to all three of ReportServer occurrences (i.e. ReportServer$InstanceName) for MSSQL 2005. I believe for MSSQL 2008 you would append _InstanceName (i.e. ReportServer_InstanceName), but I am not certain. You should get the results similar to the screen shot attached.

SELECT	
	sj.[name] AS [Job Name],
	c.[Name] AS [Report Name],
	c.[Path],
	su.Description,
	su.EventType,
	su.LastStatus,
	su.LastRunTime

FROM msdb..sysjobs AS sj 
JOIN ReportServer..ReportSchedule AS rs ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128)) 
JOIN ReportServer..Subscriptions AS su ON rs.SubscriptionID = su.SubscriptionID 
JOIN ReportServer..[Catalog] c ON su.Report_OID = c.ItemID
 
/*
USAGE:

USE [msdb]
EXEC sp_start_job @job_name = 'AF015D8B-D80D-4D2A-9808-CD1D519B3332'

NOTE:
If using a named instance use ReportServer$Instance_Name for 2005
For 2008 I believe you only need to change the $ to _ when using 2008 (i.e. ReportServer_Instance_Name)
*/

Repeating Tablix Column Headers with SSRS 2008

There seems to be an issue with repeating column headers using SQL Server 2008 Reporting Services. Typically you would highlight the row, right-click and select properties then you would be able to set the property for RepeatOnNewPage to True. However this is not the case with SSRS 2008. I tried it and it does not work and I cannot speak for SSRS 2008 R2, but I will give it a whirl and post my findings here to confirm if the behavior is the same.

I tried selecting both options in the Row Headers and Column Headers sections without any luck.

I tried selecting all options and a combination of options then tested the report to see if the headers would repeat and much to my dismay they did not.

As you can see the headers did not repeat. So I cursed a little and decided to hit the web to see if I could under cover the reason why and at the very least find a solution to resolve this.

So I scoured the web in search for answers and came across a blog post by Nick Olson titled: Repeating Tablix Header in SSRS 2008. I followed his instructions, but couldn’t determine where the group pane arrow was located because I was not as familiar with the BIDS layout as I would have liked to be. Nonetheless after some intense yet careful screen staring to the point of nearly going cross-eyed I finally found the little bugger. I was able to make the header repeat and this is how I did it.

Step 1: Click Tiny Black Arrow

The very tiny and almost unnoticeable arrow that sits atop the Group Pane, close to the properties windows on the right hand side of the report designer. You would have thought this would have been more apparent and noticeable.

Step 2: Enable Advanced Mode

There is only one item and that is Advanced Mode

Step 3: Select Static Field

I selected the Static field that was nested above the Details field in the Row Group section of the Group Pane.

Step 4: Set Tablix Member Properties

In this step I had to change two settings, but for whatever reason Nick was able to get by with only making one setting change. I had to change the KeepWithGroup and RepeatOnNewPage in order for the headers to repeat. When I just changed the RepeatOnNewPage the headers did not repeat.

There are three options for KeepWithGroup: (None, Before and After). Of course None did not work nor did before, then I tried After and wahlah! it worked. RepearOnNewPage only has two available attributes and those are: (False and True).

Step 5: Test Report

Shortly after testing all of the KeepWithGroup attributes and finally selecting After I was able to breathe a sigh of relief.

TADA! Repeating Headers

Side Note

When I selected one of the Static fields from within the Column Groups section and made the same Tablix Member Property changes I received the following error. I just wanted to make sure I made this known.

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]

02.BasicQueryInDataSproc

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.

04.DatasetSelect

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
05.PassedValue1

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
06.PassedValue-1

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

01.Rpt

Step 1: Select All

02.Rpt

Step 2: Run Report

03.AllRecs

Results when value other than All is selected

04.SingleRec