Help with display string formula - division by 0 1

[jeffm777]

Using the following display string formula but it's returning division by 0...

WHILEPRINTINGRECORDS;
NUMBERVAR columnF;
NUMBERVAR columnH;
NUMBERVAR tempX := (columnF/columnH) * 100;
//numbervar tempX := columnQ;
if tempX < 0 then
toText(tempX * -1, 1,"") & "%"
else
totext(tempX,1,"") & "%

 

[dgillz]

You are making this way too hard. Variables are not needed. Try this:

If {ColumnH} = 0 then 0 else
{ColumnF}%{ColumnH}

Then format the field with the % icon.


Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[jeffm777]

Getting "This field name is not known" on {ColumnH} and {ColumnF}. These aren't fields - they reference data in certain columns of the report.

 

[lbass]

jeff--You haven't shown the formulas where the variables for columnH and columnF are set = to certain values. In the display string area, you could correct the zero issue by changing the formula to:

WHILEPRINTINGRECORDS;
NUMBERVAR columnF;
NUMBERVAR columnH;
NUMBERVAR tempX;
if columnH <> 0 then
tempX := columnF%columnH;
//numbervar tempX := columnQ;
if tempX < 0 then
toText(tempX * -1, 1,"") & "%"
else
totext(tempX,1,"") & "%"

But you will only get a display of 0 if you haven't established the values of the contributing variables somewhere in the report.

-LB

 

[jeffm777]

Ok dumb question but how do I determine the columns? Would column F be the 6th column from the left side of the report?

 

[lbass]

The variable could be called anything, and someone (apparently not you) decided to call it ColumnF, but I doubt it is simply referring to an existing column, becuase then there would be no need for a variable. You should look through other formulas to find the formula that assigns the value.

But first, where is the display string value being used? Is it within a crosstab? Or is it simply on a field/formula in the body of the report? What is the content of the field/formula? Why do you think Display String is being used?

-LB

 

[jeffm777]

You are correct in the fact that I didn't initially develop this report...just trying to fix it.

The display string value is being used on a formula in the group footer of the report called sales%proj. The content is simple a 0. The report as a whole is showing cost of goods.

There are two other formulas in the report that seem to define "F" and "H", as well as others.

There's a formula named granual which contains...

If {InventoryAUT.Cost} <= 250 then
"A"
else
If {InventoryAUT.Cost} > 250 AND {InventoryAUT.Cost} <= 500 then
"B"
else
If {InventoryAUT.Cost} > 500 AND {InventoryAUT.Cost} <= 750 then
"C"
ELSE
If {InventoryAUT.Cost} > 750 AND {InventoryAUT.Cost} <= 1000 then
"D"
ELSE
If {InventoryAUT.Cost} > 1000 AND {InventoryAUT.Cost} <= 1500 then
"E"
ELSE
If {InventoryAUT.Cost} > 1500 AND {InventoryAUT.Cost} <= 2000 then
"F"
ELSE
If {InventoryAUT.Cost} > 2000 AND {InventoryAUT.Cost} <= 2500 then
"G"
ELSE
If {InventoryAUT.Cost} > 2500 AND {InventoryAUT.Cost} <= 3000 then
"H"
ELSE
If {InventoryAUT.Cost} > 3000 AND {InventoryAUT.Cost} <= 3500 then
"I"
ELSE
If {InventoryAUT.Cost} > 3500 AND {InventoryAUT.Cost} <= 4000 then
"J"
ELSE
If {InventoryAUT.Cost} > 4000 AND {InventoryAUT.Cost} <= 4500 then
"K"
ELSE
If {InventoryAUT.Cost} > 4500 AND {InventoryAUT.Cost} <= 5000 then
"L"
ELSE
If {InventoryAUT.Cost} > 5000 AND {InventoryAUT.Cost} <= 5500 then
"M"
ELSE
If {InventoryAUT.Cost} > 5500 AND {InventoryAUT.Cost} <= 6000 then
"N"
ELSE
If {InventoryAUT.Cost} > 6000 AND {InventoryAUT.Cost} <= 6500 then
"O"
ELSE
If {InventoryAUT.Cost} > 6500 AND {InventoryAUT.Cost} <= 7500 then
"P"
ELSE
If {InventoryAUT.Cost} > 7500 then
"Q"
ELSE
"X"

There's also a formula called SalesGroup which contains...

Select Case GroupName ({@granual})
Case "A"
formula = " 0 - 250"
Case "B"
formula = "251 - 500"
Case "C"
formula = "501 - 750"
Case "D"
formula = "751 - 1000"
Case "E"
formula = "1001 - 1500"
Case "F"
formula = "1501 - 2000"
Case "G"
formula = "2001 - 2500"
Case "H"
formula = "2501 - 3000"
Case "I"
formula = "3001 - 3500"
Case "J"
formula = "3501 - 4000"
Case "K"
formula = "4001 - 4500"
Case "L"
formula = "4501 - 5000"
Case "M"
formula = "5001 - 5500"
Case "N"
formula = "5501 - 6000"
Case "O"
formula = "6001 - 6500"
Case "P"
formula = "6501 - 7000"
Case "Q"
formula = "7001 - 7500"
Case "X"
formula = " > 7501"

Case Else
formula = "Captain Fantastic"
End Select

 

[lbass]

Sorry, but none of these are creating the variable value. You might need to check the formatting formula areas for other fields to find out where the value is being set.

Also, please test the values of F and H, by changing the display string formula to:

WHILEPRINTINGRECORDS;
NUMBERVAR columnF;
NUMBERVAR columnH;
NUMBERVAR tempX;
if columnH <> 0 then
tempX := columnF%columnH;
//numbervar tempX := columnQ;
if tempX < 0 then
toText(tempX * -1, 1,"") & "%"
else
totext(tempX,1,"") & "%;
totext(columnF)

Then change the last line to use columnH and report back.

-LB

 

[jeffm777]

I had to comment out some other string formulas that were erroring as well with the same division by zero error but finally got a 0.00 in the field when I use columnF or columnH.

 

[lbass]

Well, this suggests that at some point someone removed the formulas that established their values, so that the current formula is no longer usable.

-LB

 

[dgillz]

What is in column F and H of the report? Is it a formula field or a database field?

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html