SSRS Expression Cheat Sheet

ProblemsExpression
Return first day of current Week
(ex. Default Start Date parameter to return WTD)

Expression on the parameter default value:

                        
        =DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)
                                
                            

Expression Output Example: 11/7/2010 12:00:00 AM

Return first day of current Month
(ex. Default Start Date parameter to return MTD)

Expression on the parameter default value:

                        
        =DateAdd("d",-(Day(today)-1), Today)
                                
                            

or

                        
        =DateSerial( year(today()), month(today()), 1)                            
                                
                            

Expression Output Example: 11/1/2010 12:00:00 AM

Return first day of current year
(ex. Default Start Date parameter to return YTD)

Expression on the parameter default value:

                        
        =DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)
                                
                            

Expression Output Example: 1/1/2010 12:00:00 AM

Return period over period
(ex. Default date parameters to a rolling year)

Expression on the parameter default value:

Week over Week

                        
        =DateAdd("ww",-1, Today)
                                
                            

Month over Month

                        
        =DateAdd("m",-1,Today)                            
                                
                            

Year over Year

                        
        =DateAdd("yyyy",-1, Today)                            
                                
                            

Expression Output Example: 10/9/2010 12:00:00 AM

Return current month name

Expression in Text Box:

                        
        =MonthName(Month(Today()))
                                
                            

Expression Output Example: November

Uppercase fields

Expression in Text Box:

                        
        =UCASE(Fields!FieldName.Value)
                                
                            

Expression Output Example: NOVEMBER

Convert text to proper case
(ex. 1st letter in each word is uppercase)

Expression in Text Box:

                        
        =StrConv(Fields!FieldName.Value, VbStrConv.ProperCase)
                                
                            
Replace NULL with another value

Expression in Text Box:

                        
        =iif(Fields!FieldName.Value = nothing, "No Value",Fields! FieldName.Value)
                                
                            
Alternating row color (Banding effect)

BackgroundColor property on Text Box:

                        
        =iif(RowNumber(Nothing) Mod 2 = 0, "Silver", "White")
                                
                            
Handling division by zero

Expression in Text Box:

                        
        =iif(Fields!DenominatorField.Value = 0, 0, Fields!NumeratorField.Value/ 
        
        iif(Fields!DenominatorField.Value = 0, 1, Fields! DenominatorField.Value))
                                
                            
security number)

Expression in Text Box:

                        
        =Replace(Fields!EmailAddress.Value,"-","")