Sum of point according to ratio in crystal report
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
Software Developer
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
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)
Professor Emeritus of MIS at Penn State Erie and Owner, Millet Software
Professor Emeritus of MIS at Penn State Erie and Owner, Millet Software
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.
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions