T-SQL stored procedure date variable

1
votes

I have this T-SQL stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.datealter 
    @LastChangeDate AS date 
AS
BEGIN
    SELECT 
        s.[DivNo]as Division,
        SUM(CASE WHEN s.Date = @LastChangeDate 
                   THEN s.SELLINC ELSE 0 END) ActualSales
    FROM 
        [dbo].[NewLayerReports] s 
    WHERE 
        s.BRN = 1
    GROUP BY 
        s.[DivNo]
END
GO

When I execute this it works ok.

I am using this stored procedure in a SSRS report. When I execute the SSRS report, it asks me to enter or pick the date.

Here always I wanted a calendar to show last year current month. For example, If I run the report on November 2016 the calendar should show Nov 2015.

How can we do this please?

In SSRS parameter, if I set default value then, I couldn't edit the date value. Please see the picture,

enter image description here

2
sql-servertsqldatetimestored-proceduresreporting-services
So you want the default value of the Last Change Date parameter to be this day but in the previous year? – iamdave
@iamdave No, I want user to pick the date. So date will vary. – user2331670
The date picker will initially show the default value and then any date the user picks. You cannot have the user pick one date and then display another whilst still using the date picker. If you just want to display the relative date, see my edited answer below. – iamdave

2 Answers

3
votes

You can give default value to your parameter. Give expression in your default value for parameter as Date.Now.Date.AddYears(-1). It will show same month in previous year when you run the report.

2
votes

If you want to set the default value of a parameter in SSRS, right click on the Parameter and select Properties. In the Default Value screen you can set the expression that returns the date.

To get this day last year, you can use: =today.AddYears(-1)

Though if you want the start of the month you also need to add the days as well: =today.AddYears(-1).AddDays(1-today.day())

If appears you just want to display the date relative to the one the user picked. In that case you can use the above expressions as required within a report element (such as a title at the top of the report), but replace any instance of today with Parameters!ParameterName.Value.