crosstab grouped by rolling 12 month quarters

[oj75]

how do I go about creating a crosstab report like the following:
Type and Number of Infection
Quarter UTI URI GI Skin
January 2003 -March 2003 3 44 2 0
October 2002 - December 2002 10 2 1 4
July 2002 - september 2002 11 3 4 1
April 2002 - June 2002 3 2 4 0

I would like to take report date parameter and set the quarter groupings to start with the last completed quarter previous to the report date parameter. So if a user entered any date within the April 2003 - June 2003 quarter then the above report would be created and so on with the rolling 12 month period. Each one of these infection records has a date the infection occurred. Does the crosstab handle this summarizing by infection date easily or do I need to create a formula to handle the infection type totals by quarter.

Any info. is appreciated.

oj

 

[dgillz]

Based on the datefield, create a formula:

If {YourDateField} in Date(2003,1,1) to date(2003,3,31) then "Jan - Mar 2003" else.....

Make as many else statements as needed for your report. Then place this formula field as your row field in the crosstab.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[oj75]

I don't want to hardcode the year portion of the quarter grouping. as the report will not be applicable to 2003 by the end of next year.

Any other suggestions.

oj

 

[dgillz]

Yeah, try this formula:

If Month({YourDateField}) in 1 to 3 then "Jan - Mar " else
If Month({YourDateField}) in 4 to 6 then "Apr - Jun " else
If Month({YourDateField}) in 7 to 9 then "Jul - Sep " else
If Month({YourDateField}) in 10 to 12 then "Oct - Dec "
&
Totext(Year({YourDateField}),0,"&quot

Make this the row in your crosstab.



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[oj75]

Problem is that all the quarters in the report may not fall in the same year as the report date parameter entered by the user as shown in my example. Also, the report should start with the quarter previous to the quarter the report date parameter falls in and go back a year.

 

[dgillz]

I do not see how overlapping a year can be a problem. The formula I posted will still convert is to the proper text you want. If this is a problem, please explain how it si a problem.

In regards to the parameter, write a record selection formula to only pick the dates you want.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com