Search This Blog

Friday, October 12, 2012

Expressions or Functions used in AX 2012 SSRS Reports


This post explains all the functions and usage of those functions in developing the reports.

Expressions are usually used for appearance of the data in a report, change properties of the fields, calculate some values and display them in a proper way, compare values between data of fields and then display them. 

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/prabu.thangavelu
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 6/3/1977
1 returns Friday, June 03, 1977
2 returns 6/3/1977
3 returns 12:00:00AM
4 returns 00:00

FormatNumber =FormatNumber(Fields!EmployeeID.Value,2)
Examples: 2.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 yyyy")

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/1977
1 returns Friday, June 03, 1977
2 returns 6/3/1977
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

18 comments:

  1. Hi Krishna,

    Iam developing a ssrs report in ax2012. i have a scenario where i need to show a section of data only once on the first page of the report. I have kept the section in the footer and selected the option "show on first page" on footer properties. But this is not working. Do you have any idea how could i acheive this ?

    ReplyDelete
  2. Hi Krishna

    Iam developing a ssrs report in ax2012. i have a scenario where i need to show a section of data only once on the first page of the report. I have kept the section in the footer and selected the option "show on first page" on footer properties. But this is not working. do you have an idea how could i acheive this ?

    Thanks
    Gangadhar

    ReplyDelete
    Replies
    1. you can write expression in TextBox properties -> Visibility -> Show or hide based on Expression.

      Your write expression like =IIF(Globals!PageNumber = 1,false,true)

      Delete
  3. Hi Krishna, I'm a new developer in SSRS for AX2012. I'm having a problem like this: I have 3 rectangles in a report's header and I want to show first 2 in first page only. So I added an expression in "Hidden" attribute for that 2 rectangles like this: '=iif(Globals.PageNumber = "1",false,true)'. By somehow, the third rectangle always move left, staying about middle of the paper (abt 8cm from left margin) since the second page although I setup attribute "Left" is 13cm. The first page is always correct. If you have any clue, please let me know.
    I'm trying to review some other reports and see they hide some parts without using the "Hidden" attribute, that make me confusing also because I don't know is there any other way to do it. I'm looking forward for your help. Many thanks.

    ReplyDelete
    Replies
    1. Hi Ralph,
      ex- did you place the rectangles like this
      rectangle 1 rectangle 2 rectangle 3.
      If you hide the rectangle 1 and rectangle 2 automatically the rectangle will move to left,
      so you have to create the rectangle3duplicate on rectangle 1 and hide it on page1 and remaining rectangles1,2,3 except duplicate hide it...so that you problem will be solved.

      I hope you got what I am telling. if not
      ping me on krishna.dynamics@gmail.com

      regards,
      krishna.

      Delete
    2. My appreciate for your quick feedback, I duplicated rectangle 1 & 2 and keep them as with white borders then reverse the expression for those new one then it works perfect now. But seem this is quite tricky, I realize that if I setup those rectangles 1 & 2 always invisible then the rectangle 3 is ok. Seem it's a MS bug and occurred in run-time only.
      I'm quite new and taking care of this report from a resigned developer. Your suggestion help me a lot. I'm stucked with it for 2 days already haha. So, thank you a lot for your kindness.

      PS: The developer I mentioned above hide a part in the report body but without using Hidden attribute, it bothers me so far, if you know is there any possible using code to manipulate the layout, please kindly let me know. I'm in debt with you now :)

      Delete
    3. Hi Ralph,
      No we can't manipulate the report layout with code, because now in Ax2012 SSRS reports should be done in designing only, the only option in coding is assigning the values to the table datasource and some fields used for just hiding only if you see the standard Sales Invoice report which will have lot of fields for just to tell the report that it is avialable to show some section or not that was the only way you can write through logic in DP.

      If you have some problems you can share me the screen through and if I could some Idea to solve it.

      you can add my skype from blog


      regards,
      krishna.

      Delete
  4. Hi Krishna,

    I am hiding some fields based on different companies, different companies having same format of reports but some fields are hiding in some companies. so please send the expression of hiding fields for different companies. Please reply me as soon as possible. My mail Id: sushmitha.p17@gmail.com

    Thanks&Regards,
    Sushmitha

    ReplyDelete
    Replies
    1. Hi sushmitha,
      there will be the parameter rendering company name you can use for expression

      for the textbox go to visbility expression and write the expression based on the the IIF(companyName="EC",true,false) like this you can handle the expressions.

      Delete
    2. Thank you so much krishna, My problem is sloved.

      Delete
  5. Hi Krishna

    I have some date fields on the report.
    I have to print the dates according to the regional settings (based on language code). In the textbox properties, i have selected the format as date and it is printing the date according to the language code.
    But if i print multiple reports (of different languages), then the language of first report is getting applied for all the reports.
    If i print the single report then dates are printing according to the language code.The probelm is coming when i print the multiple reports having multiple languages.

    Is there any way that i can format the date in the DP class itself based on the language (coming for that specific record)?

    ReplyDelete
  6. Hi Krisna,

    As I know there is a function LookupSet in SSRS 2008 R2 (http://msdn.microsoft.com/en-us/library/ee240819.aspx). Can we use this function in AX 2012 SSRS Report?rt?

    Trung

    ReplyDelete
  7. Hi Krisna;

    Your document is Excellent.
    I need someone help to pass a parameter from RDP class to SSRS report.

    I need to provide user to select a option (Show Header-footer/Hide Header-Footer) during print SSRS report. By default Header-footer can be Enable.

    I am modifying RDP clas based AX std Reports.

    I'd appreciate your help Krisna.

    Regards.

    Rahman



    ReplyDelete
  8. How will it be in expressions
    if(thisDate == datenull()) or if(thisDate == datemax()) ?

    ReplyDelete
  9. Hi , Krishna,

    This is manikandan am new to ax 2012 , i have a requirement that i need to print the report in pre - printed paper. In the body section i need to make constant table structure for the output data . For Ex : In Sales Order we have 50 lines in a Document. EX: In the body section it ll allow 20 record per page, So that first 2 page ll print 20 rec and 3 page we ll print 10 records. Here my requirement is in the 3 rd page the remaining 10 empty rows has to print with borders to complete the table structure.

    Please help me ... how to do this..

    ReplyDelete
  10. Hi , Krishna

    How can i know if the report is printing, i need to hide an image only when report is printing. i try to use renderReport.Name but is not valid for this version.

    Thanks

    ReplyDelete
  11. Hi Krishna,

    I have a simple requirement in developing SSRS report. I need to display for an amount field all negative values and then all the positive values which is fairly simple achieved by sorting. But I need to subtotal the negative values and sub total the positive values. How do I achieve this?

    it should look like:

    Amount
    -10
    -20
    -30
    Subtotal -60
    20
    40
    50
    Subtotal 110
    GrandTotal 50

    Can you please let me know how to get a subtotal to display the sum of negative values and subtotal of positive value for an amount field.
    Thanks.
    -Smriti

    ReplyDelete
  12. Hi Krishna,
    I want to set report Top/Bottom Margins at run time in ax SSRS 2012. Is there any way we can do it through RDP Classes???

    ReplyDelete

Thanks for visiting my blog,
I will reply for your comment within 48 hours.

Thanks,
krishna.