SSRS Date Parameters with Calendar

This SSRS article shows how to use the Data values or columns as parameters in a report and display a calendar to select dates. To do this, we must change the Parameter Data type from default Text to DateTime.

Right-click on the Datasets folder to create a new DataSet. Writing the below code inside the SSMS shows the data set that we use for this example.

The Sql query that we used above SSRS example is:

SELECT Cat.[EnglishProductCategoryName] AS Category, 
SubCat.[EnglishProductSubcategoryName] AS SubCategory,
Prod.EnglishProductName AS ProductName, Prod.Color, Fact.[OrderDate],
SUM(Fact.OrderQuantity) AS Orders, SUM(Fact.TotalProductCost) AS ProductCost,
SUM(Fact.SalesAmount) AS Sales, SUM(Fact.TaxAmt) AS Tax
FROM FactInternetSales AS Fact
INNER JOIN DimProduct AS Prod ON Fact.ProductKey = Prod.ProductKey
INNER JOIN DimProductSubcategory AS SubCat ON Prod.ProductSubcategoryKey = SubCat.ProductSubcategoryKey
INNER JOIN DimProductCategory AS Cat ON SubCat.ProductCategoryKey = Cat.ProductCategoryKey
--WHERE OrderDate BETWEEN '2010-12-29' AND '2011-01-02'
WHERE OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY Cat.[EnglishProductCategoryName], SubCat.[EnglishProductSubcategoryName],
Prod.EnglishProductName, Prod.Color, Fact.[OrderDate]
ORDER BY OrderDate

If you observe the above query, we have used the where condition with two parameters(@StartDate and @EndDate).

How to use SSRS Date Parameters with Calendar?

We have designed a simple table report of product sales with order date and formatted the font and colors.

Simple table report

In the report preview, we manually typed the dates for both the start and end order dates.

SSRS Date Parameters with Calendar Preview

Right-click on the @StartDate under the Parameters folder and choose the Parameter properties option. Change the @StartDate data type from default text to Date/Time in the general tab. Finally, click ok to close the properties window.

Change the Data Type from Text to Date/Time

Like the above, change the data type of @EndDate to date and time. Looking at the SSRS report preview, you can see the calendar beside the start and end date parameters. You can use this calendar to select the dates. 

SSRS Date Parameters with Calendar to select Day, Year, and Month

Once you have selected it, click the view report button to see the result.

SSRS Date Parameters with Calendar Preview

Once again, go to the Default Values tab of the @StartDate report parameter properties, choose to specify the value, and hit the add button. Next, click the little ƒx button to set the default start date. Here, CDate converts the string date to Date type.

Expression for default date values

Similarly, set the default values for the @EndDate as well using the above approach. The expression for the default value of the End date @EndDate is

CDate("2011-01-02").

If you click the SSRS calendar preview button, it will generate the report with the default values of start and end date parameters.

SSRS Date Parameters with Calendar with default values