Writing a formula using particular values 1

[LHG33]

Hi,

I have fields ExpenseID and Amount where a certain ExpenseID corresponds with a certain amount. I would like to sum the amounts that are only for certain ExpenseID's (for example, 13-15). I am using version 7. Any help is appreciated.

 

[mbarron]

You can accomplish this using a running total.

Field to Summarize would be your amount.
Type of summary would be. Sum

Evaluate:
Use a formula.
formula to use if ExpenseID is a number and you want 13,14, and 15: ExpenseID in[13 to 15]

Reset:
Whenever you want.


Mike

 

[dgillz]

mbarron's example will work but it will show ALL expense IDs regardless of the expenseID number on the report, and only summarize the ones you mentioned (13 to 15).

To get only 13 to 15 on your report and summarized, dump the running total and just use a total, then in the record selction formula use the following: ExpenseID in 13 to 15

Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[LHG33]

Thank you both for your help. My report is finished! :-D

 

[MagicGirl]

Hello,

I have a similar issue; I would like to write a formula (CR 7) which displays a value from a record with a specific value in another field (a specific year's sales total). The above examples don't appear to work for me, unless I am applying them incorrectly.

Example table:

Customer ID Year Yearly Sales
---------------------------------

1234 1999 $20000
1234 2000 $34000
2345 1999 $50000
2345 2000 $45000
2345 2001 $65000
3456 1999 $32000
3456 2000 $87000

Expected report output:

Customer ID {?year} {?year}-1 Projection (etc)
--------------------------------------------------------
1234 (yearly sales) (yearly sales*)
2345 (yearly sales) (yearly sales*)
3456 (yearly sales) (yearly sales*)


The current report displays yearly totals based on parameter {?year}, which is no problem. I am having difficulty with a formula (or other solution) for the previous year's total, which is the field with the asterisks. Have tried grouping and crosstab, and these solutions do not provide the desired formatting. Thanks so much in advance!

Susan

 

[Ngolem]

Group on Customer ID
I'll assume {table.salesyear} & {?Year} are numbers

@initialize ( place this in the Cust header)
whileprintingrecords;
numbervar ThisYearSales := 0;
numbervar LastYearSales := 0;


@calcSales ( place this in the detail line)
whileprintingrecords;
numbervar ThisYearSales;
numbervar LastYearSales;

if {table.salesyear} = {?Year} then
ThisYearSales := ThisYearSales + {Table.SalesNumber};
if {table.salesyear} = {?Year}- 1 then
LastYearSales := LastYearSales + {Table.SalesNumber};

@display - ThisYearSales ( place this in the footer line)
whileprintingrecords;
numbervar ThisYearSales;
ThisYearSales;

@display - LastYearSales ( place this in the footer line)
whileprintingrecords;
numbervar LastYearSales;
LastYearSales;

the way this is written You could have other groups such as Products and you would get the yearly total for all products by customer ID

Put all your display data on the customer ID footer and suppress the Detail section

 

[MagicGirl]

This appears to do the trick! They are currency fields, but it makes no difference. Appreciate the help! B-) Two questions -

The WhilePrintingRecords evaluation time: Is this because of the grouping, which I am surmising is designed to hide that behind-the-scenes calculation of @calcSales?

The placement of the @initialize formula - is this in the header because it gets evaluated first?

Thank you very much!

Susan

 

[Ngolem]

I always use "WhilePrintingrecords" when displaying or calculating displayed values...this forces Crystal to perform the calculations/display when YOU want them done.

When using a formula for grouping purposes you don't use WhilePrintingrecords since Crystal uses this formula when ordering the data from the query (obviously this isn't during printing

The placement of the @initialize formula or any initialization formula depends on when/how often the calculation is repeated.

If done only once then yes...it would go into the header. But if you want totals based on some group (eg. Cust ID) then put it in the Group header for Cust ID

one trap to avoid is when the group header is repeated on a new page....you have to be careful of this since it will cause your totals to be reset!!

A more general @Initialization formula would be

whileprintingrecords;
if not inrepeatedgroupheader then
(
numbervar ThisYearSales := 0;
numbervar LastYearSales := 0;
);


now you are safe

Glad it worked out...this approach comes up often...

Jim

 

[MagicGirl]

Most helpful - thanks again! B-)

Susan