Dates in Report

[lthomas6]

I am trying to create a report which will do the below. Please assist. I am using Crystal 10 against an Oracle database.

1. Can we "Auto Select" dates based on the date a report is run. For example, if the report is run on any day from the first of the month through the 14th of the month, the dates for the selection criteria will be from the 15th to the end of LAST MONTH. If the report is run from the 15th through the last day of the month, the dates for the selection criteria should be the 1st through the 14th of the CURRENT month.

2. Can we have two sets of prompts, one that allows us to select a checkbox that will initiate #1 above OR one that allows us to enter in a "Start Date" and "End Date". Basically, they want to use the same report to schedule and to run "Ad Hoc" without the limitations of automatically selecting the dates.

Thanks.

 

[lbass]

You could create a string parameter {?dateselect} with two options: "Select report dates" and "Use default report dates". Then go to report->selection criteria->record and enter:

(
(
{?dateselect} startswith "Select" and
{table.date} >= {?Start} and
{table.date} < {?End}+1
) or
(
{?dateselect} startswith "Use" and
(
day(currentdate) in 1 to 14 and
{table.date} in dateserial(year(currentdate),month(currentdate)-1,15) to
dateserial(year(currentdate),month(currentdate),1)-1
) or
(
day(currentdate) >= 15 and
{table.date} in date(year(currentdate),month(currentdate),1) to
date(year(currentdate),month(currentdate),14)
)
)
)

-LB

 

[lthomas6]

This works however when choosing the Use default option, the report displays the dates from the previous selection.

How do I get the report to display the default report dates in the report header?

 

[lbass]

You need to use a formula in the report header as well, e.g.,

if {?dateselect} startswith "Select" then
totext({?Start},"MM/dd/yyyy") + " to " + totext({?End},"MM/dd/yyyy") else
if {?dateselect} startswith "Use" then
(
if day(currentdate) in 1 to 14 then
totext(dateserial(year(currentdate),month(currentdate)-1,15),"MM/dd/yyyy") + ' to ' +
totext(dateserial(year(currentdate),month(currentdate),1)-1,"MM/dd/yyyy") else
if day(currentdate) >= 15 then
totext(date(year(currentdate),month(currentdate),1),"MM/dd/yyyy") + " to " +
totext(date(year(currentdate),month(currentdate),14),"MM/dd/yyyy")
)

-LB