Avatar of Payal sathavara
Payal sathavara
Flag for India asked on

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
Crystal Reports

Avatar of undefined
Last Comment
Ido Millet

8/22/2022 - Mon
Raghavendra Hullur

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 Kumar

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 Millet

Create a new Running Total using a SUM of TotalQty with Evaluate for each ID Group.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Mike McCracken

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ido Millet

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.