[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Aggregate values by week within each month

Posted on 2014-01-21
5
Medium Priority
?
1,182 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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 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