Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formulas and variables and calculations in reports

Status
Not open for further replies.

jollyreaper

Technical User
Jul 25, 2005
105
US
Crystal Developer XI

I'm trying to determine if I'm using the smartest technique to accomplish my task. I have a report that takes option selections by customers and sorts into two separate groups beneath said customer, summing the totals of both sections.

Currently it looks like this:

Customer: Joe Blow (group1)
Structural Selections (group2)
1 $1
2 $1
3 $1
group price: $3

Non-Structural Selections (group2)
4 $1
5 $1
6 $1
8 $0
group price: $3

total Price: $6

Group2 is separated by the option numbers. For this example, 1, 2, and 3 are all structural options. 4, 5, and 6 are non-structural.

What they want to add at the bottom is as follows:
Structural Deposit 20% x $3 = $0.60
Non-Structural Deposit 10 or 20% x $3 = ?

The Structural deposit is fixed. The Non-Structural changes depending on whether a specific option was picked in non-structural -- for the purposes of this example, a selection of 8 means 10% down, 9 means 20% down.

I've got it mostly working now but I have a feeling I'm kludging a few things here and there might be a better way.

Group Price is a calculated field in the bottom of Group 2. It resets after the group changes. It sits in the footer of Group 2.

Total Price is a sum of Group Price and sits in the footer of Group 1.

The value currently stored in Group Price at the end of the report is identical to the Non-Structural price and I can get the Structural Price back by subtracting Group Price from Total Price.

The final question is how to flag non-structural for 10 or 20%. I tried doing just that with a formula but I'm not sure if it's the smartest way. I put a formula in the details for Group 2:

Global Numbervar percentflag;
IF {COPT.OPTNUMBER}=900200 THEN
percentflag:=1
else
if {COPT.OPTNUMBER}=900300 THEN
percentflag:=1
else
percentflag:=0

My thinking (and I could be dead wrong) is that the percentflag variable should now be global and I could reference it in the footer.

if percentflag:=1 then
"x 10% = "
else
"x 20% = "

Crystal complains that it wants a Boolean value here, TRUE or FALSE. Well, I could do that I suppose, but my thinking was to leave it open in case management decided to get cute with adding more downpayment options. 1 would be for 10%, 2 for 20%, 3 for 30%.

If I can only do Boolean, I suppose I could do this but it doesn't seem elegant:
if selection=8 then
percentflag10=true
if selection=9 then
percentflag20=true
etc. etc. etc. Doesn't seem elegant, though.
 
Create a detail level formula like {@deposit}:

if {table.option} in [1 to 3, 8] then .1 * {table.price} else
if {table.option} = 9 then .2 * {table.price} else//etc.

Then you can insert a summary at the group level. To get the total per group, you could create a formula:

sum({table.price},{table.selection}) + sum({@deposit},{table.selection})

...where {table.selection} is the group field with results of structured or non-structured.

-LB
 
Thanks! It's working the ugly way right now but I'll definately work these revisions in. The complexity of reports management requests will only increase in time and I'd better start doing them right.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top