During working in SSRS reports I have to handle division by Zero error for calculating accumulated cost by dividing Amount by Quantity.

This expression works for me.

IIf(Sum(Fields!xyz.Value) = 0, “N/A”, Sum(Fields!abc.Value) / IIf(Sum(Fields!abc.Value) = 0, 1,Sum(Fields!xvz.Value)))

I did little research on Expressions and function used in these SSRS expressions

**Expressions:**

**Expressions are used for manipulate or update the value, for example, cost show by dividing amount by Quantity. Rounding of decimal. Similarly Expression can be used to highlight the filed if condition meets. Style font can be changed, even you can uses switch statement, to change the display value of report.**

**Types of Expressions**

**Globals
Operators – Arithmetic, Comparison, Logical
Common Functions – Text, Date & Time, Math, Inspection, Program Flow, Aggregate, Financial, Conversion, Miscellaneous**

We can see each and every one very detail in following.

**Globals**

**Global expressions executes/works in Page Header and Footer parts only.**

**ExecutionTime**** **shows date and time at when report executes

**PageNumber**** **shows page number of each and every page but allowed only in page header and footer

**ReportName**** **displays name of the active report what name we have assigned to the active report

**UserId**** **shows current user name like company/alirazazaidi

**Language**** **displays language like US-English…

**Operators**

**Arithmetic**

**^** power of

***** multiplication

