CODEWITHSUNDEEP
visual-studioreporting-servicesvisual-studio-2013ssrs-2012
whitz11
whitz11

Reputation: 229

Expression to retrieve last 6 months

I would like to filter out the last 6 months of data in one of my report tables using an expression.

So for example from the 01/05/2017 to 31/10/2017. It needs to be an automated process so the following month would show 01/06/2017 to 30/11/2017.

Below is how I would do it in management studio, however I do not want the initial dataset to be limited to last 6 months. Hence the need for an expression. I will add that I don't require date parameters in my report

WHERE Date_Completed between 
Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6,  current_timestamp)), 0)  
and 
Dateadd(Month, Datediff(Month, 0, DATEADD(m, 0,  current_timestamp)), 0)

Upvotes: 0

Views: 2311

Answers (1)

Chris Albert
Chris Albert

Reputation: 2507

You can use the expressions below to generate the dates for your 6 month range. The dates are generated based on the day the report is ran.

From Date: =DateAdd(DateInterval.Month, -6, DateAdd(DateInterval.Day,-Day(Today())+1,Today()))

Thru Date: =DateAdd(DateInterval.Day,-Day(Today()),Today())

Use them as filters on your tablix that is displaying the dataset. To add a filter to your tablix go to the tablix properties and click filters. Add two filters using your date field and the expressions above. See the example below for what it should look like.

enter image description here

Upvotes: 1

Related Questions