Sales By Month Group by Qtr 1

[Bennie47250]

Using Crystal 7.0 with a MS SQL 7.0 database

I have this working but it is going to take quite a few different formulas. Wondering if there is a better way.

The report needs to shows sales for each product family for each month for the current year and the previous year for a specific quarter

Here is an example for the First Quarter sales

Family: Blue Shoes
Current Year Prev Year
Month Qty Sales Qty Sales
1 50 2500 36 2300
2 70 3050 85 3200
3 40 2000 50 2800
Total 160 7550 171 7800
Avg Sales $ 47.19 45.61

Family: Red Shoes
Current Year Prev Year
Month Qty Sales Qty Sales
1 50 2500 36 2300
2 70 3050 85 3200
3 40 2000 50 2800
Total 160 7550 171 7800
Avg Sales $ 47.19 45.61

The way I’m doing this now:

When the user runs the report they are prompted for the Quarter and the Current Year
The report has 2 groups.

Group 1 = Family
Group 2 = Month

In Group 2 (Month) I creating multiple running totals.

The field to summarize is the Qty and Total Sales.

In Evaluate, I using a formula to determine the year and Month, it is

{Table.Year} = {?Year}
and
if {?Quarter} = 1 then { Table.Month) in [1]
else if {?Quarter} = 2 then { Table.Month} in [4]
else if {?Quarter} = 3 then { Table.Month} in [7]
else if {?Quarter} = 4 then { Table.Month} in [10]

In the Reset, I’m resetting on Group 2

The next running total is the same except that the evaluate sections is

{Table.Year} = {?Year}
and
if {?Quarter} = 1 then { Table.Month) in [2]
else if {?Quarter} = 2 then { Table.Month} in [5]
else if {?Quarter} = 3 then { Table.Month} in [8]
else if {?Quarter} = 4 then { Table.Month} in [11]

Doing it this way, I have to have 3 running totals for each Qty and Sales to determine the Qty and Sales.

Would appreciate any tips on how to better design this report using less formulas

 

[BobSuruncle]

I assume (I know, I know) that your record selection is something like this:

{Table.Year} in ({?Year}-1  to {?Year} )
and 
{Table.Month} in
(
if {?Quarter} = 1 then 1 to 3
else if {?Quarter} = 2 then 4 to 6
else if {?Quarter} = 3 then 7 to 9
else if {?Quarter} = 4 then 10 to 12
)

The second part of the selection formula won't be passed in the WHERE clause of your SQL statment. (if you could get to at least v8, performance would be better. Maybe someone else has a better solution for v7)

You could create one formula for this year's Qty:

If {Table.Year} = {?Year} then {Table.Qty}

This would go in the Details section and be subtotaled for each group.
The Details section would be Hidden or Suppressed, and the values shown in the footers.

3 more formulas, for This Year's Sales, Last Year's Qty and Last Year's Sales could be built in the same fashion.

I don't have access to Crystal at the moment, but unless I've had a brain cramp or have misread your post, this should work for you.




Bob Suruncle