List of functions used in Dynamics AX 2012 SSRS Reports Expressions

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 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

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.




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…


^ 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
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
+ and & symbols uses for concatenation
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


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(Fields!Price.Value, “#,##0.00”), Format(Fields!Date.Value, “yyyy-MM-dd”)

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

FormatDateTime =FormatDateTime(Fields!BirthDate.Value,Integer)
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

Left Returns left side characters from a string

Len Finds length of a string

LSet Returns some length of a string from left

LTrim Trim left side of a string
=Ltrim(” “&Fields!Title.Value)

Mid Returns characters from the mentioned starting position

Replace Replaces one string with another

Right Returns right side characters from a string

RSet Returns some length of a string from left

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


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

StrReverse =StrReverse(Fields!Title.Value)

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

UCase =Ucase(Fields!Title.Value)

Date & Time

CDate Converts a object into date format

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


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

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

DateValue Returns current date

Day Returns day value from date

FormatDateTime =FormatDateTime(Fields!BirthDate.Value,Integer)
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

Today Returns Current date

Weekday Returns an integer value representing day of week

WeekdayName =WeekdayName(Weekday(Fields!BirthDate.Value))
Returns name of the day of week

Year =year(Fields!BirthDate.Value)
Returns year of specified date


Abs Returns the absolute value

BigMul Returns multiplication value of two specified numbers

Ceiling Returns next highest value

Returns cos value for specified number

Returns hyperbolic cos value


Returns integer portion

Returns largest integer

Returns integer portion of a number

Returns logarithm value

Returns the base 10 logaritm value

Returns larger value in the specified values

Returns smaller value in the specified values

Returns power of value for specified number

Returns a random number

Returns rounded value to the nearest integer


Returns the sin value

Returns the hyperbolic sin value

Returns square root value

Returns the tan value

Returns the hyperbolic tan value

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

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

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

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

Program Flow

=CHOOSE(3, “Red”, “Yellow”, “Green”, “White”)
Returns a specific value using index in a list of arguments

Returns any one value depends on condition

Evaluates list of expressions


Returns average value for all specified values

Returns count of all specified values

Returns count of all distinct values

Returns count of rows

Returns first for all specified values

Returns last for all specified values

Returns max for all specified values

Returns min for all specified values

Returns standard deviation value

Returns Population standard deviation value

Returns sum of all values

Returns variance of all values

Returns population variance of all values

Returns running aggregate of the specified


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.

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

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.

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

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.

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

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

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


CBool Convert to boolean
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


Previous =Previous(Fields!EmployeeID.Value)
Returns the previous value



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s