Date Selection, Select Expert vs. Parameter 1

[BillPeck]

I was playing around with date selections and got unexpected results, using Xtreme database

First I created a parameter for Order Date, but all records were retrieved, even though I entered 1/1/2003 to 12/31/2003.

Then I tried the Select Expert, that worked fine, but I prefer to use a parameter.

Seems like the parameter should be simple, what am I missing?

Thank you.

 

[bduke]

How are you checking for the date?

 

[BillPeck]

I set up the parameter field as type 'Date', using a range then entered the data in the required format: 1/1/2001 to 12/31/2001. The parameter doesn't do anything, even if I enter 2/27/2004 to 2/27/2004 it returns all records.

But if I enter two dates in the Select Expert, the data gets filtered.

Thanks.

Bill

 

[bdreed35]

Can you post your Record Selection Criteria for us to see?

Check Report, Edit Selection Criteria, Record.
Copy the code that is in the editor and post it.

~Brian

 

[vidru]

Adding a parameter to the report doesn't automatically restrict the data... you have to set it up.

Go to the Report menu > Edit Selection Formula > Record.

Then enter the formula to restrict the records returned based on your parameter field:

{Table.DateField} in {?DateRangeParameter}

-dave

 

[BillPeck]

Dave,

Thanks for the reply, your suggestion worked.

But I'm now trying to do a date range and not having any luck.

Here's the formula:
"Customer.Country} = "USA" and{Orders.Order Date} in {?Order Date 2}"

I changed the parameter to "Range Values". It prompts me for a range, the lower date is used but the upper date seems to be ignored.

What do I need to do for a date range?

Thanks again.

Bill

 

[vidru]

Looks like you've got it set up ok.

Run the report again, then go to Database > Show SQL Query to see what's being passed. The WHERE clause should look like:

WHERE
Customer.`Country` = 'USA' AND
Orders.`Order Date` >= {ts '2003-01-01 00:00:00.00'} AND
Orders.`Order Date` < {ts '2004-01-01 00:00:00.00'}

-dave

 

[BillPeck]

Great !

Thank you for the time, it looks excellent now.

Bill