Date Time Expression - SSRS‏

 

Last week I received my friend's email, After discussion in the MSN, send to me mail,

 

What About Date Time Experssion - SSRS

 

As  following:

 

In our application year is considered to be divided into 4 quarters. Jan-March, April-Jun, Jul-Sep, Oct-Dec.

If I have selected my time range to be ‘Last Quarter’ and Current date is 2010/07/26. I should be able to get Start Date and End Date as 2010/04/01 and 2010/06/30 respectively. I wrote the following expressions to achieve this.

 

Last Quarter:

Start Date:  =cdate(year(dateadd("q",-1,today())) & "/" & month(dateadd("q",-1,today()))  & "/1"),

End Date:    =dateadd("d", -1, cdate(year(dateadd("q",-0,today())) & "/" & month(dateadd("q",-0,today()))  & "/1"))

 

If my current date is July it displays the start and end dates properly. However if my system date is in August it shows 2010/05/01 and 2010/07/31. This is because in RS the quarter stands for 3 months and it takes last 3 months dates.

 

How do I customize the date expression to consider the quarter separation as per our application requirement.

 

-Rashmi

 

 

My reply

 

This is a SSRS function of the problem, so you can Design your Expression,Reference this.

 

Last Quarter:
Start Date:  =Today().AddDays(1 - Today().Day).AddMonths(-(((Today().Month - 1) Mod 3) + 3))
End Date:  =Today().AddMonths(-(((Today().Month - 1) Mod 3) + 3)).AddDays(-Today().Day).AddMonths(3)

I test it, is ok for SSRS.

 

p.s. My Test environment

 

1、Windows Server 2008 R2

2、Sql Server 2008 R2