AdSense

Wednesday, 25 September 2013

Lifesaver - SSRS Quick fixes

Lifesaver - SSRS Quick fixes
How many times have you thought in development that "This must be a common scenario" and then searched all over the place to find the solution for it. I am listing down such scenarios and fixes for those scenarios which are very frequent in the software industry. This will help not only to jumpstart but save your time afterwards durng development. I will be trying to cover as many areas as I can under the heading "Lifesaver".

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

Expression on the parameter default value:
=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)
Output: 14/7/2013 12:00:00 AM

Return first day of current Month (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)
Output:11/1/2010 12:00:00 AM

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

Expression on the parameter default value:
=DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)
Output: 1/1/2010 12:00:00 AM

Return period over period (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)
Output:10/9/2010 12:00:00 AM

Return current month name

Expression in Text Box:
=MonthName(Month(Today()))
Output:July

Uppercase fields

Expression in Text Box:
=UCASE(Fields!FieldName.Value)
Output:UPPERCASE

Convert text to proper case or InitCap (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,"-","")