A Weekly Group ... [please read for detail...!!!]

[SQLRory]

Hello,

I am using Crystal V11 on a report which has a start and end date. Using these dates I want the report to automatically generate groups based up on these dates, one for every week elapsed between the dates.

For example:
20080101 to 20080131 would create

Grp 1 Week1 (20080101 to 20080106)
Grp 2 Week2 (20080107 to 20080113)
Grp 3 etc
Grp 4 etc
Grp 5 etc

These need to be calender weeks i.e starting on a Monday and ending on a Sunday

The reason for this is that I need to produce a figure for each group which will calculate a value using a formula field I will create.

Does anyone have a heads up please?

Thanks in advance
Rory

 

[lbass]

Create a formula like this and insert a group on it:

{table.date}-dayofweek({table.date})+ 2

Make sure it prints on change of day.

Note that you will only have one group, but multiple group instances--one per week.

-LB

 

[SQLRory]

Thanks lbass,

The groups are not related to the data as such because the report uses a semi complex select expert formula when selecting records. I have put the select expert statement below:

(({Command.Client Start Date} in {@StartDate} to {@EndDate}) or 
({Command.Client Start Date} < {@StartDate} and (ISNULL({Command.Client Leave Date}) or {Command.Client Leave Date} > {@startdate})))

And for each of the groups created I want to use a formula field which would run against the data the same as above but substituting the

@startdate for the first day is Grp x (Monday) and
@enddate for the last day in Grp x (Sunday)

which would look like:

(({Command.Client Start Date} in [b]{Monday of Grp x}[/b] to [b]{Sunday of Grp x}[/b]) or 
({Command.Client Start Date} < [b]{Monday of Grp x}[/b] and (ISNULL({Command.Client Leave Date}) or {Command.Client Leave Date} > [b]{Monday of Grp x}[/b])))

But somehow the formula field would need to produce a count of records I am not sure how to do this. This formula field would then produce a single figure which would equal the amount of records which meet the criteria in the formula field in the entire data returned by the original parameters @startdate and @enddate. I expect a final product something like this.

Group1 Week1 100
Group2 Week2 102
Group3 Week3 108
Group4 Week4 101
Groupx Weekx 102

Does this make much sense? So in theory the group actually don't have to group data and are needed only to generate the start and end values of the weeks that can be used within the formula field.

I'll be honest I haven't used the 'when printing records' technique to program a group before therefore I may have some difficulty if I have to use this but I'm sure I could figure it out once pointed in the right direction.

Thanks in advance
Rory

 

[lbass]

Please explain what date fields you have to work with. What date are you planning to use to create these weeks? Are you saying that you ONLY have start and leave dates and that you want to create week periods even though the actual dates might not exist in the database?

-LB

 

[SQLRory]

Sorry to have complicated things.

My report already returns data using the @startdate and @enddate fields, the parameters selected when running the report. This then returns x rows of data using the

(({Command.Client Start Date} in {@StartDate} to {@EndDate}) or
({Command.Client Start Date} < {@StartDate} and (ISNULL({Command.Client Leave Date}) or {Command.Client Leave Date} > {@startdate})))

formula in the select expert

I will list below the dates which are used in the report:

@startdate parameter
@enddate parameter
command.client start date field in report
command.client leave date field in report

The Weekly Groups I would like to create would be the weeks between @startdate and @enddate and the Monday of Grp x and Sunday of Grp x fields are desired fields which would be the Monday and Sunday value of each week we create.
I would then like to use these values within an additional formula field to calculate a value for each week.

Please let me know if you require anything else.

Thanks in advance
Rory

 

[lbass]

Please try to answer my specific questions. Please show the content of {@startdate} and {@enddate}.

-LB

 

[SQLRory]

Hello,

The dates I am planning to use to create these weeks are the @startdate and @enddate. They are simply date values selected by the user.

"Are you saying that you ONLY have start and leave dates and that you want to create week periods even though the actual dates might not exist in the database?" Yes that is exactly right.

Thanks in advance
Rory

 

[lbass]

Why are these dates appearing with @ signs if they are parameters? Are you referencing a stored procedure somehow in your command?

-LB

 

[lbass]

Can you clarify whether the report would always be limited to a month period? I have a specific method that should work in that case.

-LB

 

[SQLRory]

Hello,

I have a simple parameter titled date range that has values such as:
Last Week
Last Month
This Week
This Month
Today

and the @startdate and @enddate are formula fields which automatically calculate the date required based upon the option selected from the list of examples above.

Therefore the @startdate and @enddate are still the parameter values of the report but are just created automatically by date range parameter.

So in theory if it makes things a little easier just assume they are {?startdate} and {?enddate}

The report would need to be ran for any given range potentially 2 years +.

Thanks
Rory

 

[lbass]

I don't see why you didn't just show the content of the formulas to begin with. Anyway, the method I had in mind won't work with such a wide date range.

You need to create a dates table that ideally you then build into the command, but if you can't do that, you could link the table to the command with a left join FROM the dates table to the command. The date selection criteria should be used on the new dates table. There is an faq on how to create a dates table, or you could simply create a table in Excel where you add a date and then use the fill function to increment the date for as many dates as you think you might need.

Then you would use the formula that I first gave to create the weeks group, using the new dates table field in the formula.

-LB

 

[Charliy]

Some of these could have been combined, but I like keeping them as separate formulas for clarity.

@BDATE is the begin date

@BDOW
BeforeReadingRecords ;
totext({@bdate},'ddd')

@DTFS
// Days Till First Saturday
BeforeReadingRecords ;
if {@bdow} = 'SUN' THEN 7
ELSE
if {@bdow} = 'MON' THEN 6
ELSE
if {@bdow} = 'TUE' THEN 5
ELSE
if {@bdow} = 'WED' THEN 4
ELSE
if {@bdow} = 'THU' THEN 3
ELSE
if {@bdow} = 'FRI' THEN 2
ELSE
if {@bdow} = 'SAT' THEN 1

@CALGRP - the formula to group on
truncate((((({table.date}-{@bdate})+1)-({@dtfs}+1))/7)+1)