able to select multiple dates (parameter) in ssrs

Question

Is there a way where in I can select multiple dates and pass it as my parameters for a stored proc for a report in ssrs. selecting allow multiple values for a parameter gives a dropdown list. but can i get a calender control where I can select Multiple dates.

Answer 1

SQL Server Reporting Services, as of version 2008R2, does not have this functionality built in. I haven't looked at 2012, but I'd be surprised if it offered this.

(You can always build your own interface using a ReportViewer control, URL access or another access method to display reports.)

Answer 2

As Jamie stated, you can't really do this. The "best" work around I have come across in my experience is to pass your parameter value(s) as one text string, and use a split function to parse in your WHERE condition in the stored proc.

    USE [YOUR DATABASE]
    GO

    SET ANSI_NULLS ON
    GO
     SET QUOTED_IDENTIFIER ON
    GO

   CREATE FUNCTION [dbo].[split](
      @delimited NVARCHAR(MAX),
      @delimiter NVARCHAR(100)
    ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
    AS
    BEGIN
      DECLARE @xml XML
      SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

      INSERT INTO @t(val)
      SELECT  r.value('.','varchar(MAX)') as item
      FROM  @xml.nodes('/t') as records(r)
      RETURN
      END

Your parameter would be something like this in your stored proc:

   @Parameter VARCHAR(200)

Then your where condition in your stored proc will be something like this

 where  convert(varchar(10), cast([YOURDATE] as date), 101)  IN (select val from dbo.split(@Paramater,','))

I hope this helps!