Visual Basic Crystal Reports - Beginner - Summarizing formula fields that already have summations

Author

Beginner - Summarizing formula fields that already have summations

alexmoquia@gmail.com

2006-03-28, 7:05 pm

Hi all,

Alright first off, I know I can not summarize any fields which already
have summations. I was just wondering the simplest workaround to this
problem?

I am working with a financial record db that has 'salesOrder' records
which are associated with several 'workItem' records. (a salesOrder can
have several workItems).

Each 'salesOrder' has a total balance which goes towards the cost of
each 'workItem' associated with it. If a 'salesOrder.total' is $10,000
and it has two 'workItem' records that cost $2,000 each, then the
'residual amount' of the 'salesOrder.total' (which i plan on
calculating, and not keeping as a database field) is $6,000.

The report I am working on needs to summarize the 'residual amount's of
each 'salesOrder', in several different grouping levels as well as in a
report total.

//'residual amount' formula field:
{salesOrder.total} - Sum ({workItem.cost}, {salesOrder.ordernum});
//This sums up the costs of all workItems associated with one
salesOrder and subtracts it from the total balance, to get what is left
over.

Now I want to do a summation for this field for all salesOrders
selected in the report.

I have tried doing this manually through sum formula fields, except the
formula I worked out is innacurate due to the fact that record
selection is getting done on the 'workItem' level.
So this formula:

//Report Footer sumfield
Sum({salesOrder.total}) - Sum({workItem.cost})

Provides an overprojected sum, because 'workItems' associated with the
same 'salesOrder' are summing the same salesOrder total more than once.

>From endless googling, I've come to the conclusion my solution lies in
either global variables and/or subreporting?

In experimenting with global variables, I found that CR prints the last
expression in your formula. In terms of variable passing/manipulation
between formula fields, this causes a problem, because I want to print
the value of a variable first, and then change the value for the next
recurrence of the field. Is there anyway to force CR to print certain
expressions and only evaluate others? Does the answer lie in
EvaluationTime controls?

I'm a complete beginner to Crystal Reports but have gotten thrown into
the fire. If anyone can shed some light towards the simplest solution,
it would be greatly appreciated. Thanks!

Iain Sharp

2006-03-29, 4:12 am

On 28 Mar 2006 08:33:25 -0800, alexmoquia@gmail.com wrote:

>Hi all,
>
>Alright first off, I know I can not summarize any fields which already
>have summations. I was just wondering the simplest workaround to this
>problem?
>
>I am working with a financial record db that has 'salesOrder' records
>which are associated with several 'workItem' records. (a salesOrder can
>have several workItems).
>
>Each 'salesOrder' has a total balance which goes towards the cost of
>each 'workItem' associated with it. If a 'salesOrder.total' is $10,000
>and it has two 'workItem' records that cost $2,000 each, then the
>'residual amount' of the 'salesOrder.total' (which i plan on
>calculating, and not keeping as a database field) is $6,000.
>
>The report I am working on needs to summarize the 'residual amount's of
>each 'salesOrder', in several different grouping levels as well as in a
>report total.
>
>
>//'residual amount' formula field:
>{salesOrder.total} - Sum ({workItem.cost}, {salesOrder.ordernum});
>//This sums up the costs of all workItems associated with one
>salesOrder and subtracts it from the total balance, to get what is left
>over.
>
>Now I want to do a summation for this field for all salesOrders
>selected in the report.
>
>I have tried doing this manually through sum formula fields, except the
>formula I worked out is innacurate due to the fact that record
>selection is getting done on the 'workItem' level.
>So this formula:
>
>//Report Footer sumfield
>Sum({salesOrder.total}) - Sum({workItem.cost})
>
>Provides an overprojected sum, because 'workItems' associated with the
>same 'salesOrder' are summing the same salesOrder total more than once.
>
>either global variables and/or subreporting?
>
>In experimenting with global variables, I found that CR prints the last
>expression in your formula. In terms of variable passing/manipulation
>between formula fields, this causes a problem, because I want to print
>the value of a variable first, and then change the value for the next
>recurrence of the field. Is there anyway to force CR to print certain
>expressions and only evaluate others? Does the answer lie in
>EvaluationTime controls?
>
>I'm a complete beginner to Crystal Reports but have gotten thrown into
>the fire. If anyone can shed some light towards the simplest solution,
>it would be greatly appreciated. Thanks!

What you're looking for is a runing total. Sum the workitems as
normal. Create a running total for the sales order, set to sum the
sales order value, evaluated on change of sales order group, reset
whenever.

The formula for the 'residual' is then this running total less
sum({workitem.cost}), paint it in the right place and there you go.

Iain

alexmoquia@gmail.com

2006-03-29, 7:06 pm

Thanks alot. Worked great and that was pretty simple.

Sponsored Links