The fields are Type, CompanyName, Price, qty, AverageAt
AverageAt in this case= 100 accross the table
I have group on Type - group 1. Also I have Group by Companyname -Group 2.
TYPE COMPANYNAME PRICE QTY
HARDWARE XEROX 12 100 // 12 need to be used for Average because 12<100
HARDWARE HP 1 150
HARDWARE HP 10 180 // 180 need to be used for Average because 10<100
HARDWARE HP // this is what is the problem currently as I can't know if there will be next Price<=100
HARDWARE HP 1000 110
HARDWARE SONY 1 70
HARDWARE SONY 10 90 // 10 CANNOT BE USED AS THERE'S PRICE 100 below even not right below
HARDWARE SONY
HARDWARE SONY 100 120 // 120 need to be used for Average because Price=100
HARDWARE SONY 1000 200
HARDWARE AVERAGE
FOR PRICE<=100 80.5
SOFTWARE MICROSOFT 50 10 // 10 need to be used for Average because 10<100
SOFTWARE MICROSOFT 1000 50
SOFTWARE MICROSOFT 2000 80
SOFTWARE MICROSOFT 3000 100
SOFTWARE AVERAGE
FOR PRICE<=100 10
The way I calculate average for Type is by having Running Average with evaluation formula and Reset on Type Group:
NumberVar vQTY:= {QTY};
NumberVar vPRICE:= {PRICE};
NumberVar vNextPRICE:= next({PRICE});
BooleanVar vNextisNullQTY :=nextisnull({QTY}) ;
BooleanVar vNextisNullPRICE :=nextisnull({QTY}) ;
// this is to be static in all columns
{rpt_DReport.CoCode}<>{rpt_DReport.RequestingCoCode}
AND
(
(
(
{COMPANYNAME} =next({COMPANYNAME})
AND
vPRICE<={AverageAt}
)
AND
(
vNextisNullPRICE
or vNextPRICE=0
or vNextPRICE>{AverageAt}
or nextisnull({COMPANYNAME})
or vNextisNullQTY
)
)
OR
(
(
{rpt_DReport.BankName} <>next({rpt_DReport.BankName})
AND
vPRICE<={rpt_DReport.AverageAt}
)
)
)
Really strugling, please help!
AverageAt in this case= 100 accross the table
I have group on Type - group 1. Also I have Group by Companyname -Group 2.
TYPE COMPANYNAME PRICE QTY
HARDWARE XEROX 12 100 // 12 need to be used for Average because 12<100
HARDWARE HP 1 150
HARDWARE HP 10 180 // 180 need to be used for Average because 10<100
HARDWARE HP // this is what is the problem currently as I can't know if there will be next Price<=100
HARDWARE HP 1000 110
HARDWARE SONY 1 70
HARDWARE SONY 10 90 // 10 CANNOT BE USED AS THERE'S PRICE 100 below even not right below
HARDWARE SONY
HARDWARE SONY 100 120 // 120 need to be used for Average because Price=100
HARDWARE SONY 1000 200
HARDWARE AVERAGE
FOR PRICE<=100 80.5
SOFTWARE MICROSOFT 50 10 // 10 need to be used for Average because 10<100
SOFTWARE MICROSOFT 1000 50
SOFTWARE MICROSOFT 2000 80
SOFTWARE MICROSOFT 3000 100
SOFTWARE AVERAGE
FOR PRICE<=100 10
The way I calculate average for Type is by having Running Average with evaluation formula and Reset on Type Group:
NumberVar vQTY:= {QTY};
NumberVar vPRICE:= {PRICE};
NumberVar vNextPRICE:= next({PRICE});
BooleanVar vNextisNullQTY :=nextisnull({QTY}) ;
BooleanVar vNextisNullPRICE :=nextisnull({QTY}) ;
// this is to be static in all columns
{rpt_DReport.CoCode}<>{rpt_DReport.RequestingCoCode}
AND
(
(
(
{COMPANYNAME} =next({COMPANYNAME})
AND
vPRICE<={AverageAt}
)
AND
(
vNextisNullPRICE
or vNextPRICE=0
or vNextPRICE>{AverageAt}
or nextisnull({COMPANYNAME})
or vNextisNullQTY
)
)
OR
(
(
{rpt_DReport.BankName} <>next({rpt_DReport.BankName})
AND
vPRICE<={rpt_DReport.AverageAt}
)
)
)
Really strugling, please help!