Design Test Validation and Publish Reports Out Using Microsoft Ssrs

SSRS (SQL Server Reporting Services) – Expressions or Functions used in SSRS - Part 2

Posted Date:

25 May 2011 |Updated:

25-May-2011 | Category: SQL Server | | Member Level: Gold |Points: 15 |

Expressions are usually used for appearance of the data in a report. In this article, i would like explain expressions or functions used in SSRS for each and every field which are inside the report. I would like to share my own findings of expressions or functions, why because, in which expressions I struggled a lot and I had wasted my time.

Expressions or Functions used in SSRS

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, compares 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 deataily

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

Conclusion

Hope useful to you all and Let me know if you not understand or find anything missed

Reference: http://www.dotnetspider.com/resources/42881-SSRS-SQL-Server-Reporting-Services-Manages.aspx

Comments

Design Test Validation and Publish Reports Out Using Microsoft Ssrs

Source: https://www.dotnetspider.com/resources/42874-SSRS-SQL-Server-Reporting-Services.aspx

Share this post