Reporting Services Date Parameters

Being the guy that I am I often try to implement pure TSQL solutions especially when working with parameters via Reporting Services. I know it seems like they go hand-in-hand and to a great extent they do, but there are some cases where it is best to use expressions for the report parameters over a stored procedure. Don’t get me wrong my TSQL approach worked fine but when I used the expression I was able to accomplish the exact same results with slightly less syntax to write and possibly less overhead. I stumbled upon the blog post “Calculating the first and last day of the month” by datageek on blogspot which illustrates the expressions I used for my report.

TSQL Syntax

SELECT DATEADD(Month,DATEDIFF(Month,0,getdate()),-1)+1 'StartDate'
SELECT DATEADD(Month,DATEDIFF(Month,0,getdate())+1,-1) 'EndDate'

RS Expression Syntax

Note: The following can be used for Access as well to get the first and last days of the month from what I understand.

First Day of the Month

=DateSerial(Year(Now), Month(Now), 1)

Last Day of the Month

=DateSerial(Year(Now), Month(Now) + 1, 0)

So for illustration purposes I put together a report based on the AdventureWorks database and used the RS Expression Syntax to populate the date fields, but first let’s create the stored procedure.

Stored Procedure

CREATE PROCEDURE sp_RP_GetEmployeeData
(
	@StartDate datetime
	,@EndDate datetime
)

AS

SELECT	e.EmployeeKey
	--, e.ParentEmployeeKey
	--, e.EmployeeNationalIDAlternateKey
	--, e.ParentEmployeeNationalIDAlternateKey
	--, e.SalesTerritoryKey
	, e.FirstName
	, e.LastName
	--, e.MiddleName
	--, e.NameStyle
	, e.Title
	, e.HireDate
	--, e.BirthDate
	--, e.LoginID
	--, e.EmailAddress
	, e.Phone
	--, e.MaritalStatus
	--, e.EmergencyContactName
	--, e.EmergencyContactPhone
	, e.SalariedFlag
	--, e.Gender
	--, e.PayFrequency
	, e.BaseRate
	--, e.VacationHours
	--, e.SickLeaveHours
	--, e.CurrentFlag
	--, e.SalesPersonFlag
	--, e.DepartmentName
	--, e.StartDate
	, e.EndDate
	--, e.Status
	--, st.SalesTerritoryCountry
	--, st.SalesTerritoryRegion
	--, st.SalesTerritoryGroup

FROM AdventureWorksDW2008.dbo.DimEmployee AS e
JOIN AdventureWorksDW2008.dbo.DimSalesTerritory AS st
ON e.SalesTerritoryKey = st.SalesTerritoryKey
WHERE (HireDate BETWEEN @StartDate AND @EndDate)
ORDER BY e.HireDate

Now that we have the sProc in place building the report is pretty much the same for the most part; however, the report parameters have relocated. They are now found on the left hand side under the report data section. In 2005 you would have found the parameters under the Report menu.

Now to set default values for the parameters

1) Right click on the StartDate parameter and select “Parameter Properties”

5-26-2010 8-16-31 AMa

2) At the Properties screen click on “Default Values”

5-26-2010 8-25-56 AM

3) Then select “Specify Values”

5-26-2010 8-41-10 AM

4) Click on the “Expression” button

5-26-2010 8-47-24 AM

5) Use the RS Expression Syntax for the “First Day of the Month”

5-26-2010 9-01-28 AM

6) Now follow the steps 1-5 and set the default value for the EndDate parameter but this time use the RS Syntax for the “Last Day of the Month”

5-26-2010 9-07-08 AM

7) Let’s preview the report, there won’t be any data though but the parameters are defaulting as intended

5-26-2010 9-09-08 AM

To see the data simply change the date range to reflect 1/1/1996 to 7/1/1999 then press “View Report”

5-26-2010 9-15-54 AM

1) If you just want to see the date only and not the time just right click on the field and select “Text Box Properties”

5-26-2010 1-45-59 PM

2) In the Text Box Properties dialog select Number

5-26-2010 1-49-19 PM

3) From within the Category section select “Date”

5-26-2010 1-49-43 PM

4) Then select the format under “Type”

5-26-2010 1-50-09 PM

And here is the outcome…

5-26-2010 4-12-53 PM

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s