Display Month Name And Year Instead Of Complete Date in SSRS

Posted on 2012-09-07
Last Modified: 2012-12-02
Hi All,
           I have a sample data like this..I have added the cases for the particular worker in the table..My question is that when displaying the cases  I need to subtotal them by Month and Year. For example,  instead of date 2011-04-30 I have to April 2011... and have a subtotal for April 2011 ...can anyone please help me with this...?

312   KRISTI  WHITE                 865400          2011-04-30   2
312   JOANN  BLACK                1000264311   2011-04-30   3
312   KRISTI  BLUE                   1000430815   2011-04-30   1
Total for April 2011:                                                           6

312   HELEN  GREEN              1000660614     2011-05-30   1
312   JOAN WHITE                  1002371318      2011-05-30   2
Total for May 2011:                                                              3

312   ELLA RED                       2003722520     2011-06-03   4
Total for June 2011:                                                             4

Is there a way to achieve this in SSRS?
Please note that the date is only one field. and I can't seperate it into month/day/year as I can't touch the stored procedure.
Question by:echorosz
    LVL 37

    Accepted Solution

    Given the fact that you can't change the stored proc, another option that you have is adding a calculated field to your dataset.  To do that, right-click the dataset and select "Add Calculated Field".  Give it a name, something like Period or perhaps GroupingPeriod, and use an expression such as:

    =DatePart(DateInterval.Year, Fields!OrderDate.Value) & DatePart(DateInterval.Month, Fields!OrderDate.Value)

    That gives you a field to group on.  You could add an additional calculated field to be displayed, containing a result such as "April 2011".  Have a look at the MonthName function if you'd like that.

    Here's an article about calculated fields with a screenshot on how to add them:
    LVL 25

    Expert Comment

    Just change the textbox property where you are denoting the value as the below expression in the expression field.
    =MonthName(Month(YourDate)) + " " +Year(YourDate).ToString()
    LVL 21

    Expert Comment

    by:Alpesh Patel
    DAte Foramt

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now