question

ChuckSalerno-4276 avatar image
0 Votes"
ChuckSalerno-4276 asked ChuckSalerno-4276 answered

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

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.

106228-dateoption.jpg

106246-dateoption1.jpg

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.


sql-server-reporting-servicesdotnet-visual-basic
dateoption.jpg (16.5 KiB)
dateoption1.jpg (38.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered

Hi @ChuckSalerno-4276 ,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChuckSalerno-4276 avatar image
0 Votes"
ChuckSalerno-4276 answered Joyzhao-MSFT commented

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?

106648-dateoption2.jpg





dateoption2.jpg (44.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Is there a way to set the "Available Values" of a parameter to "None" using an expression?

I think this cannot be achieved.


0 Votes 0 ·
ChuckSalerno-4276 avatar image
0 Votes"
ChuckSalerno-4276 answered Joyzhao-MSFT commented

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:
108589-desired.jpg

Instead of:
108693-notdesired.jpg



desired.jpg (11.7 KiB)
notdesired.jpg (14.7 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If you want calendar control instead of drop-down list, you could use DateTime datatype for parameters.

  • Right click your parameter, go to properties.

  • In General tab, go to data-type and select DateTime.

  • Your Available Values should be set to None.

  • In default values, if you want to set current date then you can use,
    =Today
    or set to None.

  • Click OK.

0 Votes 0 ·

II believe your not grasping the issue. It's almost like you're not even reading.

II'm fully aware of how to set the parameter as a DateTime.

0 Votes 0 ·

Sorry for misunderstanding what you mean. Do you mean that you need the date parameter value to be presented in the form of a calendar instead of a drop-down list?
112075-03.jpg
If this is the case, you need to make sure the Data type is Date/time and uncheck "Allow multiple values"
If I misunderstand what you mean, please feel free to correct me.

0 Votes 0 ·
03.jpg (18.0 KiB)
ChuckSalerno-4276 avatar image
0 Votes"
ChuckSalerno-4276 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.