ssrs 2005 matrix divide count value by the subtotal

Question

0.00/5 (No votes)

See more:

I have this table

SQL

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

SQL

                         | =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

SQL

       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

SQL

       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

Posted 20-Dec-13 1:55am

Soldier_T

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-12-20T03:10:00

Solution 1

If you want to get 3/17 instead of 17.65%, formula should looks like:

CSS

=Fields!grade.Value & "/" & Fields!count.Value

Update your query:

SQL

SELECT ProductName, Grade, COUNT(Grade) AS CountOfGrades, (SELECT COUNT(Grade) FROM Inspection) AS TotalOfGrades
FROM   Inspection
WHERE (Branch = @branchno) AND (Date BETWEEN @DateFrom AND DATEADD(day, 1, @DateTo))
          AND (Grade <> '')
GROUP BY ProductName, Grade
ORDER BY ProductName

and formula.

Posted 20-Dec-13 3:10am

Maciej Los

Updated 21-Dec-13 9:36am

v2

Comments

Soldier_T 20-Dec-13 10:46am

Hello thanks for your answer but when i do that i get the grade / the count
orange 1/3 2/2 3/10 bad/2
thats where im stuck i cant get it to give me the total of orange alone so that it would be like the table above
orange| 3/17 | 2/17 | 10/17 | 2/17
which 17 is the total of 3+2+10+2

Soldier_T 23-Dec-13 9:02am

Thanks for your help with this its been messing with my head big time i tried that query but it gave me the total for all inspections so i added some change :) sssswwweeeeettttt
thanks

Maciej Los 23-Dec-13 9:04am

You're very welcome ;)
Could you accept my answer as a solution (green button)?

Soldier_T · Accepted Answer · 2013-12-23T03:04:00

Thanks to Maciej got this to work

XML

SELECT ProductName, Grade, COUNT(Grade) AS CountOfGrades, (SELECT COUNT(Grade) FROM Inspection WHERE (Branch = @branchno) AND (Date BETWEEN @DateFrom AND DATEADD(day, 1, @DateTo))
          AND (Grade<>'') AND (productname=i.productname) AS TotalOfGrades
FROM   Inspection as i.inspection
WHERE (Branch = @branchno) AND (Date BETWEEN @DateFrom AND DATEADD(day, 1, @DateTo))
          AND (Grade <>'')
GROUP BY ProductName, Grade
ORDER BY ProductName