percent calculation

[BHScripter]

Hello:

I am attempting to create a calculated field with the following:

=IIF (Sum(Fields!totalilt.value = 0,0,Sum(Fields!totalilt.value)*100/Sum(fields!totalilt.value)

where am I going wrong?

Thanks much for the help.

 

[RiverGuy]

I count four left parentheses and only two right.

 

[BHScripter]

Yeah, I found that and it looked okay but whenever I preview the report Visual Studio crashes after I insert the calculated field. What I am trying to reproduce are the following formula fields I had when I created this report originally in crystal:

Total (so always 100% unless its 0):
(Crystal formula)
if Sum ({@totalilt})= 0 then 0
else
Sum ({@totalilt})*100/Sum ({@totalilt})

By group: (crystal formula)
if Sum ({@totalilt}, {Command.hrname})= 0 then 0
else
Sum ({@totalilt}, {Command.hrname})*100/Sum ({@totalilt}, {Command.hrname})

Any help to get these two types of percent calcs would be most appreciated. I can intuit all the permutations if I could get these two "translated" for Reporting Services.

Best regards,

 

[RiverGuy]

I've usually done the percentages in the expression of the TextBox as opposed to a calculated field, but I do know that you need to nest two IIF statements.

But perhaps a cleaner way is to use a VB function within SSRS. Take a look at this blog post. The author demonstrates how to use a VB function, while the comment by ggaggo demonstrates the nested IIFs for divide by zero handling.

http://gaspodethewonderdog.blogspot.com/2007/11/reporting-services-divide-by-zero-error.html

 

[BHScripter]

Hi:
I looked at formatting the textbox and that works great. The following works:
=FormatPercent(Sum(Fields!attendedcount.Value)/sum(fields!totalilt.value))

 

[BHScripter]

With the expression I used:
=FormatPercent(Sum(Fields!attendedcount.Value)/sum(fields!totalilt.value))

how do I set zero decimal places?

Best,