How do I SumIf in a crosstab report??????

[marksimm]

I am using Crystal 7. I have a crosstab report that has customer and year in the row area, a formula which translates a period number(1,2,etc..) to a label(Jul,Aug,etc..) in the column area, and the sales quantity in the summarized field. The report summarizes all months in the Total column. I need to build it so it sums only sales for ... say Jan through Sep. This can allow me to compare Jan - Sep 2000 totals with Jan - Sep 2001 totals, and still display Oct - Dec 2000 month figures. How can I do this????

 

[Jacque]

I'm not sure if this can be done in V7, but what if you create a manual crosstab.

1. Group by your row field(s).
2. In the details section place the row field(s).
3. To display the data, add a formula field for each column (in the details section). Note: Do a check to see if the field value is applicable to the column heading (Year, month, whatever), either display the field value or zero if null or not applicable.
3a. Add a formula field (sumif or whatever) to summarize the specific time period you are interested in as field in the details section as well. This column will allow you to do your comparison.
4. Add summary fields in the group footer, be aware of which group footer, for your info, this will allow you to summarize the formulas displayed in the details section.
5. Suppress the detail and group header(s) sections.
Your group footer should appear as a row on the crosstab.
6. Add text objects in the page header for your column headers.
7. Add your lines to make it look like a typical crosstab and grand total in the report footer if required.

 

[kenhamady]

There is another description in my FAQ on common formulas in the General forum. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/