Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Doing Percentages in Reporting Services SQL Server 2005

Posted on 2007-04-01
6
Medium Priority
?
598 Views
Last Modified: 2008-01-09
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
Comment
Question by:Fraser_Admin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 18832379
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
 

Author Comment

by:Fraser_Admin
ID: 18832434
I don't understand the inner join?  Why would i do that part?
0
 

Author Comment

by:Fraser_Admin
ID: 18832614
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
 

Accepted Solution

by:
Fraser_Admin earned 0 total points
ID: 18832638
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

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question