Scheduling SSRS with Dynamic Dates

You can schedule SSRS Reports, via Subscriptions, but the issue is that you can easily set custom parameters except the date.

This became a problem for me because:

  • I want the users to pick a Start Date and End Date when they open the report. I don’t want it to default to anything because it minimises the reusability
  • I want to schedule the reports to automatically generate for the previous month. SSRS doesn’t allow me to do that from the My Subscriptions page

From the SSRS Home Page, look at the upper-right corner and click on My Subscriptions. This will open up your scheduled reports. In the screenshot below, you’ll notice that you can choose from a list: Any list, that is, including dynamically-loaded ones. This limitation forces you to type in a static date, which defeats the purpose of scheduling if you want your users to always pick a date when they run the same report themselves.

image

Here’s how I went around the limitation:

Steps

  • Create the Dynamic Dates: I created date parameters that will hold the actual date and time used by the report. Make sure they’re hidden, so the user won’t see them.

image

  • Create the parameter that will tell the report whether to use the Dates entered by the user, or the one that we want to override it with. It will make more sense on the next bullet point.

image

Make sure it’s an integer and that it’s hidden, so the user won’t see it.

image

Set the default value to 0

image

  • Create a new Shared Dataset with this Query. In my project, i named it dsDateRange

  DECLARE @mStartDate DATETIME = @StartDate
  DECLARE @mEndDate DATETIME = @EndDate

  if @DateRange = 1
  BEGIN
    —
    — Last Month
    —

    SET @mStartDate = DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0))
    SET @mEndDate = DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
  END
  SELECT @mStartDate AS StartDate, @mEndDate AS EndDate

We set the default value of @mStartDate and @mEndDate to what the users selected by default. Now, let’s look at @DateRange: If the date range is 0, then do nothing. If the @DateRange is 1, then we set @mStartDate and @mEndDate for the first and last date of last month respectively. Now, you can do whatever you want with what the @DateRange corresponds with.

  • Set @mStartDate and @mEndDate with the values from the Dataset.

image

@mStartDate and mEndDate will always get a value depending on @DateRange. If it is opened by the user, it will all be invisible to them and it will have a default value of 0.

Looking at Subscriptions

Now, if you’ll go to My Subscriptions, you will find that you have the Date Range, mStartDate and mEndDate. Set Date Range to 1, indicating that we want to use our custom Data Set code. Also set mStartDate and mEndDate to Default.

image

Now you’re good to go.

  • Use @mStartDate in your Datasets and text fields instead of @StartDate.

image

Optional

You can make scheduling prettier by instead of making the Date Range an integer, make it a selection from a list. Make another Dataset. This will consist of a Value and a Label.

image

Keep the @DateRange an integer, but on the Available Values, choose the Dataset that we just created.

image

Advertisements