SSRS default parameter values in subscription

Question

I have a report that has two required date parameters which the user enters. I want to create a subscription that runs on Friday that pulls for the previous week's Sunday through Saturday period. So for example, for this coming Friday, the subscription would pull for Jan 29 - Feb 4. I've tried =Now(), =Today(), @ExecutionTime and then subtracting the number of days but all I get is errors. Is this possible to do?

I did see this link but I wonder if there's a better way. http://www.sqlservercentral.com/articles/Development/datadrivensubscriptions/2432/

SSRS 2008

Answer 1

My favorite trick to handle this situation is to create an Integer parameter called StartWeek. Prompt of "The week starting:" Available values like:

Value    Label
 -4      =dateadd("d",0-weekday(today)+2+(-4*7),today).ToString("m")
 -3      =dateadd("d",0-weekday(today)+2+(-3*7),today).ToString("m")
 -2      =dateadd("d",0-weekday(today)+2+(-2*7),today).ToString("m")
 -1      Previous Week
  0      Current Week
  1      Last Month
  2      This Month

Default value of -1.

Then in your query:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

DECLARE @CurrentDate DATETIME
SET @CurrentDate = GETDATE()
--SET @CurrentDate = 'October 31, 2011'  -- for debugging

IF ( @StartWeek > 0 ) 
   BEGIN
      SET @StartDate = DATEADD(mm, DATEDIFF(mm, 0, @CurrentDate), 0)
      SET @StartDate = DATEADD(mm, 2 - @StartWeek, @StartDate)
      SET @EndDate = DATEADD(s, -1, DATEADD(mm, 1, @StartDate))
   END
ELSE 
   BEGIN
      SET @StartDate = DATEADD(wk,
                               DATEDIFF(wk, 0, DATEADD(d, -1, @CurrentDate))
                               + @StartWeek, 0)
      SET @EndDate = DATEADD(s, -1, DATEADD(day, 7, @StartDate))
   END

Select
   *
FROM
   MyTable
WHERE
   BeginDate <= @EndDate
   AND FinishDate >= @StartDate

Answer 2

On the report parameter configuration (on development time, not on the subscription creation), add a default value for the parameter. If you do, you will have a check box called "use default value" when creating the subscription

Answer 3

BeginDate

=DateAdd("d", -12, Today())

EndDate

=DateAdd("d", -6, Today())

So for the upcoming Friday 2/10. This would give you a date range of 1/29 - 2/4.

Answer 4

Yes I have done this, see this post https://stackoverflow.com/a/5539615/168703 You can create a dataset that gets date ranges and use it in your report. Then your subscription can use this date range and change on its own dynamically without manual changes every day/week/month/year/etc.

Reposted here as well, this is your most flexible solution:

I'll also share a set of common date functions I use. Just create this as a table valued function:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[udfCommonDates] (@date datetime)
RETURNS @t table (week_start datetime,
                  week_end datetime,
                  lastweek_start datetime,
                  lastweek_end datetime,
                  month_start datetime,
                  month_end datetime,
                  lastmonth_start datetime,
                  lastmonth_end datetime,
                  yesterday_start datetime,
                  yesterday_end datetime,
                  today_start datetime,
                  today_end datetime,
                  thisweek_monday_start datetime,
                  thisweek_monday_end datetime,
                  year_start datetime,
                  year_end datetime,
                  tomorrow_noon datetime,
                  today_noon datetime,
                  date_only datetime)
BEGIN
   INSERT @t
   SELECT
   dbo.get_week_start ( @date ) AS week_start,
   dbo.get_week_end   ( @date ) AS week_end,
   dbo.get_week_start ( DATEADD(d, -7, @date ) ) AS lastweek_start,
   dbo.get_week_end   ( DATEADD(d, -7, @date ) ) AS lastweek_end,
   dbo.get_month_start( @date ) AS month_start,
   dbo.get_month_end  ( @date ) AS month_end,
   dbo.get_month_start ( DATEADD(m,-1, @date) ) AS lastmonth_start,
   dbo.get_month_end  ( DATEADD(m,-1,@date) ) AS lastmonth_end,
   dbo.get_yesterday_start ( @date ) AS yesterday_start,
   dbo.get_yesterday_end ( @date ) AS yesterday_end,
   dbo.get_today_start (@date) AS today_start,
   dbo.get_today_end ( @date ) AS today_end,
   dbo.get_weekday_start(1,@date) AS thisweek_monday_start,
   dbo.get_weekday_end(1,@date) AS thisweek_monday_end,
   dbo.get_year_start(@date) AS year_start,
   dbo.get_year_end(@date) AS year_end,  
   dbo.get_tomorrow_noon(@date) AS TomorrowNoon,
   dbo.get_today_noon(@date) AS TodayNoon,
   dbo.get_date_only(@date) AS DateOnly
