Solved

Aggregate values by week within each month

Posted on 2014-01-21
5
952 Views
Last Modified: 2014-01-22
Maybe I'm making this harder than it has to be.  I am simply trying to create a monthly report subtotaled by week.  I created a group and am using the Date field in an expression.  I've tried both ways below.  The first code is going to keep counting weeks successively instead of only within each month (I think).  The second code gives me (for the month to date of January), January Week 1, January Week 1, January Week 2 and January Week 3.  So it isn't grouping the weeks correctly.  Does anyone see anything obvious I am doing wrong, or does anyone have code that works for grouping by week within each month?

Code 1:  ="Week # " & DatePart(DateInterval.WeekOfYear, Fields!Date.Value)

Code 2:  =Format(Fields!Date.Value, "MMMM") & " Week " &
(Int(DateDiff("d", DateSerial(Year(Fields!Date.Value),
Month(Fields!Date.Value),1), Fields!Date.Value)/7)+1).ToString
0
Comment
Question by:fabi2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39799096
I don't think there's a specific function that extracts the week numbers for a month only, so in that case you'd have to "calculate" the week numbers. And this is probably more easily done in your SQL script than in your report itself.
I don't have the time to write out an example, but you easily calculate the week number within a month for a given day by dividing the day by 7 and rounding up to the nearest integer. For example, day 12 would fall in the second week. 12 / 7 = 1.714285.... which rounds up to 2.
Put that in your SQL query and use that value in your report group.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39799218
As far as I understood you want to show a table with Month as top group and WeekNumber as subgroup, right?  Here's how I would do that:

First add a new calculated field to the dataset.  Call it WeekNumber and use your first expression:

=DatePart(DateInterval.WeekOfYear, Fields!Date.Value)

If Month is not a separate field I'd create a calculated field for that as well.  Depending on the requirements (is the report showing data for multiple years?) you may need to take year into account, for instance 201312 would be December 2013.

Secondly, set up table groupings using Month for the top group and WeekNumber for the subgroup.

Now you can use expressions like Sum(Fields!SomeNumber.Value) in the WeekNumber level cells to get your totals by week.

If the above is not what you're looking for, could you post a simple mockup of expected layout?
0
 
LVL 1

Author Comment

by:fabi2004
ID: 39800071
Kvwielink, I think that what you described is what I'm trying to do in my "code 2".  But my code is not working correctly.  Maybe it's because I'm missing a calculated field like you mention.  Where would I add that field, in the report or in the query?

Valentino, the problem I am having with solutions like that is that the week number will coontinue to increase throughout the year.  I want the week numbering to start over each month at week 1.  I will post a mockup like you suggest and maybe the query and report fields code too.

Thanks so much for answering.
0
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 39800172
I would definitely add this as an extra column in your sql query. It will be faster.
You could use:

Select ceiling(datepart(day, yourDateField) / 7) as Week

This would start with week 1 for every new month. Use the new column in your report for grouping.
0
 
LVL 1

Author Closing Comment

by:fabi2004
ID: 39801258
Thank you!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 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