Share via

Percentage of Actual VS Target

  • Article

Hi All

Recently I have been involved lot of time to answer on the SSRS forum how to take percentage of actual value against a total or target value and lot of people are facing problem by having a result as #error message some time NAN and some time it come as infinity. I thought it would be good idea to put something together.

IN SSRS when you divide some values with another value and the divisor has zero value or null values then it will end up as an error (Like NAN, #error, Infinity)

When you use expression in SSRS, Reporting services evaluate the expression fully so you need to put IIF while dividing the value as well

For example if you are trying to take percentage of your actual vs. target and some time you don’t have any target so you can put expression  like this  

-- You can either use this one 
=IIF((Fields!Target.Value = 0 or Fields!Target.Value  is nothing), 0, Fields!Actual.value/IIF(Fields!Target.Value = 0 or Fields!Target.Value is nothing, 1,(Fields!Target.Value) )) 
--or  use this one 
=IIF((Fields!Target.Value = 0 or Fields!Target.Value  is nothing), nothing, Fields!Actual.value/IIF(Fields!Target.Value = 0 or Fields!Target.Value is nothing, 1,(Fields!Target.Value) ))

I hope this will help who are trying to achieve similar functionality inside expression,.

If you have any questions please ask.

Many Thanks

Syed Qazafi Anjum