RETURN
END

Here are the scalar valued functions for these:

    CREATE FUNCTION [dbo].[get_date_only] (@date datetime)
    RETURNS datetime
    WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
    AS    
    BEGIN
        RETURN dateadd(day, DateDiff(day, 0, GetDate()), 0)
    END
    GO

CREATE FUNCTION [dbo].[get_month_end] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    
BEGIN
   RETURN dateadd(ms, -3, dateadd (m,datediff(m,0,
          dateadd(m,1,@date)),0))
END
GO

CREATE FUNCTION [dbo].[get_month_start] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    
BEGIN
   RETURN dateadd(m,datediff(m,0, @date),0)
   END
GO

CREATE FUNCTION [dbo].[get_today_end] (@today datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    
BEGIN
   return dateadd(ms, -3, datediff(d,0,dateadd(d,1,@today)))
END
GO

CREATE FUNCTION [dbo].[get_today_noon](@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,0, @date),0))
END
GO

CREATE FUNCTION [dbo].[get_today_start] (@today datetime)
RETURNS datetime 
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(day, 0, datediff(d,0,@today))
END
GO

CREATE FUNCTION [dbo].[get_tomorrow_noon](@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,-1, @date),0))
END
GO

CREATE FUNCTION [dbo].[get_week_end] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(yyyy, datepart(yyyy,
      dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0)
    + dateadd(ms, -3,
      dateadd(dy, datepart(dy,
     dateadd(weekday,7-datepart(weekday, @date),@date)),0) )
END
GO

CREATE FUNCTION [dbo].[get_week_start] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(yyyy, datepart(yyyy,
      dateadd(weekday,1-datepart(weekday, @date),@date))-1900, 0)
    + dateadd(dy, datepart(dy,
      dateadd(weekday,1-datepart(weekday, @date),@date))-1,0)
END
GO

CREATE FUNCTION [dbo].[get_weekday_end] (@weekday tinyint,
                                 @date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(yyyy, datepart(yyyy,
      dateadd(weekday,@weekday-
      datepart(weekday, @date),@date))-1900, 0)
    + dateadd(ms, -3,
      dateadd(dy, datepart(dy,
      dateadd(weekday,@weekday-datepart(weekday, @date),
                                        @date)),0) )
END
GO

CREATE FUNCTION [dbo].[get_weekday_start] (@weekday tinyint,
                                   @date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(yyyy, datepart(yyyy,
      dateadd(weekday,@weekday-
      datepart(weekday, @date),@date))-1900, 0)
    + dateadd(dy, datepart(dy,
      dateadd(weekday,@weekday-datepart(weekday, @date),
                                        @date))-1,0)
END
GO

CREATE FUNCTION [dbo].[get_year_end] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
RETURN DATEADD(year, DATEDIFF(year, 0, GetDate())+1, 0)-1
END
GO

CREATE FUNCTION [dbo].[get_year_start] (@date datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   RETURN DATEADD(year,DATEDIFF(year,0, @date),0)
END
GO

CREATE FUNCTION [dbo].[get_yesterday_end] (@today datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   return dateadd(ms, -3, datediff(d,0,@today))
END
GO

CREATE FUNCTION [dbo].[get_yesterday_start] (@today datetime)
RETURNS datetime
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS    BEGIN
   RETURN dateadd(day, -1, datediff(d,0,@today))
END
GO

These were really helpful for me because I used this in reporting services for date parameters. You could simply create a dataset referencing this table function and then use these in the parameters for any datetime within RS.

You could execute this entire table-valued function like so:

SELECT * FROM [MyDB].[dbo].[udfCommonDates] (GetDate())

The result is like so

For Reporting Services Folks

Now I mentioned earlier that I use these for reporting services. Now the RS folks might be thinking but how does this help me as I need a dataset and a dataset can only be based on a Stored Procedure or a direct table. No problem create the following stored procedure:

CREATE PROCEDURE [dbo].[uspCommonDates] AS
begin
   set datefirst 1
   declare @date datetime
   set @date = getdate()
   select * from dbo.udfCommonDates(@date)
end

Now you've got a stored procedure to use as a dataset...Now in reporting services add a new dataset:

Now go to the report parameters section of the report:

Now pick that dataset dsFunctions (or whatever you called it) and then pick any of the value fields from the scalar functions such as:

Now when you run the report it uses the scalars:

Also now in your "Subscription" you will see a "Use Default" checkbox next to the parameter for the date. If you check this checkbox it will automatically use the default value provided by this custom function. It is very very flexible and a very nice solution in reporting services. Here is a screen print of that: