creating paramaters for default dates or selected dates 1

[sniggih]

I have been requested to create a report that will give the user a choice either a default date range of 12 months ending from Current Month - 4 months or give them the ability to select their own date range.

I know I can use a parameter in my select expert and that a parameter field can't return a database or formula-field name.

I know I can create a parameter that has two options Default or Select for example. I can also create parameters for my user selected begin and end dates. Then I can call those parameters in my begin and end date formulas. But how would I use that in the select expert? Can I call a formula in my select expert? Or is there another way to approach this?

Please help!

 

[rosemaryl]

Dear Sniggih:

I would say the answer depends on how you want them to select the date range.

If you only want one parameter, then you could enter values such as:
?ReportRange - Please choose a range of dates for report, String
Value Description
Default Last 12 Mo end Current-4
Last60 Last 60 Days from Current
Last90 Last 90

And so on,


Then in your select statement, You would do a

If ?ReportRange = "Default" then
Date in Date to Date
else if
?ReportRange = to X then Y

and so on.

If you want your users to be able to select actual date ranges then you should do this:

Create a parameter for the type of report
Example

?ReportType
Choose your report Type Default or Specific Date Range:
Standard Range
Specific Range

Then do other parameter(s) for the date range. If you are going to run from the Web, don't use range parameters which are not supported. If not running from the Web you can. Let's assume that you need two dates for the range:

?StartDate
Please choose the Start Date.

?EndDate
Please choose the End Date.

Select Statement:

If uppercase({?ReportType}) = "STANDARD" then
{Table.DateField} in Mydate to Mydate
else
{Table.DateField} in {?StartDate} to {?EndDate}

I hope this was helpful and not confusing. If you need more help, just ask!

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[sniggih]

Thanks Rosemary

I am a little confused...

How do you use those statements in your select expert? (i.e. IF ?ReportRanger = "Default" then Date in Date to Date else if ?ReportRange = to X then Y).

 

[rosemaryl]

Dear Sniggih,

Assuming Crystal 8.0 or higher!

You do them just as stated. If you want you can look at a rather nice report example in the examples that ship with crystal Program Files\Seagate Software\Samples\Reports\Feature Examples\Date Range Formula.rpt

