x

Using the date picker in SSRS

I have a report that requires a start and end date and I've set these as dates and on initial load the date picker is displayed. However I have added criteria so that the end date starts after the start date and is less than today's date and as soon as it does this the date picker disappears and a list box replaces it.

Is this expected behaviour, which I'm expecting to be the answer, or is there any way at all that I can have filtering on a date and keep the date picker?

Below is the image showing before and after: alt text

datepicker.png (71.3 kB)
datepicker.png (71.3 kB)
more ▼

asked Aug 05, 2014 at 09:26 AM in Default

avatar image

Mrs_Fatherjack
5.2k 65 68 77

I do not understand your question. The date picker does not go away if the SSRS Report Parameter is correctly setup as Date data type. If you give it a default value, you will get the specified date preloaded in the field, but it does not "turn" into a single-select parameter (i,e, you can still pick a date and change it).

Aug 05, 2014 at 05:37 PM virtualjosh

I have added a screen dump to show the before and after in the question above.

Aug 06, 2014 at 08:58 AM Mrs_Fatherjack
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Interesting..... In doing a test, SSRS does perform as you describe.

What you are evidently doing is giving End_Date its "Available values" using "Get values from a query". Using a dataset to assign values to a filter, you must agree, is a typical practice of single-select and multi-select filters. By specifying values, you are telling SSRS some date values are legal, while others are illegal. The only way it can forbid illegal values be entered by the user is by taking away the anything-your-heart-desires-to-choose date picker. The behavior makes sense.

Furthermore, you are using Start_date as an input parameter to your End_Date dataset. So you have a cascading filter. Notice, even if your filters weren't linked to each other, you'd still get the turn. What I am pointing out here is, the reason your date picker is "turning" into a single-select has nothing to do with the fact that the filters are linked. The reason it is turning is because you are using "Get values from a query". The date picker will still turn if the filters weren't linked.

How can you avoid this?

Three things you can do....

One - Get rid of the validation and get rid of "Get values from a Query". If the user gives you an illegal range, give them a report with no data.

or

Two - Get rid of "Get values from a query" and take your validation to the main PROC as opposed to the parameter dataset. That is, on your main proc, choose which is the your start_date and which is your end_date. Something along the lines of:

 DECLARE @SSRS_End_date    DATE = '2014-06-01'  -- From SSRS Report Parameter
       , @SSRS_Start_date  DATE = GETDATE()     -- From SSRS Report Parameter
       , @Start_date       DATE = null          -- Internal Validated Range Start 
       , @End_date         DATE = null          -- Internal Validated Range End 
       ;
 
 
 -- Pick/Validate date range
 WITH cte AS
 (    SELECT @SSRS_Start_date AS [d]
     UNION ALL
     SELECT @SSRS_End_date
 )
 SELECT 
       @Start_date = MIN([d])
     , @End_date   = MAX([d])
 FROM cte;
 
 -- Feedback
 PRINT @Start_date;
 PRINT @End_date

or

Three - Leave it as it is. Validate on the End_Date dataset and have the date picker turn to a single-select. I would only suggest testing how the report behaves if the user selects the End_date first.

more ▼

answered Aug 06, 2014 at 02:33 PM

avatar image

virtualjosh
311 5 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x690
x2

asked: Aug 05, 2014 at 09:26 AM

Seen: 4076 times

Last Updated: Aug 06, 2014 at 03:16 PM

Copyright 2017 Redgate Software. Privacy Policy