Sum of point according to ratio in crystal report

Payal sathavara
Payal sathavara used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Raghavendra HullurSoftware Developer

Commented:
Hi,
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
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
What is your backend DB?

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)

Open in new window

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
Create a new Running Total using a SUM of TotalQty with Evaluate for each ID Group.
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
YOur setup will work, just don't do a sum on Total Quantity and Points

mlmcc
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial