Setting Parameter Default value to a Date/Time

Question

I have an SSRS report built in Visual Studio 2017. There is a start and end date. The start being -1 week from todays date at 6am, and the end date being todays date at 5:59am.

I'd normally set these values in SQL, but someones requested the ability to select the date range using parameter drop downs as well.

I opened up the parameter in VS, went to default values and then not knowing Visual Basic didn't know the next step.

Solution 1

In the "Default Values" tab of the Report Parameter Properties like you've already opened

• Click on "Specify Values"
• Click on the Expressions button (fx)

• enter the following for the Start date

  =CDate(Format(DateAdd("d",-7,Now()), "yyyy-MM-dd") + " 06:00:00")

• enter the following for the End date

  =CDate(Format(Now, "yyyy-MM-dd") + " 05:59:59")

Output like so

Solution 2

As with any question you pose on SO there are a number possible solutions, I'll share what has worked for myself and my BI team.

We allow report users to select from a list a number of different date ranges (This Month, Last Month, Yesterday etc) which populates @StartDate and @EndDate which are both hidden parameters. The @DateRange is defaulted to Last Month but could be set to anything.

To accomplish this I've created two stored parameters in SQL, named Reporting.usp_StartDate and reporting.usp_EndDate but ofcourse call them what you will.

Reporting.usp_StartDate

SELECT 
COALESCE(
    CASE WHEN @TimePeriod = 'Current Month' THEN DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) ELSE NULL END,
    CASE WHEN @TimePeriod = 'Previous Month' THEN DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-1,0) ELSE NULL END,
    CASE WHEN @TimePeriod = 'Previous 7 Days' THEN DATEADD(HOUR,6,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE())-1,0)) ELSE NULL END,
    DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0) 
        ) [StartDate];

Reporting.usp_EndDate

SELECT 
COALESCE(
    CASE WHEN @TimePeriod = 'Current Month' THEN DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0) ELSE NULL END,        
    CASE WHEN @TimePeriod = 'Previous Month' THEN DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) ELSE NULL END,
    CASE WHEN @TimePeriod = 'Previous 7 Days' THEN DATEADD(HOUR,6,DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)) ELSE NULL END,
    GETDATE()           
       ) [EndDate];

Now in your VS2017 project, open up your .RDL.

• Add two new datasets selecting 'Use a dataset embedded in my report' from the radio buttons. We call these datasets GetStartDate and GetEndDate but this is up to you.
• Select your datasource either by creating a new connection or using a shared datasource from your solution.
• Select Stored Procedure
• Find and select the above stored procedures. For my team it would be GetStartDate uses Reporting.usp_StartDate and GetEndDate uses Reporting.usp_EndDate.
• If you are using the above templates then a new @TimePeriod parameter will be created but you may already have this.
• Create two new parameters, StartDate and EndDate, again you likely already have these if you're using a pre-existing report template, create these with the data type of 'Date/Time'.
• On both the StartDate and EndDate parameters, using the 'Default Values' tab, from the radio button select 'Get Values from a Query', Select the relevant Dataset (@StartDate uses GetStartDate dataset) and the Value Field will be StartDate (there should only be one entry).
• On your @TimePeriod parameter, you can add a load of 'Available Parameters' basically add anything you want the user to be able to select
• You can also set a 'Default Value' for the @TimePeriod, in your case I would add the 'Previous 7 Days' options, which I've included in the parameters above.
• Lastly, I would set both the @StartDate and @EndDate as 'Hidden' under the 'Select parameter visibility' options, this is optional, however.

This pretty much sets up the report, you need to make sure that the Datasets that retrieve your data expect to receive @StartDate and @EndDate in WHERE clauses or JOINs, you said you were happy to set values in SQL so I assume you can handle this.