SSRS Expression Cheat Sheet

Home Resources SSRS Expression Cheat Sheet
Problems Expression
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,"-","")