MTD, YTD, LastFullMonth Prompt and Parameter 1

[swc123]

Hi,

Is there anyway to prompt for MTD/YTD (and others in the list)? I keep seeming to come up with issues. I can't enter MTD/YTD in a date range parameter but if I create a string parmameter I am unable to use it against the db date field in the record selection. (eventually I'd love to add actual date range - MTD, YTD, or from/to but will take things one step at a time)

Thanks

 

[synapsevampire]

I sometimes do this to provide users with a friendly prompt:

I create 3 parameters, a select from a list of predefined ranges as text, or enter a custom set of dates:

I use Start and End date formulas to do this, and reference them in the Record selection formula. This example has multiple periods, inclusive of using a picklist to select the range of interest for canned ranges, and allowing for a custom date range which is entered in another parameter:

////////////////////////////////////////////////////////////////////////////////////
// Starting date of the date range to be used against the column: {LOCALTIME}
// Constructed specifically in this manner to allow for pass through SQL
// Version 2
// Kai Molvig - 5/2/2002
////////////////////////////////////////////////////////////////////////////////////

// If Yesterday
If {?Predefined Date Range} = "Yesterday" Then
DateTime(Year(CurrentDate-1),Month(CurrentDate-1),Day(CurrentDate-1),0,0,0)

// If Last Week
Else
If {?Predefined Date Range} = "Last Week" Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),0,0,0)-DayOfWeek(CurrentDate)-6

// If Last Month
Else
If {?Predefined Date Range} = "Last Month" Then
If Month (CurrentDate) = 1 Then
DateTime((Year(CurrentDate)-1),12,1,0,0,0)
Else
DateTime(Year(CurrentDate),Month(CurrentDate)-1,1,0,0,0)

// If Last Quarter
Else
If {?Predefined Date Range} = "Last Quarter" Then
If (Month(CurrentDate) < 4) Then
DateTime(Year(CurrentDate)-1,10,1,0,0,0)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)+2)/3)-5,1,0,0,0)

// If Last Year
Else
If {?Predefined Date Range} = &quot;Last Year&quot; Then
DateTime(Year(CurrentDate)-1,1,1,0,0,0)

// If Current Week
Else
If {?Predefined Date Range} = &quot;Current Week&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),0,0,0)-DayOfWeek(CurrentDate)+1

// If Current Month
Else
If {?Predefined Date Range} = &quot;Current Month&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),1,0,0,0)

// If Current Quarter
Else
If {?Predefined Date Range} = &quot;Current Quarter&quot; Then
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)-1)/3)+1,1,0,0,0)

// If Current Year
Else
If {?Predefined Date Range} = &quot;Current Year&quot; Then
DateTime(Year(CurrentDate),1,1,0,0,0)

// If Today
Else
If {?Predefined Date Range} = &quot;Today&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),0,0,0)

// If Custom Time Period
Else
If {?Predefined Date Range} = &quot;Custom Date Range&quot; Then
Minimum({?Custom Date Range})

// This code should never be invoked
Else
CurrentDate+1

////////////////////////////////////////////////////////////////////////////////////
// Ending date of the date range to be used against the column: {LOCALTIME}
// Constructed specifically in this manner to allow for pass through SQL
// Version 2
// Kai Molvig - 5/2/2002
////////////////////////////////////////////////////////////////////////////////////

// If Yesterday
If {?Predefined Date Range} = &quot;Yesterday&quot; Then
DateTime(Year(CurrentDate-1),Month(CurrentDate-1),Day(CurrentDate-1),23,59,59)

// If Last Week
Else
If {?Predefined Date Range} = &quot;Last Week&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),23,59,59)-DayOfWeek(CurrentDate)

// If Last Month
Else
If {?Predefined Date Range} = &quot;Last Month&quot; Then
If Month (CurrentDate) = 1 Then
DateTime(Year(CurrentDate)-1,12,1,23,59,59)
Else
DateTime(Year(CurrentDate),Month(CurrentDate),1,23,59,59)-1

// If Last Quarter
Else
If {?Predefined Date Range} = &quot;Last Quarter&quot; Then
If Month (CurrentDate) < 4 Then
DateTime(Year(CurrentDate)-1,12,1,23,59,59)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)-1)/3)+1,1,23,59,59)-1

// If Last Year
Else
If {?Predefined Date Range} = &quot;Last Year&quot; Then
DateTime(Year(CurrentDate)-1,12,31,23,59,59)

// If Current Week
Else
If {?Predefined Date Range} = &quot;Current Week&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),23,59,59)-DayOfWeek(CurrentDate)+7

// If Current Month
Else
If {?Predefined Date Range} = &quot;Current Month&quot; Then
If Month (CurrentDate) = 12 then
DateTime(Year(CurrentDate),12,31,23,59,59)
Else
DateTime(Year(CurrentDate),Month(CurrentDate)+1,1,23,59,59)-1

// If Current Quarter
Else
If {?Predefined Date Range} = &quot;Current Quarter&quot; Then
If Month (CurrentDate) > 9 Then
DateTime(Year(CurrentDate),12,31,23,59,59)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)+2)/3)+1,1,23,59,59)-1

// If Current Year
Else
If {?Predefined Date Range} = &quot;Current Year&quot; Then
DateTime(Year(CurrentDate),12,31,23,59,59)

// If Today
Else
If {?Predefined Date Range} = &quot;Today&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),23,59,59)

// If Custom Date Range
Else
If {?Predefined Date Range} = &quot;Custom Date Range&quot; Then
dateadd('s',-1,(Maximum({?Custom Date Range})+1))

// This code should never be invoked
Else
CurrentDate-1


// Record Selection Formula:
(
({SOL_VW_RPT_CLASS_SUM_DUR.LOCALTIME} >= {@Date Range Start})
and
({SOL_VW_RPT_CLASS_SUM_DUR.LOCALTIME} <= {@Date Range End})
)

and

<whatever else you have>

I think that this version had the bugs ironed out.

-k

 

[vidru]

That's pretty handy, k. *

-dave

 

[swc123]

Thanks K / synapesvampire.

Unfortunate CR doesn't do this for you given their MTD, YTD, other ranges. That will come with dynamic pull downs for parameters.

thanks again

 

[synapsevampire]

SWC: I've no idea what you're trying to say with:

&quot;Unfortunate CR doesn't do this for you given their MTD, YTD, other ranges. That will come with dynamic pull downs for parameters.&quot;

Crystal has numerous functions for date ranges, including monthtodate and yeartodate, and it will pass to the database in most instances.

Dynamic parameters would certainly be nice...

-k

 

[swc123]

K,

Basically that while they do indeed have the MonthToDate, etc. and they can easily be used in the record selections, it would be great to be able to easily prompt for them and use directly. (instead of having to convert using DateTime)

Your example hits the mark. I've already added it to my reference library of handy tools.

Thanks again.

 

[synapsevampire]

I see, yeah, with CR 8.5 *sometimes* a record selection formula of {table.date} in monthtodate did not pass if you have a datetime, but it does in most cases, so don't discount it's use.

But you've hit upon why I spent many a late night hammering this stuff out thru trial and screaming obscenities on the wind at the Crystal developers, it's part voodoo getting things to pass when you have > 10 parameters with complex rules, but so far I've never been stumped (providing I have access to the database side for some things).

Glad it worked out.

-k