• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 603
  • Last Modified:

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%
0
Fraser_Admin
Asked:
Fraser_Admin
  • 3
1 Solution
 
dqmqCommented:
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

0
 
Fraser_AdminAuthor Commented:
I don't understand the inner join?  Why would i do that part?
0
 
Fraser_AdminAuthor Commented:
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??
0
 
Fraser_AdminAuthor Commented:
nevermind.  i figured out how to do it.  for anyone else trying to do something similar:

=Sum(Fields!volumeamount.Value,"GradeGroup") / Sum(Fields!volumeamount.Value, "ContractGroup")

You have to put the name of your group in quotes in your sum function so it knows where you are trying to take the sum from.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now