Print Page | Close Window

week grouping

Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11468
Printed Date: 18 May 2024 at 3:36am


Topic: week grouping
Posted By: Gian Luca
Subject: week grouping
Date Posted: 20 Oct 2010 at 5:11am
Hi all,
Pretty new at CR. I need to group all payables by due week. All i have - per each record - is the due date (per day, not per week)

For instance I need a report like this:

                 year 2010                                         year 2011
                 week1       week2     week3   etc etc    week 1      week 2   etc   etc
supplier 1    $ 100       $56        $ 99                       $0          $1         $0
supplier 2    $   0        $ 876      etc   etc

I saw CR has  Year/Month/day functions but not weeks. How can I do that?

Thanks a lot.
Cya.


Replies:
Posted By: lockwelle
Date Posted: 21 Oct 2010 at 3:36am
if you can use stored procs, I would say add a column to the result set for the year/week number and use the DATEDIFF() to calculate the nubmer of days from a given start date (which if is calendar years, could be 1/1/YEAR(payableDate)).
 
Now that you have the number of days, for simplicity, add the 7 - (dayCount mod 7) so that all the days are in increments of 7, then divide them all by 7 to get the week number.
 
You might be able to do this in CR, but it would be difficult as you need to alter a column (or change the data in it so that it works for you)...stored procs are always the easiest.
 
HTH


Print Page | Close Window