0

I have a parameter in SSRS where the user can select whether or not a provider is Terminated. If they select Yes for Terminated then I want them to be able to select a Termination date range. If they select No, then I want the date range to be greyed out. I found an answer for this elsewhere on the site, but I can't get it to work. I got confused by what they meant by create a dataset based on the SpecifyDate dataset. I tried to replicate this by creating a Terminated dataset, but I don't think I was doing this correctly. This is what I found: possible solution

enter image description here

The Terminated parameter was created with this: enter image description here

The main dataset has a where filter:

    where status = @terminated
aduguid
  • 3,099
  • 6
  • 18
  • 37
jackstraw22
  • 517
  • 10
  • 30
  • What you are attempting to setup a cascading parameter. I bet you need to pull your "Default Value" for the low and high date parameters from the result of a SP that takes the Paramaters!Terminates.Value as a @Parameter of the dataset. This binds the cascading link (returning false dates when not needed). You also may have to specify the Always refresh to reset the chain. Here is another link for the topic. --> https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0f88137a-896f-4f4e-8eb4-6a9fd1f9a42b/ssrs-cascading-parameters-and-date-parameters?forum=sqlreportingservices – Ross Bush Dec 19 '17 at 17:37
  • Also, if I recall correctly, the order of parameters from top to bottom in the parameter listing matters. The Always Refresh advanced option will re-fetch the values for your dependent parameter when the linked value changes, i.e @terminated. This will force your date values to change from an enabled/disabled state based on nulls or having value. – Ross Bush Dec 19 '17 at 17:44
  • I understand what you're saying about needing a cascading parameter, but I'm having a hell of a time trying to get this to work. – jackstraw22 Dec 19 '17 at 18:37

1 Answers1

0

You cannot "Grey out" parameters in SSRS. What you can do is set it to NULL. Your query needs to be able to handle NULL dates if Terminated parameter is set to "No".

So what I would do is, create a dataset called Date which selects desired dates based on the Terminated parameter..

The date dataset might look something like this: ( comment out the declare statement when implementing)-- I'm setting an arbitrary value for the termination dates 

declare @teminated varchar(3) = 'Yes'
select
 case when @teminated = 'No' then getdate() else NULL end as start_date
, case when @teminated = 'No' then getdate() else NULL end as end_date

You would then set the default valued for the Termination dates form the dates generated in this dataset.. Hope that made sense.

Harry
  • 2,636
  • 1
  • 17
  • 29
  • I tried something like this, but what ends up happening is you have to set the parameter in SSRS to accept NULLs. When you do this it gives a default where the date parameter is null with checkboxes for the user to check or uncheck. This works fine when the user wants to run the report where Terminated = "Y." However, if they want to run it where Terminated = "N' they will have to manually uncheck the NULL box and then enter the date. As simple as this sounds, I was told this option was too user unfriendly. – jackstraw22 Dec 20 '17 at 21:42
  • @jackstraw22 SSRS parameters are pretty hard to work with.. in the sense.. you can't customize them much .. only in 2016 you can move the location of parameters.. being able to hide them conditionally would have been fantastic.. but I'm guessing you are out of luck here – Harry Dec 21 '17 at 19:25