Calculation of Data in FoxPro Report/Form

[mherz715]

Good day,
I am trying to create a simple form and report and I would like to ask how I formulate the data below in foxpro report or in a form of code.
For example I have two record:
1. Expandable Exp. 3,000.00
2. Salary and Wages 13,000.00

What I want to do is to have an output like this:
1. Salary and Wages 12,000.00
(I should less the Salary&Wages to Expandable Exp.)
Please help me.

Thanks in advance.

 

[Mike Lewis]

It's not clear from your question what you are trying to achieve. In particular, where does the figure of 12,000.00 come from?

You talk about "less the Salary&Wages to Expandable Exp". Do you mean the lesser of the two items (in which case, you would show 3,0000.00)? Or do you mean Salary & Wages minus Expandable Exp (in which case, the result would be 10,000.00)?

If you could clarify the question, I'm sure we'll be able to help.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

In general you can do calculations in a report via report variables. In the variables tab of the report properties you can define a variable, what is calculated in it, how it is inited (set once at report start) and reset (eg "Report" means never).

Click on Help in that tab and you get all neccessary info about report variables.

As a quick starter you could set
Value To Store: 1
Initial Value: 0
Reset Value based on: Report
Calculation type: Sum

That would do the same as the calculation type Count, then, as Sum(1) is Count. Changing Value To Store changes, what is summed, so you could also sum an Amountfield. What you need to do now, is put the report variable somewhere you want to print it, eg in the report summary band or in the page footer, the same way you add a table field in these section, just set the report field expression to the report variable name instead of table.field.

If you need to add or subtract depending on the description, I'd think about changing the numbers to include the sign. If this isn't feasable a solution would be to set Value To Store to an expression using eg:
ICASE(description='Salary',Amountfield,description='Expendable',-Amountfield,Amountfield)

You could expand that, but I would rather set the amountfields to the right sign, instead of putting that logic into the report.

Bye, Olaf.

 

[JRB-Bldr]

You might want to look at the free on-line VFP tutorial videos at:

http://www.garfieldhudson.com/FreeVideos.aspx

Of particular note might be:
Basic Reporting - Pt. 1
Basic Reporting - Pt. 2

Good Luck,
JRB-Bldr

 

[Dan Freeman]

For simple math, there's no need for report variables. Just make the expression field1-field2.

But, as pointed out, that doesn't seem to be what you're after. You're displaying "magic math" without telling us how you got the answer or where the parts of the answer come from.

 

[mherz715]

To all, thank you for the response.
To rephrase my question:
I am trying to create a simple form and report and I'd like to ask what condition should i use (that might be the best)to formulate the data below in foxpro report or should I put in a form of code.
For example I have two record:
1. Expandable Exp. 3,000.00
2. Salary and Wages 13,000.00
What I want to do is to have an output like this:
1. Salary and Wages 10,000.00
(I should less the Salary&Wages to Expandable Exp.)
I tried to use if statement but nothing is happen.
Please, some advise.
thank you in advance.

 

[Mike Lewis]

So, in other words you want to subtract one value from the other?

If so, all you need is a simple expression:

Answer = Salary_Wages - Expandable_Exp

If you want to display that value in a form, place a label on the form, and set its caption to Salary_Wages - Expandable_Exp (substituting the actual field or variable names that hold the salary, etc).

If you want the value to appear in a report, place a field in the report, and set its expression to the above.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

You can't write if in a report field, you can only write expressions. You can use IIF() though, to have alternate expressions depending on a condition, also see ICASE().

What for do you need IF? Just let the data have the correct sign and you can simply sum.

Bye, Olaf.

 

[Mike Lewis]

Or maybe you want to subtract whichever figure is lower from whichever figure is higher?

If so, subtract either one from the other, then use the ABS() function to make the answer positive.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Another very simple thing:

If you have two records in a table for reporting, you get two detail lines in the report.

Aggregating data into one record rather is done in a report prerun with SQL and GROUP BY. Or you print nothing in the detail band, add a group band and specify what to group and only print in the group footer band.

Bye, Olaf.

 

[Dan Freeman]

What you're describing is incredibly simple field math. Just use an expression subtracting one field from another.

I'm wondering, though, whether you already have those two fields to use in an expression. Do you need help getting to that point?

There's no use for IF whatsoever in this endeavor unless you're trying to do a lot more involved than simple math using two fields.