Grouping by week and manipulating headers

[infinitizon]

thread767-438413

Hi all,

I have a report (created from Crystal reports in VS2008) with a date field and then amounts thus:

Creation Date   |   Total Cost  |  0-7 days    |  8-30 days    |  >30 days
 11-Jun-2012    |   70,314.51   |   0.00       |   70,314.51   |   0.00
 11-Jun-2012    |   46,307.42   |   0.00       |   46,307.42   |   0.00
 11-Jun-2012    |   79,503.50   |   0.00       |   79,503.50   |   0.00
 11-Jun-2012    |   113,817.12  |   0.00       |   113,817.12  |   0.00
 11-Jun-2012    |   106,234.20  |   0.00       |   106,234.20  |   0.00
 11-Jun-2012    |   10,162.43   |   0.00       |   10,162.43   |   0.00
 05-Apr-2012    |   14,931.30   |   0.00       |   14,931.30   |   0.00
 12-Jun-2012    |   70,269.44   |   70,269.44  |   0.00	       |   0.00
 12-Jun-2012    |   207,047.36  |   207,047.36 |   0.00	       |   0.00
 12-Jun-2012    |   207,047.36  |   207,047.36 |   0.00	       |   0.00
 31-May-2012    |   330,581.16  |   330,581.16 |   0.00	       |   0.00
 08-Jun-2012    |   10,500.00   |   10,500.00  |   0.00	       |   0.00
 12-Jun-2012    |   11,045.32   |   11,045.32  |   0.00	       |   0.00
 12-Jun-2012    |   132,985.29  |   132,985.29 |   0.00	       |   0.00
 12-Jun-2012    |   8,750.97    |   8,750.97   |   0.00	       |   0.00
 12-Jun-2012    |   4,586.80    |   4,586.80   |   0.00	       |   0.00

I also have a discreet string parameter with two options "by value" or by records".

Now what I want is that the field are grouped by week, so I did a formular like so:

datevar Sunday;
datevar Saturday;
stringvar week;
sunday := dateserial(year({useWipByAgeReport.Creation Date}),month({useWipByAgeReport.Creation Date}),day({useWipByAgeReport.Creation Date})-((datepart("w",{useWipByAgeReport.Creation Date},1)))+1);
saturday:=dateserial(year({useWipByAgeReport.Creation Date}),month({useWipByAgeReport.Creation Date}),day({useWipByAgeReport.Creation Date})+(7-((datepart("w",{useWipByAgeReport.Creation Date},1)))));
week := "Week " & sunday & " - " & saturday

What I want however is that the group heading have a serial number for weeks e.g week 1, Week 2 and for weeks that are skipped (maybe because there is no posting on that week), it should still record for that week as "Week x" (Where x is the serial number for the week).

Similarly, if the parameter "by value" is picked, then, It gives the group total, otherwise, give the total count of records in the group

For more information, please ask.

Thanks a whole lot in advance.

 

[hilfy]

In order to get all dates, regardless of whether they have data, you'll need some sort of calendar table that contains all of the dates. There is no way I know of to do this without something like that. I've seen this done a number of ways. My favorite is a table that has this format:

PERIOD_TYPE varchar(7)
START_DATE date
END_DATE date

PERIOD_TYPE has the following possible values: DAY, WEEK, MONTH, QUARTER, YEAR.

You would then join from this calendar table to your data with a left outer join and the selection criteria that {CALENDAR.PERIOD_TYPE} = 'WEEK'. This way you also won't have to use the long formula for your group, you could use something like this instead:

'Week ' + ToText(DatePart('ww', {CALENDAR.PERIOD_TYPE}) + ' ' + ToText({CALENDAR.START_DATE}, 'dd/MM/yyyy') + '-' + ToText({CALENDAR.END_DATE}, 'dd/MM/yyyy')

This will base the week number on which week it is for the year. If you want to use the week number on the report, you could create a running total that does a distinct count of {CALENDAR.START_DATE} and use that instead of the "DatePart" syntax above.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[infinitizon]

Thanks Hilfy,

This looks nice to get the week number, however, if the user picks a date parameter starting from 12-Feb-2012 for instance, the report will show starting from "Week 8 ..." instead of "Week 1..." that I want. So I still dont get what I'm looking for

 

[hilfy]

See my last paragraph - set up a running distinct count of the week start date. I think you can use that running count in your group.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[hilfy]

Also, if your purpose in adding the week number is just to get the dates sorted correctly, you could also convert the start date to a string with the format 'yyyyMMdd', group on that, and then use your label in the group header section to correctly identify it.

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[infinitizon]

thanks hilfy, but I solved it another way.

 

[lbass]

Please explain your solution.

-LB

 

[infinitizon]

Well lbass, I just convinced the manager he does not need the header showing the week numbers.

So I grouped by week, and changed the title with the formular

saturday:=dateserial(year({useWipByAgeReport.Creation Date}),month({useWipByAgeReport.Creation Date}),day({useWipByAgeReport.Creation Date})+(7-((datepart("w",{useWipByAgeReport.Creation Date},1)))));
week := "As at" & saturday

So it displays "As at 04-Feb-2011