**/** divides two numbers and returns a floating point result

**\** divides two numbers and returns a integer result

**Mod** divides two numbers and returns remainder only

**+** adds two numbers and concatenation for two strings

**–** subtraction and indicates negative value for numeric values

**Comparison**

Known operators : **< <= > >= <>**** **

**Like** compares two strings and return true if matched or else returns False. Ex: =Fields!Title.Value Like Fields!LoginID.Value

**Is** compare two object reference variables Ex: = Fields!Title.Value Is Null

**Concatenation**

**+ and &**** **symbols uses for concatenation

**Logical**

Known: **And, Not, Or**** **

**Xor** SELECT * FROM users where firstname = ‘Larry’ XOR lastname = ‘Smith’

**AndAlso** First condition will check first and if it is true only, goes to next or else it won’t need to check. Because our execution time is saving in a logical operation in which more conditions is combined using AndAlso function.

**OrElse** same like above

**Common Functions**

**Text**

**Asc, AscW**** **returns an integer value represents character code corresponding to a character

**Chr, chrw** returns the character associated with the specified character code

**Filter** =Filter(Fields!Title.Value,”Pr”,true,0

**Format**

=Format(Fields!Price.Value, “#,##0.00”), Format(Fields!Date.Value, “yyyy-MM-dd”)

**FormatCurrency** =formatcurrency(Fields!SickLeaveHours.Value,3)

**FormatDateTime** =FormatDateTime(Fields!BirthDate.Value,Integer)

Examples:

0 returns 10/10/2014

1 returns Friday, October 10, 2014

2 returns 6/3/2014

3 returns 12:00:00AM

4 returns 00:00

**FormatNumber** =FormatNumber(Fields!EmployeeID.Value,2)

Examples: 4.00

**FormatPercent** =”Percentage : ” & formatpercent(Fields!SickLeaveHours.Value)

**GetChar** =GetChar(Fields!Title.Value,5)

**InStr** =InStr(Fields!Title.Value,”a

**InStrRev** =Instrrev(Fields!Title.Value,”a

**LCase** Change strings into lower case

=Lcase(Fields!Title.Value)

**Left** Returns left side characters from a string

=Left(Fields!Title.Value,4)

**Len** Finds length of a string

=Len(Fields!Title.Value)

**LSet** Returns some length of a string from left

=Lset(Fields!Title.Value,5)

**LTrim** Trim left side of a string

=Ltrim(” “&Fields!Title.Value)

**Mid** Returns characters from the mentioned starting position

=Mid(Fields!Title.Value,InSTrRev(Fields!Title.Value,”T

**Replace** Replaces one string with another

=Replace(Fields!Title.Value,”a”,”A

**Right** Returns right side characters from a string

=Right(Fields!Title.Value,10)

**RSet** Returns some length of a string from left

=Rset(Fields!Title.Value,5)

**RTrim** Trim left side of a string

=Rtrim(Fields!Title.Value & ” “)

**Space** Specifies some spaces within strings

=Fields!Title.Value & Space(5) & Fields!Title.Value

**StrComp** Returns a value indicating the result of a string comparison

vbBinaryCompare 0 Perform a binary comparison.

vbTextCompare 1 Perform a textual comparison.

string1 is less than string2 -1

string1 is equal to string2 0

string1 is greater than string2 1

string1 or string2 is Null Null

**StrConv**

=Strconv(Fields!Title.Value,vbProperCase)

=Strconv(Fields!Title.Value,vbLowerCase)

=Strconv(Fields!Title.Value,vbUpperCase)

**StrDup** Returns a string or object consisting of the specified character repeated the specified number of times.

=StrDup(3,”M”)

**StrReverse** =StrReverse(Fields!Title.Value)

**Trim** =Trim(” “& Fields!Title.Value & ” “)

**UCase** =Ucase(Fields!Title.Value)

**Date & Time**

**CDate** Converts a object into date format

=Format(CDate(Fields!BirthDate.Value),”MMMM”)

**DateAdd** Returns a datetime that is the result of adding the specified number of time interval units to the original datetime.

=dateadd(“m”,12,Fields!BirthDate.Value)

**DateDiff** Find number of days, months and years between two dates

=datediff(“d”,Fields!BirthDate.Value,Now)

**DatePart** DatePart(DateInterval.Weekday, CDate(“2009/11/13”), FirstDayOfWeek.Monday) returns 5 (Friday)

**DateSerial** for first day of the month

=DateSerial(Year(Now), Month(Now), 1)

for the last day of the month

=DateSerial(Year(Now), Month(Now)+1, 0)

**DateString** Returns string value of system date

=datestring()

**DateValue** Returns current date

**Day** Returns day value from date

=day(Fields!BirthDate.Value)

**FormatDateTime** =FormatDateTime(Fields!BirthDate.Value,Integer)

Examples:

0 returns 6/3/2014

1 returns Friday, June 03, 2014

2 returns 6/3/2014

3 returns 12:00:00AM

4 returns 00:00

**Hour** =Hour(Fields!BirthDate.Value)

**Minute** =Minute(Fields!BirthDate.Value)

**Month** =Month(Fields!BirthDate.Value)

**MonthName** =MonthName(Month(Fields!BirthDate.Value))

**Now** Indicates current month

=Now() or =Now

**Second** =Second(Fields!BirthDate.Value)

**TimeOfDay** =TimeOfDay()

Returns a date value containing the current time of day according to your system

**Timer** =Timer()

Returns number of seconds elapsed since midnight

**TimeSerial** =TimeSerial(24,60,60)

Returns a date value representing a specified hour, minute and second

**TimeString** =TimeString()

Returns string value representing the current time of day according to your system

**TimeValue** Returns a date value set to jan 1 of year 1

=TimeValue(Fields!BirthDate.Value)

**Today** Returns Current date

**Weekday** Returns an integer value representing day of week

=WeekDay(Fields!BirthDate.Value)

**WeekdayName** =WeekdayName(Weekday(Fields!BirthDate.Value))

Returns name of the day of week

**Year** =year(Fields!BirthDate.Value)

Returns year of specified date

**Math**

**Abs** Returns the absolute value

=Abs(-2.36)

**BigMul** Returns multiplication value of two specified numbers

=BigMul(2,3)

**Ceiling** Returns next highest value

=Ceiling(2.67)

**Cos**

=Cos(2.33)

Returns cos value for specified number

**Cosh**

Returns hyperbolic cos value

=Cosh(2.33)

**DivRem**

=DivRem(23,2,5)

**Fix**

=Fix(23.89)

Returns integer portion

**Floor**

=Floor(24.54)

Returns largest integer

**Int**

=Int(24.78)

Returns integer portion of a number

**Log**

=Log(24.78)

Returns logarithm value

**Log10**

=Log10(24.78)

Returns the base 10 logaritm value

**Max**

=Max(Fields!EmployeeID.Value)

Returns larger value in the specified values

**Min**

=Min(Fields!EmployeeID.Value)

Returns smaller value in the specified values

**Pow**

=Pow(Fields!EmployeeID.Value,2)

Returns power of value for specified number

**Rnd**

=Rnd()

Returns a random number

**Round**

=Round(43.16)

Returns rounded value to the nearest integer

**Sign**

=Sign(-34534543)

**Sin**

=Sin(Fields!EmployeeID.Value)

Returns the sin value

**Sinh**

=Sinh(Fields!EmployeeID.Value)

Returns the hyperbolic sin value

**Sqrt**

=Sqrt(Fields!EmployeeID.Value)

Returns square root value

**Tan**

=Tan(Fields!EmployeeID.Value)

Returns the tan value

**Tanh**

=Tanh(Fields!EmployeeID.Value)

Returns the hyperbolic tan value

**Inspection**

**IsArray**

=IsArray(Fields!EmployeeID.Value)

Returns a boolean value indicating whether the specified object is array or not

**IsDate**

=IsDate(Fields!BirthDate.Value)

Returns a boolean value indicating whether the specified object is Date or not

**IsNothing**

=IsNothing(Fields!EmployeeID.Value)

Returns a boolean value depends on specified object is Nothing or not

**IsNumeric**

=IsNumeric(Fields!EmployeeID.Value)

Returns a boolean value depends on specified object is Numeric value or not

**Program Flow**

**Choose**

=CHOOSE(3, “Red”, “Yellow”, “Green”, “White”)

Returns a specific value using index in a list of arguments

**IIf**

=IIF(Fields!EmployeeID.Value>10,”Yes”,”No

Returns any one value depends on condition

**Switch**

=Switch(Fields!EmployeeID.Value<10,”Red

Fields!EmployeeID.Value>10,”Green

Evaluates list of expressions

**Aggregate**

**Avg**

=Avg(Fields!EmployeeID.Value)

Returns average value for all specified values

**Count**

=Count(Fields!EmployeeID.Value)

Returns count of all specified values

**CountDistinct**

=CountDistinct(Fields!EmployeeID.Value)

Returns count of all distinct values

**CountRows**

=CountRows()

Returns count of rows

**First**

=First(Fields!EmployeeID.Value)

Returns first for all specified values

**Last**

=Last(Fields!EmployeeID.Value)

Returns last for all specified values

**Max**

=Max(Fields!EmployeeID.Value)

Returns max for all specified values

**Min**

=Min(Fields!EmployeeID.Value)

Returns min for all specified values

**StDev**

=StDev(Fields!EmployeeID.Value)

Returns standard deviation value

**StDevP**

=StDevP(Fields!EmployeeID.Value)

Returns Population standard deviation value

**Sum**

=Sum(Fields!EmployeeID.Value)

Returns sum of all values

**Var**

=Var(Fields!EmployeeID.Value)

Returns variance of all values

**VarP**

=Var(Fields!EmployeeID.Value)

Returns population variance of all values

**RunningValue**

=RunningValue(Fields!EmployeeID.Value,sum,nothing)

Returns running aggregate of the specified

expression

**Financial**

**DDB** DDB (Double Declining Balance) method computes depreciation of an asset for a specified period.

Syntax: DDB (Cost, Salvage, life, period, factor)

**FV** FV (Future Value) of an investment based on periodic, constant payments and a constant interest rate.

Syntax: FV (rate, nper, pmt, pv, type)

**IPmt** IPmt (Interest Payment) for a given period for an investment based on periodic, constant payment and a constant interest rate

IPMT (rate, per, nper, pv, fv, type)

**IRR** IRR (Interest Rate of Return) for a series of cash flows represented by the numbers in values.

IRR(values,guess)

**MIRR** MIRR ( Modified internal rate of return ) for a series of periodic cash flows

MIRR(values,finance_rate,reinvest_rate)

**NPer** Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

NPER (rate, pmt, pv, fv, type)

**NPV** Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).

Syntax: NPV(rate,value1,value2, …)

**Pmt** Calculates the payment for a loan based on constant payments and a constant interest rate.

PMT(rate,nper,pv,fv,type)

**PPmt** Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

PPMT(rate,per,nper,pv,fv,type)

**PV** Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.

PV(rate,nper,pmt,fv,type)

**Rate** Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions.

RATE(nper,pmt,pv,fv,type,guess)

**SLN** Returns the straight-line depreciation of an asset for one period.

SLN(cost,salvage,life)

**SYD** Returns the sum-of-years’ digits depreciation of an asset for a specified period.

SYD(cost,salvage,life,per)

**Conversion**

**CBool** Convert to boolean

=CBool(fields!EmployeeID.Value)

**CByte** Convert to byte

**CChar** Convert to char

**CDate** Convert to date

**CDbl** Convert to double

**CDec** Convert to decimal

**CInt** Convert to integer

**CLng** Convert to long

**CObj** Convert to object

**CShort** Convert to short

**CSng** Convert to single

**CStr** Convert to string

**Fix** =Fix(32.342143)

Returns integer portion of a number

**Hex** =Hex(Fields!EmployeeID.Value)

Returns a hexadecimal value of a number

**Int** =Int(43.44)

Returns integer portion of a number

**Oct** =Oct(Fields!EmployeeID.Value)

Returns a octal value of a number

**Str** =Str(Fields!EmployeeID.Value)

Returns string value of a number

**Val** =Val(“32.43”)

Returns numeric value in string format

**Miscellaneous**

**Previous** =Previous(Fields!EmployeeID.Value)

Returns the previous value