Date Parameters 2

[ajstickland]

Hi

I’m using Crystal XI.

I have a main report that a user selects a date range via two date parameters (end and start) and a different report which I will be using as a sub report that is based on weekly data (this cannot be changed). For these to match up I need to change the to and from parameters on the main report to return the first date and last date of a whole week or if the range is more than one week the first date of the first week and the last date of the last week.

E,g. If user selects 07/01/2008 – 18/01/2008 this would pass 07/01/2008 – 18/01/2008 into the main report and wk2 – wk3 into the sub report. I.e. so the date range ties up.

However if the user selects a part week I would like to know how to:

1)Warn the user that the date ranges won’t tie up and the report will not be accurate

2)Force the user to select either the first date (from) and last date of the week (to) or override by passing the parameter into the report and calculating the first date (From) and the last date of the week (To).

E.g. If user selects 07/01/2008 – 15/01/2008 this would be corrected or the user would only be allowed to select 07/01/2008 – 18/01/2008.

I would prefer to use a calendar as using a drop down list I think would be too confusing for the user.

Have you got any idea how to do this or does crystal not let you do this?

 

[CR85user]

The warning can be generated by a conditionally suppressed header containing the warning which is suppressed if the date format is matching your criteria (in this case full weeks).

I would consider the approach of the user selecting a beginning date and then from a drop down list how many weeks of data.

It really depends on typical usage.

Regards the subreport linking this is easier once we confirm the method of parameter selection - Are you still definite on your currently described route of calander entries for both start and end dates?

'J

 

[lbass]

You could do the conversion in a formula that you then use for linking, but I'm unclear on what your "week" is--Sunday to Saturday or? It looks like you want to display Monday - Friday, so how would you know which week to assign if a parameter selection is on a Sat or Sun?

-LB

 

[ajstickland]

Thanks CR85user and lbass

You're quite correct I should define a whole week. This will be Sunday to Saturday.

I am halfway there as on the sub report I can already return the week if a user selects any date between Sunday and Saturday.

DatePart("ww",{?Param_Start_Week},crFirstFourDays);
DatePart("ww",{?Param_End_Week},crFirstFourDays);

The issue is on the main report we have a field 'calls_modified' (which sows the date the engineer goes out and works ona call) that is a date field and a param is passed into the report to filter this.

I could turn this into weeks by adding the above DatePart,ww formula in the report that converts this into a week. This would then tie in with the sub report that returns the planned hours.

However the management will no doubt want to be more flexible in what they can ask for I.e. break down by days and not weeks. If this is the case then the values retrieved from the sub report will not tie up.

I did some thinking overnight and I think by adding a boolean parameter I could overcome this. The param will be set to:

Yes - Lets the user enter a date and the formula then converts to weeks.

No - Warns the user that they will be potentially entering not complete weeks and therefore the values returned in the sub rport will not tie up.

Does this sound plausible and is the a more efficient way of doing it.

thanks for your help!

 

[lbass]

You could just create two formulas in the main report:

//{@minrange}:
minimum({?daterange})-dayofweek(minimum({?daterange}))+1

//{@maxrange}:
maximum({?daterange})-dayofweek(maximum({?daterange}))+7

Then set the record selection to:

{table.date} >= {@minrange} and
{table.date} < {@maxrange} + 1

Then go to edit->subreport links and select the two formulas as linking fields for the sub. Then go into the sub record selection formula and change it to read:

{table.date} >= {?pm-@minrange} and
{table.date} < {?pm-@maxrange} + 1

-LB