CODEWITHSUNDEEP
sqlreporting-services
NewbieSQL_Germany
NewbieSQL_Germany

Reputation: 85

SSRS- Previous Month in Parameter

I have the following SQL query for my month parameter:

WITH Kalender 
AS (SELECT DateAdd(m,-4,DateAdd(d,1-DAY(DateAdd(d,-1,Convert(date,GETDATE()))),DateAdd(d,-1,Convert(date,GETDATE())))) AS [Datum] 
    UNION ALL 
    SELECT DATEADD(m, 1, [Datum]) 
    FROM Kalender 
    WHERE DATEADD(m, 1, [Datum] ) <= DateAdd(d,-1,Convert(date,GETDATE()))
   )
   
Select Monat = convert(varchar(4),Year([Datum])) + right('00' + convert(varchar(2),Month([Datum])),2)
FROM Kalender
ORDER BY Monat desc

Now when someone opens the report in september, i want the report to show them 202108 instead of 202109 as a default value. Is this possible with an expression in the parameter - default value itself?

Thanks in advance.

Upvotes: 0

Views: 180

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

Assuming you want to return an integer 202108 then you could do something like this..

=
(YEAR(DateAdd(DateInterval.Month, -1, Today())) * 100)
+MONTH(DateAdd(DateInterval.Month, -1, Today()))

Wrap this in a CStr() is you need it as a string.

Upvotes: 1

Related Questions