Showing posts with label Number to month name. Show all posts
Showing posts with label Number to month name. Show all posts

Wednesday, May 20, 2015

SSRS - How to convert month number into month name

In the real development environment, there are lots of requirements where we need to get the month name from the month number or date field in the SSRS. There are many ways to get the month name from the month number /date which are given below:
  1. By using Format Function
  2. By using Switch Function
  3. By using IIF Function
By using Format function
This function could be very useful if you are pulling the month name from the date field or variable. Here, you need to right click on the Textbox on which you want to display the month name and choose properties. In Textbox properties window, you can do the following things with the Format function as -

<-- It will give you the full name of the month such as January.
=Format(Fields!MonthNumber.Value,"MMMM")  


<-- It will give you the starting three character of the month name such as Jan.    

=Format(Fields!MonthNumber.Value,"MMM")        

By using Switch function
This function comes into the picture if you want to get the month name from the month number. To do this, we need to set the following things in the Textbox properties window as-
<-- It will give you the full name of the month such as January.
=Switch(Fields!MonthNumber.Value = 1, "January", 
                   Fields!MonthNumber.Value = 2, "February",
                   Fields!MonthNumber.Value = 3, "March"................)
Or
<-- It will give you the starting three character of the month name such as Jan.  
=Switch(Fields!MonthNumber.Value = 1, "Jan", 
                   Fields!MonthNumber.Value = 2, "Feb",
                   Fields!MonthNumber.Value = 3, "Mar"................)
By Using the IIF Function
SSRS is fully loaded of lots of function which can be used as per the requirements. This is the lengthy step to get the month name from the month number such as
<-- It will give you the full name of the month such as January.
=IIF(Fields!MonthNumber.Value = 1, "January", IIF(Parameters!Month_Number.Value = 2, "February", ...)))))))))))
Or
<-- It will give you the starting three character of the month name such as Jan. 
=IIF(Fields!MonthNumber.Value = 1, "Jan", IIF(Parameters!Month_Number.Value = 2, "Feb", ...)))))))))))

So, we have a lots of functions which can be used to achieve the requirement. Some of them are very lengthy and need more concentration to apply them.