SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Urgent : Sum of calculated column		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Beginer2012
Starting Member

45 Posts
Posted - 05/16/2012 :  09:32:26  Show Profile  Reply with Quote
Hello,
My dataset contains two column and ID column and a NumberOfCases columns.
I'm calculating the percentage of each ID in the table by dividing each NumberOfcases by total numberOfCases. How ever I want to give a sum of these percentages in the Last row. How can I sum an expression in the table ?

Thank you

Example:
ID NumberOfCases Percentage
1 17 10.0 %
2 28 16.5 %
3 30 17.6 %
4 45 26.5 %
6 50 29.4 %

Total 170 100 % ==========> I need this % total.

The total of NumberOfCases is easy to get by having a sum on a field...But the percentage is not a field...It is calculated in an expresion in the table.
Edited by - Beginer2012 on 05/16/2012 09:59:31

visakh16
Very Important crosS Applying yaK Herder

India
47189 Posts
Posted - 05/16/2012 :  20:32:50  Show Profile  Reply with Quote
you can add a similar expression for totalrow to make it calculate over the dataset.use scope as dataset name when applying the required aggregate functions.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Beginer2012
Starting Member

45 Posts
Posted - 05/17/2012 :  17:35:21  Show Profile  Reply with Quote
Can you elaborate more please??
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 05/17/2012 :  18:14:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Well, the sum will always be 100%, so why would you want to sum it?

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

vasu_thiagu
Starting Member

India
1 Posts
Posted - 05/25/2012 :  06:04:50  Show Profile  Reply with Quote
Use this:

select id, NoCases, NoCases*100/(select sum(NoCases) from Cases) from Cases
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47189 Posts
Posted - 05/25/2012 :  10:40:29  Show Profile  Reply with Quote
quote:
Originally posted by Beginer2012

Can you elaborate more please??


you can put expression like

=SUM(Fields!yourfield.value,"your required scope here")


for percentage you can yuse like

SUM(Fields!NumberOfcases.value)/SUM(Fields!NumberOfcases.value,"datasetname")

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.26 seconds. Powered By: Snitz Forums 2000