Solved

Doing Percentages in Reporting Services SQL Server 2005

Posted on 2007-04-01
6
578 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
  • 3
6 Comments
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
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
Comment Utility
I don't understand the inner join?  Why would i do that part?
0
 

Author Comment

by:Fraser_Admin
Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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 …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now