Using the "Available Values" for Dates in a SSRS Parameter

Question

I have a requirement to provide a dropdown Date Option providing the following:

1. Custom - allow date entry or using the Calendar control
2. YTD - populate the StartDate & EndDate parameters with the 1st of January to today
3. MTD - populate the StartDate & EndDate parameters with the 1st day of Month to today
4. QTD - populate the StartDate & EndDate parameters with the 1st day of Qtr to today
   etc

Using a Custom Code VB script to provide the Date for both the Start & End Date.
Function GetStartDate(ByVal intChoice As Integer) as Date

Select Case intChoice   
    	Case 0  
       		GetStartDate = Nothing  
Case 1  
   	GetStartDate = DateSerial(YEAR(Today), 1, 1)  
Case 2  
   	GetStartDate = DateSerial(YEAR(Today), MONTH(DateAdd(DateInterval.Quarter, 0, Today)), 1)  
Case 3  
   	GetStartDate = DateSerial(YEAR(Today), MONTH(Today), 1)  
Case 4  
   	GetStartDate = DATEADD(DateInterval.Month, -12, Today)  
Case 5  
   	GetStartDate = DATEADD("m",-1,dateserial(year(Today),month(Today),1))  
End Select  

Return FormatDateTime(GetStartDate,2)  

End Function

The StartDate parameter is setup as a DateTime and the "Available Values" and the "Default Values" are set to use this custom code to preload the parameter. When only the "Default Values" is set and the "Available Values" is set to None, the Start & End Date parameters are set correctly using the "dd/MM/yyyy" but will not change when the Date Option is changed unless the report is refreshed.

When the "Available Values" and the "Default Values" are set to use this custom code, the Start & End Date parameters are set using the "dd/MM/yyyy HH:mm:ss" and will function propertly when the Date Option is changed.

My problems

1. Eliminating the time section of the Start & End Date parameters.
2. Setting the Start & End Date parameters to blank when the "Custom" option is selected.

Answer 1

Hi @InspiredUser33157 ,

Eliminating the time section of the Start & End Date parameters.

I think the best way to remove the "Time" part of "DateTime" is to use CDate() Function
For example: =CDate(Now).ToString("dd/MM/yyyy")
For more information, please refer to: https://stackoverflow.com/questions/1395441/reporting-services-remove-time-from-datetime-in-expression

Setting the Start & End Date parameters to blank when the "Custom" option is selected.

What I can guess here is that you may need to use cascading parameters: Add Cascading Parameters to a Report (Report Builder and SSRS).
Hope this helps.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

So I tried the =CDate( var ).ToString("dd/MM/yyyy") but since the pStartDate parameter is typed to a Date\Time and your suggestion converts to a string, it errors out.

I populated a string date format on the "Label" side of the "Available Values" and maintained the DateTime type for the "Value" side and the "Default Value" is assigned the Datetime typed value and it's working.

To address the "Setting the Start & End Date parameters to blank when the "Custom" option is selected."
I still having issues. Is there a way to set the "Available Values" of a parameter to "None" using an expression?

Answer 3

Do you know of any other way to use an expression in the Available Values for a DateTime parameter type to see the Calendar control and not the dropdown? If I pass to the Available Values a "Nothing" when the Custom date option is selected it wont allow an entry because it's not one of the Available Values.

Desired:

Instead of:

Answer 4

Yes, that's closer. You have the read the entire question as when I use a "Nothing" for the "Available Values" of the Date\Time parameter when selecting the "Custom" option I'm not able to enter in any data value because "Nothing" is the only Available value.