Summary field for existing formula in Crystal 10

[mabis]

Systems: Crystal reports 10, ODBC to SQL 2000.

Report Premise: Calculate which business clients are new or regained clients for the current year. Do so by checking revenue from the prior year versus the current year. If the client had zero revenue in prior year and greater than zero in current year, the account is new or regained.

Current Report:
Group 1: Account Rep (useridmaster.lastname)
Group 2: Client (account.accountname)
Group 3: Orders (order_header.order_head_order_nbr)
Details: line item detail for the orders

I am able to have each individual account show me the revenue for the account based on the criteria above in “Report Premise.”

This formula is (@ytdnewbusiness):

if Sum ({@priorytdlinetotal}, {account.account_name})=0 and Sum ({@yeartodatelinetotal}, {account.account_name})>0 then Sum ({@yeartodatelinetotal}, {account.account_name}) else 0

Struggle: I am unable to summarize the data for the Account Rep.
(1) Insert summary does not show or allow this field to be summarized.
(2) Running Total Field: I have not been able to figure out a formula that will work doing it as a Running Total Field.

Is there a method to accomplish my goal? As always, I appreciate the support you all offer.

 

[synapsevampire]

We don't know what's in {@priorytdlinetotal}, and the other formula referenced, so you can't really expect this sort of post to give a solution using the formula posted. Please think through your posts, if you reference unknowns, we will ask about them instead of offering solutions.

Generally people will use Running Totals or qualified summaries for this sort of thing.

So assuming that a client can't span reps, the grouping seems OK, so 2 Running Totals with the evaluate->use a formula set to the date periods of this year and the other set to last year, and then reset the values at the client will allow you to create a formula against the running totals of:

if {#MyLastYearTotal} = 0
and
{#MyCurrentYearTotal} > 0 then
"New"
else
"Old"

-k

 

[IdoMillet]

Crystal can't total other totals or formulas that are dependent on totals.

One option is to use a variable and a formula that accumulates values into that variable.

Another option is to create a command as a data source (or a database view) that provides the YTD and prior year totals per customer as raw data for the report.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

If you have a date field like {table.orderdate}, then create a formula like this:

if minimum({table.date},{table.client}) >= date(year(currentdate),1,1) then "New" else
if minimum({table.date},{table.client}) < date(year(currentdate),1,1) and
maximum({table.date},{table.client}) >= date(year(currentdate),1,1) then "Regained" else //

...depends upon your definition for regained, of course.

Once you have identified the New vs. Regained, you can use variables to summarize the revenue amounts per Account rep.

-LB