In this report they did this using a switch statement: (Think of ?reference condition as ?Report Range in my example above and ?reference date as the date you want them to use if other than currentdate. If you want to use current date, just replace all the mentions of {?reference date} with CurrentDate or DadaDate as might be your preference:

{Orders.Order Date} in
Switch
(
{?reference condition} = "Aged 0 to 30 days",
({?reference date} - 30) To {?reference date},
{?reference condition} = "Aged 31 to 60 days",
({?reference date} - 60) To ({?reference date} - 31),
{?reference condition} = "Aged 61 to 90 days",
({?reference date} - 90) To ({?reference date} - 61),
{?reference condition} = "All dates from today",
Is >= {?reference date},
{?reference condition} = "All dates from tomorrow",
Is >= ({?reference date} + 1),
{?reference condition} = "All dates to today",
Is <= {?reference date},
{?reference condition} = &quot;All dates to yesterday&quot;,
Is <= ({?reference date} - 1),
{?reference condition} = &quot;Calender 1st half&quot;,
CDate(Year({?reference date}), 1, 1) To CDate(Year({?reference date}), 6, 30),
{?reference condition} = &quot;Calendar 2nd half&quot;,
CDate(Year({?reference date}), 7, 1) To CDate(Year({?reference date}), 12, 31),
{?reference condition} = &quot;Calendar 1st quarter&quot;,
CDate(Year({?reference date}), 1, 1) To CDate(Year({?reference date}), 3, 31),
{?reference condition} = &quot;Calendar 2nd quarter&quot;,
CDate(Year({?reference date}), 4, 1) To CDate(Year({?reference date}), 6, 30),
{?reference condition} = &quot;Calendar 3rd quarter&quot;,
CDate(Year({?reference date}), 7, 1) To CDate(Year({?reference date}), 9, 30),
{?reference condition} = &quot;Calendar 4th quarter&quot;,
CDate(Year({?reference date}), 10, 1) To CDate(Year({?reference date}), 12, 31),
{?reference condition} = &quot;Last 4 weeks to Sunday&quot;,
({?reference date} - 27 - (WeekDay({?reference date}) - 1)) To
({?reference date} - (Weekday({?reference date}) - 1)),
{?reference condition} = &quot;Last 7 days&quot;,
({?reference date} - 6) To {?reference date},
{?reference condition} = &quot;Last full month&quot;,
DateSerial(Year({?reference date}), Month({?reference date}) - 1, 1) To
DateSerial(Year({?reference date}), Month({?reference date}), 1 - 1),
{?reference condition} = &quot;Last full week&quot;,
({?reference date} - 6 - WeekDay({?reference date})) To
({?reference date} - WeekDay({?reference date})),
{?reference condition} = &quot;Last year Month to Date&quot;,
CDate(Year({?reference date}) - 1, Month({?reference date}), 1) To
CDate(DateAdd(&quot;yyyy&quot;, -1, {?reference date})),
{?reference condition} = &quot;Last year Year to Date&quot;,
CDate(Year({?reference date}) - 1, 1, 1) To
CDate(DateAdd(&quot;yyyy&quot;, -1, {?reference date})),
{?reference condition} = &quot;Month to Date&quot;,
CDate(Year({?reference date}), Month({?reference date}), 1) To {?reference date},
{?reference condition} = &quot;Next 30 days&quot;,
{?reference date} To ({?reference date} + 30),
{?reference condition} = &quot;Next 31 to 60 days&quot;,
({?reference date} + 31) To ({?reference date} + 60),
{?reference condition} = &quot;Next 61 to 90 days&quot;,
({?reference date} + 61) To ({?reference date} + 90),
{?reference condition} = &quot;Next 91 to 365 days&quot;,
({?reference date} + 91) To ({?reference date} + 365),
{?reference condition} = &quot;Over 90 days&quot;,
Is <= ({?reference date} - 91),
{?reference condition} = &quot;Week to Date from Sunday&quot;,
({?reference date}- (Weekday({?reference date}) - 1)) To {?reference date},
{?reference condition} = &quot;Year to Date&quot;,
CDate(Year({?reference date}), 1, 1) To {?reference date},
True, // provide default handling and specify a valid range
CDate(1899, 12, 30) To CDate(1899, 12, 30)
)

This works great. Now, obviously you would need to add all those defaults to your PickList for the parameter and are most likely loathe to type them. No Problem.

Easy trick. Open the report I mentioned above from Crystal's Examples, go to the Parameter and edit. Choose Export Pick List, save as a text file named whatever you like.

Go to your report you are writing. Create your parameter field and then click set defaults. Choose Import Pick List and import the text file.

If you don't need all those conditions, just delete the ones that you don't want to use!

Hope you found the above helpful, if you still need help, just ask.

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[sniggih]

Thanks

I am still having a little problem. I entered the following formula in to my select expert.

if {?Default Date or Select Date} = 'Standard Date' then {profile:_Stage_II.Inc Period} in
DateAdd('m',3,Maximum({Profile:_Stage_II.Inc Period})) to DateAdd('m',3,Maximum({Profile:_Stage_II.Inc Period})) else {profile:_Stage_II.Inc Period}
in {?Start Date} to {?End Date}

I get the following error.

&quot;This function cannot be used because it must be evaluated later.&quot;

If I use WhileReadingRecords; I get the following error.

&quot;This function cannot be used because it must be evaluated later.&quot;

If I use WhilePrintingRecords; I get the following errot.

&quot;This formula cannot be evaluated at the time specified.&quot;

How can I fix this?

Thanks again for all your help.

 

[rosemaryl]

Dear Sniggih,

You can't use Maximum in a Select Statement because the records haven't been read.

I believe in your first statement/question you stated that the default was <<12 months ending from Current Month - 4 months>>.

So why not use that as your calc.

if {?Default Date or Select Date} = 'Standard Date' then {profile:_Stage_II.Inc Period} in

Dateadd(&quot;M&quot;,-4,CurrentDate) to Dateadd(&quot;M&quot;,-16,CurrentDate)

else {profile:_Stage_II.Inc Period}
in {?Start Date} to {?End Date}


If run today. the above says in &quot;English&quot; if the {?Default Date or Select Date} = Standard Date then {profile:_Stage_II.Inc Period}
in January 24 2002 to January 25 2001!

Leaving now for the holiday weekend, I will try to check up on this tonight or tomorrow to see if you needed more help.

Happy Memorial Day to all.

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[sniggih]

I am sorry I did not fully expand on my problem. I decided to use maximum Incurred_date because the dates that are populated in the Incurred_date will always be at the end of the month. So like you said if I use current date ran today would give me the 24th day of January instead of the end of January.

I was thinking if I could some how chop currentdate to give me for example 200205 (Vs 20020524) I could concatenate it with '01' then subtract 1 day...I should always get the last day of the previous month. So if I had a way to do that I could subtract 3 from the current month...then change that to the first of the month and subtract one day. I tried to do this but I can not get the syntax correct. Any suggestions...?

Thanks again for all of your help. Hope you have a great Memorial Day holiday.


Jean
bobbie.higgins@phs.com

 

[rosemaryl]

Dear Jean,

I am not at my own computer so I don't have Crystal right now, but I believe the following will work to get the last day of the previous month.

//@lastdayprevmonth
Datevar MD;
MD := Date(Month(CurrentDate),01,Year(CurrentDate));

Dateadd(&quot;M&quot;,1,MD)-1

//end formula

Like I said, I am not at my computer so I may have the syntax slightly off (hmmm I am sitting here wondering, does the year go first or the month up above in the MD variable)...but this should get you close. I will be at my computer in the morning and will check back to see if you need help.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[sniggih]

I think I am almost there. Here is the formula I used.


Datevar MD;

MD := Date(Year(CurrentDate),Month(CurrentDate),01)-1;


if {?Default Date or Select Date} = 'Standard Date' then {profile:_Stage_II.Inc Period} in
Dateadd(&quot;M&quot;,-3,MD) to Dateadd(&quot;M&quot;,-15,MD)
else {profile:_Stage_II.Inc Period}
in {?Start Date} to {?End Date}

Here is the error I got back.

A boolean is required here.

Thanks again for all your help.

Jean
bobbie.higgins@phs.com

 

[rosemaryl]

Dear Jean,

Sorry I wasn't able to get back to you sooner, I had a family emergency and am just now able to check my email.

I realized (remembered) when re-reading the posts above that you are doing this in the select. You need to put the code we used in for the date variable into the actual formula, which is actually just a slight adjustment on the formula that I provided to you in my third response. Try the following:

if {?Default Date or Select Date} = 'Standard Date' then {profile:_Stage_II.Inc Period} in


Dateadd(&quot;M&quot;,-3,Date(year(CurrentDate),month(Currentdate),1)-1
to
Dateadd(&quot;M&quot;,-15,Date(year(CurrentDate),monthCurrentdate),1)-1

else

{profile:_Stage_II.Inc Period} in {?Start Date} to {?End Date}


When you run it now, it should give you the date range you are looking for.

Hope this helps, please let me know if you need more help.

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.