Adding Percentage of Total Columns to Table in SSRS Report

Question

Adding Percentage of Total Columns to Table in SSRS Report

smthembu

Hall of Fame

Points: 3464
More actions
November 17, 2010 at 2:42 am

#229334

Hi All
I'm trying to add a percentage of total column in a SSRS (SSQL 2005) and can't see a logical way to do it
e.g. of Table
Group = Fields!Assignee_Dept.Value
Col 1 =Sum(Fields!SLA_Achieved.Value)
Col 2 =Sum(Fields!SLA_Missed.Value)
Col 3 = Achieved as a % Total...which is what I need
Col 4 = Missed as a % Total..which is what I need
Any help would be appreciated
It's better to fail while trying, rather than fail without trying!!!

Viewing 4 posts - 1 through 3 (of 3 total)

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

paulm-510034 SSC Enthusiast Points: 136 More actions · Answer 1

Hi,

I think I know what you require....I'd probably make the calculations at the source in SQL rather than in the layout(expression editor) of SSRS. It's a bit quicker and easier.

Something like this will get you the required result

SELECT groupdesc

,sum(achieved)

,sum(missed)

,cast(sum(achieved) as numeric(8,2))/

cast((sum(achieved)+sum(missed)) as numeric(8,2))*100

FROM Table1

group by groupdesc

Please note casting to numeric is important.

I hope that helps.

Paul

smthembu Hall of Fame Points: 3464 More actions · Answer 2

Thanks for your response the below expression worked with formating the cell in SSRS table to show % sign.

=Sum(Fields!SLA_Missed.Value)/(Sum(Fields!SLA_Achieved.Value) + Sum(Fields!SLA_Missed.Value))

=Sum(Fields!SLA_Achieved.Value)/(Sum(Fields!SLA_Achieved.Value) + Sum(Fields!SLA_Missed.Value))

Thanks

It's better to fail while trying, rather than fail without trying!!!

Dave Klug SSCertifiable Points: 5811 More actions · Answer 3

You may also want to add a check for zero in the denominator or you will get a didvide by zero error. Also, be aware an iif block will not catch these; you will need to use custom code.