SSRS – Calculate an Upcoming Quarter

Here is a quick hack of SQL Server Reporting Services to calculate an upcoming quarter (for the parameter fields).  It will do this for a 30 day upcoming range, and then stick to the existing quarter.  I have found this nice for seeing what an upcoming quarter looks like, while maintaining the previous quarter within 2 months.  The 30 days is fully adjustable.

Year:
=IIF((DATEDIFF("d", Now(), DateAdd("d", 0, DateAdd("q", DateDiff("q", ("1/1/1900"), Today)+1, DateSerial(1900,1,1)))) < 30), (DatePart(DateInterval.Year, DateAdd("d", 0, DateAdd("q", DateDiff("q", ("1/1/1900"), Today), DateSerial(1900,1,1))))) , Year(DateAdd("m", -3, DateSerial(Year(Now), 3 * ((Month(Now()) - 1) \ 3 + 1) -2, 1))))
Quarter:
=IIF((DATEDIFF("d", Now(), DateAdd("d", 0, DateAdd("q", DateDiff("q", ("1/1/1900"), Today)+1, DateSerial(1900,1,1)))) < 30), (DatePart(DateInterval.Quarter, DateAdd("d", 0, DateAdd("q", DateDiff("q", ("1/1/1900"), Today), DateSerial(1900,1,1))))), ((Month(DateAdd("m", -3, DateSerial(Year(Now), 3 * ((Month(Now()) - 1) \ 3 + 1) -2, 1))) - 1)\3 + 1))

 

Posted in Reporting.

Leave a Reply

Your email address will not be published. Required fields are marked *