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. 

3 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Thanks for ur useful information

    ReplyDelete
  3. This is a good article & good site.Thank you for sharing this article. It is help us following categorize:
    healthcare, e commerce, programming, multi platform,inventory management, cloud-based solutions, it consulting, retail, manufacturing, CRM, technology means, digital supply chain management, Delivering high-quality service for your business applications,
    Solutions for all Industries,packaged applications,business applications, Web services, data migration
    Business intelligence, Business Development, Software Development etc.


    Our address:
    2002 Timberloch Place, Suite 200
    The Woodlands, TX 77380
    281-364-1799

    prologic-corp

    ReplyDelete

Popular Posts