Advertisements
Home > Business Intelligence, Expressions, SSRS > SSRS Expressions

SSRS Expressions

Very practical expressions, when you create SSRS Reports in your day to day development.

1. Lets start with the easiest of the expressions i.e. To get Today’s date

=Today

2. Get Date +/- a number of days – The following code will get Today’s date -3 days. e.g. If today is the 08/12/2009, it should return 05/12/2009. You can change the -3 to any number and you should get the current date +/- the number specified. The DateAdd function basically adds an interval to a date. Here we add the interval -3 to Today’s date.

=DateAdd(“d”, -3, Today)

3. Get First Day of the Month – This is an extension of the pervious expression, it can be divided into two parts the -1.0 * DatePart(“D”, Today) + 1 returns the number of days from the first day of the month -1 in negative. i.e. If today is the 08/12/2009 it would return the value -7 which is then passed to the function DateAdd to get the first day of the month e.g. 01/12/2009

=DateAdd(“D”, -1.0 * DatePart(“D”, Today) + 1, Today)

4. Get Last Day of the Month – This is almost the same as the last expression, the only difference is we don’t add the +1 which gives us the last day of the last month then pass that date to another DateAdd function to add an extra month.

=DateAdd(“m”, 1, DateAdd(“d”, -1.0 * DatePart(“d”, Today), Today))

5. Get the First Day of the Last Month – This is another variation of the above expressions

=DateAdd(“D”, -1.0 * DatePart(“D”, Today) + 1, DateAdd(“m”, -1, Today))

6. Get the Name of the day – This gets the name for the day that was passed for example if its 08/12/2009 it would display “Tuesday”

=WeekdayName(DatePart(“w”, Today))

7. Check what day it is and display date accordingly (This checks to see if today is a Monday and if it is true then displays today -3 days else displays today -1 day)

=IIF(WeekdayName(DatePart(“w”, Today))=”Monday”,DateAdd(“d”, -3, Today),DateAdd(“d”, -1, Today))

8. Formatting Dates
Get Year – Will display only the year =Format(Today,”yyyy”) Years as 1900-9999
Get Month (without 0 suffix) =Format(Today,”M”) Months as 1-12
Get Month (with 0 suffix) =Format(Today,”MM”) Months as 01-12
Get Month by short Name =Format(Today,”MMM”) Months as Jan-Dec
Get Month by Name =Format(Today,”MMMM”) Months as January-December
Get Date (without 0 suffix) =Format(Today,”d”) Days as 1-31
Get Date (with 0 suffix) =Format(Today,”dd”) Days as 01-31
Get Date by short Name =Format(Today,”ddd”) Days as Sun-Sat
Get Date by Name =Format(Today,”dddd”) Days as Sunday-Saturday
Get Date in dd/mm/yyyy hh:mm:ss tt =Format(cdate(“01/31/2009 16:00:00″),”dd MM yyyy hh:mm:ss tt”) 31/01/2009 08:00:00 PM

9. Convert String to Date – Following example combines string and integer values to form a valid date format

=CDate( “1/” & Parameters!StartMonth.Value & “/” & Parameters!StartYear.Value)

10. SWITCH statement – An alternative to IIF/CASE (Returns a value depending on which condition is true)

=SWITCH(WeekdayName(Fields!Date.Value) = “Monday”,”Blue”,
WeekdayName(Fields!Date.Value) = “Tuesday”,”Green”,
WeekdayName(Fields!Date.Value) = “Wednesday”,”Red”)

11. Format Numbers as Currency – The result for the following will be $1000.00

=FormatCurrency(1000)

12. Convert integer values to string

= CStr(123123)

Thanks
Sandip Shinde

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: