Wildcard (*) use for a date field?

[Bennie47250]

Designing a report that has several different date fields, for example Manufacturing Date, Ship Date, and Install Date.

The user wants to be able to provide a date range and they also want to be able to be prompted for all 3 date fields. Depending on which date field they select, one date range could influence the other.

For example, we are looking for products shipped between 2 dates. The date provided for the Install or Manufacturing prompt could limit the results. To eliminate that possibility, we would like to be able to enter a wildcard so it considers all dates. Since this is a date field it will not accept an *.

Would it be possible to provide a wildcard to get all dates?

If this is not possible, I think the user would be able to set an extremely low beginning date and extremely high ending date on the 2 date fields they are not filtering on but provide the actual date range for the field they do want to filter on. For example, for the Manufacturing and Install date prompt, they could enter 1/1/1970 to 5/31/2017 and for the Ship Date enter the specific date range they are looking for 2/1/2017 to 4/15/2017.

Occasionally the date fields will not be populated so this needs to be considered in the solution.

Thanks

 

[lbass]

First, the dates ARE related to each other, since they exist within the same record for a particular item, so if you select a date range for one date, the record might be excluded from consideration for other date range parameters. I can't imagine the scenario, but if you wanted the date selections to be truly independent I think you'd have to use separate subreports OR you could use a command which uses union statements to return the dataset three times, with a particular parameter limiting one of three sets of data.

If you are just aiming to make selection by one or more parameters optional, you can do something like the following. Also note that higher versions of CR allow for optional parameters, but I have never made use of them and am not sure how they work with date fields.

Try a selection formula like this which basically makes a particular date range optional:

(
(
minimum({?ship date})<> date(9999,09,09) and
(
isnull({Orders.Ship Date}) or
{Orders.Ship Date}={?ship date}
)
) or
minimum({?ship date})= date(9999,09,09)
) and
(
(
minimum({?order date})<>date(9999,09,09) and
(
isnull({Orders.Order Date}) or
{Orders.Order Date}={?order date}
)
) or
minimum({?order date})=date(9999,09,09)
) and
(
(
minimum({?required date})<>date(9999,09,09) and
(
isnull({Orders.Required Date}) or
{Orders.Required Date}={?required date}
)
) or
minimum({?required date})=date(9999,09,09)
)

The parameter prompts would have to instruct the user to enter 9999-09-09 (or the format specific to your version of CR) to select all dates for that parameter.

-LB