Display Month Name And Year Instead Of Complete Date in SSRS

Posted on 2012-09-07
Medium Priority
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 display...like 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

ValentinoV earned 2000 total points
ID: 38379085
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: http://blog.hoegaerden.be/2011/03/07/cascading-calculated-fields-ssrs/
LVL 25

Expert Comment

ID: 38379137
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
ID: 38382084
DAte Foramt

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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