calculate value on 1 sheet from 2 records

[stonee]

hi guys... maybe anybody can help me out??

there is a table in a database... which is named "point"

in this table lots of points are stored... like following:

point.ID- Point.X- Point.Y- Point.Z-
1 - 10 - 10 - 10
2 - 20 - 20 - 30
3 - 30 - 5 - 4
abc - 40 - 50 - 145
def - 44 - 45 - 10

.....and so on


i have to take the x, y, and z values of point.ID abc and def and calculate with them....
the problem is, i can not have only values from 2 different records ( i mean data row with record)

i created a formula:

if {POINT.id} = "abc" THEN {POINT.x}
if {POINT.id} = "abc" THEN {POINT.y}
if {POINT.id} = "abc" THEN {POINT.z}

> AND SO ON....(same for def)

then i have the values in the new formulafields called for instance
@abc_X

but i have only values from abc OR def ....
i need both but how can i do that...

i need the x, y, z values of the mentioned two ID's and thats all...
all other points i don't need


then i calculate a special new datafield with the following formula e.g.:


({abc_X}-{def_X} / {abc_Y}-{def_Y})

(where abc_X e.g. the value x of Point.ID abc is...)


i need the x, y, z, values of two PointID's , two rows.. that must be possible... not?


thank u......

stonee

 

[JustineB]

Hi!

Try grouping by POINTID (you can suppress the group header and footer to make the report look the same as before - the records will be sorted according to this grou though)
Then add this formula:

numbervar abc;
numbervar def;
numbervar total;

if {Table1.Pointid} like "abc" then
abc:= sum({Table1.pointx},{Table1.Pointid}) +
sum({Table1.pointy},{Table1.Pointid}) +
sum({Table1.pointz},{Table1.Pointid});

if {Table1.Pointid} like "def" then
def:= sum({Table1.pointx},{Table1.Pointid}) +
sum({Table1.pointy},{Table1.Pointid}) +
sum({Table1.pointz},{Table1.Pointid});

total:= abc + def;

total;

The results should be (in the details section) :

point.ID- Point.X- Point.Y- Point.Z- FORMULA
1 - 10 - 10 - 10 0
2 - 20 - 20 - 30 0
3 - 30 - 5 - 4 0
abc - 40 - 50 - 145 235
def - 44 - 45 - 10 334

If you want the totals displaying as a grand total or group total, simply move the formula.

Please let me know if this is what you are trying to do, if not, mail back and I will try and help further.

Kind regards,
Justine.

 

[stonee]

hey justine
thanks a lot for ur reply..
but i have still a question

how or what should i group?
the formula should look like following for the first..


numbervar centerpointx;
numbervar centerpointy;
numbervar centerpointz;
numbervar circlcenterx;
numbervar circlcentery;
numbervar circlcenterz;
numbervar total;

//whats that sum for????

if {Point.Ptid} like "centerpoint" then
centerpointx:= sum({Point.x},{Point.Ptid});
centerpointy:= sum({Point.y},{Point.Ptid});
centerpointz:= sum({Point.z},{Point.Ptid});


if {Point.Ptid} like "cir_center" then
circlcenterx:= sum({Point.x},{Point.Ptid});
circlcentery:= sum({Point.y},{Point.Ptid});
circlcenterz:= sum({Point.z},{Point.Ptid});

total:= ((centerpointx - circlcenterx)/(centerpointy - circlcentery))*1000;

total;


all i want is to have the respective values in the the variables, how can i do that...
that way i did it, it doesn't work..

thanks stonee...

 

[JustineB]

You need to Group on the whole report - do you get an error message when you check the formula that you added (last message that you sent)

 

[stonee]

justine...

but how can i group the whole report
and i always have on the firstpage of the report only the values of id centerpoint and on the second page the values of circlecenter in the formula

so on first and second page two wrong results...
i want to have one report page with one value.
hmm thanks again..
stonee

stonee@bluemail.ch

 

[Guest_imported]

hey justine...
maybe u read this..it works!
i made a group and then i added the following code to the formula:

numbervar numi1;
numbervar numi2;
numbervar numi3;

numbervar numi4;
numbervar numi5;
numbervar numi6;

numbervar total;

if {Point.PtId} = "centerpoint" then numi1 := {Point.X};
if {Point.PtId} = "centerpoint" then numi2 := {Point.Y};
if {Point.PtId} = "centerpoint" then numi3 := {Point.Z};

if {Point.PtId} = "cir_center" then numi4 := {Point.X};
if {Point.PtId} = "cir_center" then numi5 := {Point.Y};
if {Point.PtId} = "cir_center" then numi6 := {Point.Z};

total := ((numi1+numi3)/(numi2+numi4)*100000)+numi6;
totext(total)+"mm/m";

and placed the field in the "report footer" otherwise it doesn't work of course....
but once there was a msg cannot divide through zero..
i think the formula is correct or not?

any comments about that?

thanks to justine and all others
cheers stonee