Solved

Aggregate values by week within each month

Posted on 2014-01-21
5
810 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
  • 2
  • 2
5 Comments
 
LVL 12

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 12

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

16 Experts available now in Live!

Get 1:1 Help Now