Manual Summary

[tiger5000]

I have a Crystal 9 report in which I want to sum some column values. I cannot figure out how to get the summaries, which appear to work fine when placed BELOW the columns they're counting, to work right when placed at the top of the columns.

It's not just a matter of putting the calculated fields on top of the columns, in the group header - I already tried that.

For example, given the following report layout, where I am grouping on groupid:

groupid abc123
date1	product description	type1	type2	type3	qty
010105	somesillydescription	0	0	4	4
010105	somesillydescription	3	0	0	3
010105	somesillydescription	0	2	0	2
010105	somesillydescription	0	0	1	1
010105	somesillydescription	1	0	0	1

groupid xyz456
date1	product description	type1	type2	type3	qty
010105	somesillydescription	1	0	0	4
010105	somesillydescription	0	2	0	3
010105	somesillydescription	3	0	0	2
010105	somesillydescription	0	2	0	1
010105	somesillydescription	1	0	0	1

I would like to have the report look like this:

groupid abc123			4	2	5	11
date1	product description	type1	type2	type3	qty
010105	somesillydescription	0	0	4	4
010105	somesillydescription	3	0	0	3
010105	somesillydescription	0	2	0	2
010105	somesillydescription	0	0	1	1
010105	somesillydescription	1	0	0	1

groupid xyz456			5	4	0	9
date1	product description	type1	type2	type3	qty
010105	somesillydescription	1	0	0	4
010105	somesillydescription	0	2	0	3
010105	somesillydescription	3	0	0	2
010105	somesillydescription	0	2	0	1
010105	somesillydescription	1	0	0	1

I would like to have the numbers above each of the columns be the results of the formula below marked "is_type*". The number above the quantity column correctly auto sums via an inserted Crystal Summary, where I sum the myStoredProc column quantity.


Right now, I have defined the following formulas. All formulas are of type "VB Syntax".

FORMULA: createvars
LOCATED IN: report header

global type1 as number
global type2 as number
global type3 as number
formula = " "

FORMULA: is_type1
LOCATED IN: details, as column "type1", above

whileprintingrecords
global type1 as number
if {myStoredProc.WidgetType} = "type1" then
    type1 = type1 + {myStoredProc.quantity}
    formula = {myStoredProc.quantity}
else
    type1 = type1 + 0
    formula = 0 
end if

FORMULA: is_type2
LOCATED IN: details, as column "type2", above

whileprintingrecords
global mytype2 as number
if {myStoredProc.WidgetType} = "type2" then
    mytype2 = mytype2 + {myStoredProc.quantity}
    formula = {myStoredProc.quantity}
else
    mytype2 = mytype2 + 0 
    formula = 0
end if

FORMULA: is_type3
LOCATED IN: details, as column "type3", above

[tt]
whileprintingrecords
global type3 as number
if {myStoredProc.WidgetType} = "type1" then
    type3 = type3 + {myStoredProc.quantity}
    formula = {myStoredProc.quantity}
else
    type3 = type3 + 0
    formula = 0 
end if
[/tt]

FORMULA: resetrecords
LOCATED IN: group footer

whileprintingrecords
global type1 as number
global type2 as number
global type3 as number
type1 = 0
type2 = 0
type3 = 0
formula = " "

 

[Madawc]

The problem is that the counting is happening when the line is printed, and not before. Note is says 'whileprintingrecords'.

I tend to use summary totals and running totals rather than formulas. Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

I doubt you can get summary totals to do the relatively complex counts you are using. If you have to have those figures at the top of the column, you'd need to use a subreport and count them in the subreport. This will work but be very inefficient.

Madawc Williams (East Anglia, UK)

 

[lbass]

If you use conditional formulas like:

//{@type1}:
if {myStoredProc.WidgetType} = "type1" then
{myStoredProc.quantity}

...you can place them in the detail section and insert summaries on them which can be dragged into the group header. You seem to be aware of this possibility, so I am unclear why you are reluctant to use this approach.

-LB

 

[tiger5000]

lbass-

At some point during my troubleshooting, I probably tried that, yes, but did something else wrong, and so didn't see the solution.


madawc-

Thank you - I'm going to be trying both of what you suggested to see how it comes out.