Purchase SSRS On-Demand Training

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,"-","")
                        
                    

Digital Transformation

Today’s corporate strategy is focused on transforming their use of technology and data. Pragmatic Works has been the leader in digital and data strategy for over a decade. We partner with you to find atransformation plan and then execute it together.