Sum of point according to ratio in crystal report
Dear all,
In data table i have following record
Id Name TotalQty Qty Ratio Points
1 A 10 10 100% 0.5
2 B 20 20 100% 1.0
3 C 10 05 050% 1.0
3 C 10 05 050% 1.0
4 D 10 10 100% 0.5
In Id '3' TotalQty is 10 but is Qty 05 because Ratio is half for that Qty.
I have created Group For Id in Crystal Report but i got wrong data for Points as Total Qty...
I got Output like,
Id Name TotalQty Qty Points
1 A 10 10 0.5
2 B 20 20 1.0
3 C 20 10 2.0
4 D 10 10 0.5
Please suggest me solution for got the output like
Id Name TotalQty Qty Points
1 A 10 10 0.5
2 B 20 20 1.0
3 C 10 10 1.0
4 D 10 10 0.5
In data table i have following record
Id Name TotalQty Qty Ratio Points
1 A 10 10 100% 0.5
2 B 20 20 100% 1.0
3 C 10 05 050% 1.0
3 C 10 05 050% 1.0
4 D 10 10 100% 0.5
In Id '3' TotalQty is 10 but is Qty 05 because Ratio is half for that Qty.
I have created Group For Id in Crystal Report but i got wrong data for Points as Total Qty...
I got Output like,
Id Name TotalQty Qty Points
1 A 10 10 0.5
2 B 20 20 1.0
3 C 20 10 2.0
4 D 10 10 0.5
Please suggest me solution for got the output like
Id Name TotalQty Qty Points
1 A 10 10 0.5
2 B 20 20 1.0
3 C 10 10 1.0
4 D 10 10 0.5
What is your backend DB?
SQL - you need to first distinct your data then you can do grouping..
Sample solution for you -
SQL - you need to first distinct your data then you can do grouping..
Sample solution for you -
CREATE TABLE testSLQ
(
Id INT
,Name varchar(1)
,TotalQty iNT
,Qty INT
,Ratio VARCHAR(10)
,Points DECIMAL(10,1)
)
GO
INSERT INTO testSLQ VALUES
(1,'A',10,10,'100%',0.5),
(2,'B',20,20,'100%',1.0),
(3,'C',10,05,'050%',1.0),
(3,'C',10,05,'050%',1.0),
(4,'D',10,10,'100%',0.5)
GO
SELECT * FROM
(
SELECT DISTINCT * FROM testSLQ
)u
/*------------------------
SELECT * FROM
(
SELECT DISTINCT * FROM testSLQ
)u
------------------------*/
Id Name TotalQty Qty Ratio Points
----------- ---- ----------- ----------- ---------- ---------------------------------------
1 A 10 10 100% 0.5
2 B 20 20 100% 1.0
3 C 10 5 050% 1.0
4 D 10 10 100% 0.5
(4 row(s) affected)
Create a new Running Total using a SUM of TotalQty with Evaluate for each ID Group.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
mlmcc is correct, if you don't need to compute the ratio at a higher level of aggregation.
You need the running total approach only if you need to compute the ratio at a higher level of aggregation such as higher-level groups or grand total.
You need the running total approach only if you need to compute the ratio at a higher level of aggregation such as higher-level groups or grand total.
Some questions:
1. Even though there might be multiple records for each ID, the total ratio will always be 100%, is it true?
2. TotalQty and Points needs to be proportional to the ratio, right?
3. For Ratio, will there be a chance of other values other than 50% and 100% ?
I just created a sample based on your values, see if that helps. All I did was, I created 2 formulae for TotalQty and Points based on ratio and then summed up.
Ratios.rpt