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. The below screenshot 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.
In the report preview, we manually typed the dates for both the start and end order dates.
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.
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.
Once you have selected it, click the view report button to see the result.
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.
Similarly, set the default values for the @EndDate as well.
If you click the SSRS calendar preview button, it will generate the report with the default values of start and end date parameters.
