Using CR 2008
I am trying to develop a report that counts matters that were open at the end of each quarter.
This is a cross tab example as below.
smith 10 15
The financial year runs from 1st May to 30th April of the next year
The quarters would be
Q1 – 1st May- 31st July
Q2 – 1st August – 31st October
Q3 - 1st November to 31st January
Q4 – 1st Feb to April 30th
There is data from the year 1996 onwards
At the moment the formula to get the column headings
if opendate < '1996-08-01 00:00:00.000' and closedate > '1996-08-01 00:00:00.000' then 'Q1 -1996' else
if opendate < '1996-11-01 00:00:00.000' and closedate > '1996-11-01 00:00:00.000' then 'Q2 -1996' else
etc. all the way through to the present quater
As you can imagine this is unwieldy and I have to keep adding new code when the next quarter comes along
Is there a better way of doing this?