I have this table
SELECT ProductName, Grade, COUNT(Grade) AS count
FROM Inspection
WHERE (Branch = @branchno) AND (Date BETWEEN @DateFrom AND DATEADD(day, 1, @DateTo))
AND (Grade <> '')
GROUP BY ProductName, Grade
ORDER BY ProductName
and the report matrix has subtotals that i would like to divide with count of grade
| =Fields!grade.Value |Total
========================= =========================== ======
=Fields!ProductName.Value|=Sum(Fields!count.Value) |
but ive been trying to get the fields count value to divide by the total value but having trouble with that cause what i need the table to look like is this
Grade 1 | Grade 2 | Grade 3 | bad
======|========|=========|=========|=======
orange| 17.65% | 11.76% | 58.82% | 11.76%
------ -------- --------- ---------|--------
banana| 13.33% | 13.33% | 53.33% | 20.00%
which we would get by doing the following
Grade 1 | Grade 2 | Grade 3 | bad | Total
======|========|=========|=========|=======|======
orange| 3/17 | 2/17 | 10/17 | 2/17 | 17
------ -------- --------- --------- ------- ------
banana| 2/15 | 2/15 | 8/15 | 3/15 | 15
Thanks for your help
Thanks to Maciej from http://www.codeproject.com/ we got this to work