Quarter to date of fiscal quarter

[ericb123]

using CR11 and sql server, I'm trying to set the date range of my report to be the current quarter to date, but the quarters of my company aren't the standard fiscal quarters (01-03).

What's the best way to handle this? I can create a new table to store the quarters for the company, and then somehow see which quarter the current date is in, then set the date range of the report to be the min & max of the date range?

Any advice as to the best way to store this data, and find what quarter I'm in, and set the date range for the report is greatly appreciated, thanks!

 

[Madawc]

Do a formula field that splits it your way, e.g.

if month({your.date}) in [4, 5, 6] then "Q1"
else
if month({your.date}) in [1, 2, 3] then "Q4"
...

You can also use Datepart to get the day, if you need it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ericb123]

Got it, thanks. Once I have the Quarter, how can I find the first day of it? In other words, once I find which quarter I'm in, how do I find the first day so I can set the date parameter of the report to be fromt he first day of the quarter to the current day?

Thanks again!

 

[lbass]

Use another formula like:

if month(currentdate) in 4 to 6 then
{table.date} in date(2008,4,1) to currentdate else
if month(currentdate) in 7 to 9 then
{table.date} in date(2008,7,1) to currentdate //etc.

-LB

 

[ericb123]

Awesome, thanks again lbass! Last question, is there an easy way to make this more dynamic and not hard code the year?

Thanks again!

 

[lbass]

I would add a fiscal year parameter {?fiscalyr} and then change the formula to (assuming you name your fiscal year by the ending year):

if month(currentdate) in 4 to 6 then
{table.date} in date({?fiscalyr}-1,4,1) to currentdate else
if month(currentdate) in 7 to 9 then
{table.date} in date({?fiscalyr}-1,7,1) to currentdate else
if month(currentdate) in 10 to 12 then
{table.date} in date({?fiscalyr}-1,10,1) to currentdate else
if month(currentdate) in 1 to 3 then
{table.date} in date({?fiscalyr},1,1) to currentdate

This assumes your fiscal year runs 4/1 to 3/31.

-LB