Adding Percentage of Total Columns to Table in SSRS Report

Question

Post reply

Adding Percentage of Total Columns to Table in SSRS Report

aeonf242

Old Hand

Points: 316
More actions
June 25, 2010 at 1:49 am

#223193

Hi

I'm trying to add a percentage of total column in a simple SSRS report (SSQL 2005) and can't see a logical way to do it as there are no GroupSum or Percentage of Total functions

e.g. of Table

Group = Fields!Loan_Cat.Value

Col 1 = sum(Fields!pyofbl.Value)

Col 2 = Line Total as a % Total...which is what I need

Any help would be appreciated 🙂

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 1

Hi ,

I think I know what you mean.

The group total would be sum(Fields!Loan_Cat.Value)

The total grand total would be the same expression but you would place it in the footer of your table

Rename this field, in the cell properties under name and call it say GT .

You can then call this calculated cell to use in your percentage expression.

in a field next to the the group sum field create a field with the expression

sum( Fields!Loan_Cat.Value) / ReportItems!GT.Value

Hope this helps!

aeonf242 Old Hand Points: 316 More actions · Answer 2

aeonf242

Old Hand

Points: 316

June 25, 2010 at 5:27 am

#1184920

Thanks..that's very helpfuL!

llaminm Old Hand Points: 310 More actions · Answer 3

llaminm

Old Hand

Points: 310

September 7, 2010 at 10:06 am

#1217411

Hi,

I am trying to replicate your suggestion of adding the "Percent total", but could not implement what've suggested. I have attached an Image file of the report, and have applied red color in places where i want to total the Percent.

Thanks

H4K Hall of Fame Points: 3374 More actions · Answer 4

H4K

Hall of Fame

Points: 3374

September 8, 2010 at 6:06 am

#1217871

sum(Recieved_Late) / (SUM(Recieved_Late) + SUM(Recieved_on_Time))

add 'P2' to its format

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

llaminm Old Hand Points: 310 More actions · Answer 5

llaminm

Old Hand

Points: 310

September 8, 2010 at 9:21 am

#1218033

Hello,

Thanks for the help. I have tried what you've suggested, and unfortunately didn't get the correct total - (56%).

I have attached the result in an image file below for your reference. Also, what do you mean

by add 'P2' to its format ?

Thanks

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 6

P2 is shorthand for percent, 2 decimal places. You can go into the text box dialog and do the numeric formatting through the GUI, or go the the text box properties and under format type "p2" or if you don't want any decimal places type "p0"

FYI there is also shorthand for other types of formats

C2 is for currency to 2 decimal places

N0 is numeric formatting, zero decimal places

etc.

As you get better at SSRS you will find that these are a faster way to format than the GUI, especially since you can highlight multiple cells at the same time and type in the shorthand formatting code and have them all done whereas using the GUI you have to do them one at a time.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 7

Daniel Bowlin

SSC-Dedicated

Points: 34566

September 8, 2010 at 12:55 pm

#1218185

have tried what you've suggested, and unfortunately didn't get the correct total - (56%).

My math 208 / 1534 is the 13.5% that your picture shows. Percent calculations are not additive, meaning you cannot take the sum of the columns and get the correct number.