asked on

Doing Percentages in Reporting Services SQL Server 2005

I'm trying to show the following report in reporting services

The percentage field is percentage by contract/grade.

How do I have to do my grouping inorder to get that percentage field correct?

Contract A SubContract B Grade 1 10 10%
Contract A SubContract C Grade 2 40
Contract A SubContract D Grade 2 30 70%
Contract A SubContract A Grade 3 20 20%
Total Volume 100

Contract B SubContract A Grade 1 20 100%

dqmq

Group by Contract in the underlying record source:

Select Contract, SubContract, Grade, yourtable.Quantity, yourtable.Quantity/ContractView.Quantity*100 AS Percentage
FROM yourtable
INNER JOIN
--this is the group by contract
(Select Contract, Sum(Quantity) Quantity from yourtable Group by Contract) AS ContractView
ON ContractView.Contract = yourtable.contract

Fraser_Admin

ASKER

I don't understand the inner join? Why would i do that part?

Fraser_Admin

ASKER

Basically what I have right now is a grouping for contract and a grouping for grade.

So in my grade grouping, I need to access the total in the contract so I can do grade total / contract total to get my percentage.

But I can't seem to figure out the right thing to put in there??

ASKER CERTIFIED SOLUTION

Fraser_Admin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial