CODEWITHSUNDEEP
reporting-servicesreportbuilder3.0
adamn11
adamn11

Reputation: 311

Grouping by Dates

I have a report that displays the date on the left column (mm/dd/yyyy) and data that corresponds to each day on the right column. For example:

Date      | Number
1/2/2014  | 10
1/5/2014  | 4
1/17/2014 | 22
2/1/2014  | 2
2/13/2014 | 14
3/3/2014  | 1

How do I group the dates together by months so it will be displayed like this:

Jan 2014  | 36
Feb 2014  | 16
Mar 2014  | 1

Upvotes: 1

Views: 132

Answers (2)

Brennan King
Brennan King

Reputation: 76

First we create a table as you already have.

At the bottom of Report Builder there should be a footer that says: "Row Groups" and your date row should be in there.

Right click on the date group and select group properties. Under general it should already have a group expressions relative to "Date". Click on the Fx button to the right and edit the expression so that it says: =MONTH(Fields!your_column_name_here.Value) instead of 

=Fields!your_column_name_here.Value

That should group by month. If you want the date formatted in a specific manner right click on the text box for [date], go to "text box properties", then "Number", select "Date" from the "category" menu and select the format you are looking for.

Upvotes: 2

Nathan
Nathan

Reputation: 171

Make groupings by = Month(Fields!Date.Value) & Year(Fields!Date.Value)
(use them year first if you want the values to be the sort, for correct ordering)

Then For the labels, you need to use MonthName and the integer of the Month:

=MID(MonthName(Month(Fields!Date.Value)), 1,3) & Year(Fields!Date.Value)

Here I also took the Md of the month name, its first 3 characters, Jan Feb Etc.

In the grouping make your counts =Count(Fields!Item.Value)

Upvotes: 1

Related Questions