How To Use Date Ranges In Complex Reports

Using Date Ranges

You can specify date ranges in reports. For example, you may wish to print all transactions recorded during the past month. Or you may want to pull records if a certain condition is met: for instance, if the transaction date is a particular day. This is possible with date range commands .

Review the How-to topics about Dates and Date Formatting to refresh your understanding of the basic date codes used by the report system.

WHERE clauses and 'if' Conditionals are used in report design for retrieving ranges of data. Please refer to the How-to topics about Filtering Data Using the WHERE Clause and Using Conditional Expressions to Filter Data.

Sample reports illustrating these concepts are included in the list of pre-defined report definitions. When you are in the Report Definition list, look for Sample Reports using WHERE and 'if' clauses.

If you would rather have Collect! Technical Services design your report, see the How-to topic "Provide Information to Comtech Systems for Custom Reports."

Syntax

The syntax for date ranges is the same as for any WHERE clause. The only difference is that the value field can provide detailed selection criteria.

Specifying A Date Range In Your Report

WHERE ({field_code} = {date_code} [.. {date_code}])

Example:

WHERE (@tr.pd = @m-2 .. @m-1-1)

field_code is any field code for a date field, such as Transaction Posted Date (@tr.pd).

date_code = @d[ +/- n ][ +/- nn ] when n and nn can be any number, and n represents units of the current date specifier (Day, Week, Month or Year) and nn represents Number of Days.

In the above example, we are dealing with Month (@m). [ n ] is -2 and ranges to -1.

This indicates transactions from two months ago (@m-2) up to last month (@m-1).

[ nn ] is -1. This indicates one day less than the beginning of last month (@m-1-1). The first -1 is attached to @m. The second -1 refers to a number of days.

If today is any day in June, @m is June 1st. This WHERE clause will pull all transactions posted from April 1st (@m-2) to April 30th (@m-1-1).

Please refer to other How-to topics about Dates and Date Ranges for more information.

Prompt For A Date Range At Run Time

When you enter a question mark (WITHOUT THE @), you will be prompted for a date range when the report is run.

dialog_code WHERE ({field_code} = ?)
dialog_code

@de WHERE (@de.li = ?)
@de

The code for this prompt starts with the code for the form you want to scan. For example, @de indicates the Debtor form. The second @de tells the report system to scan all debtor records. This creates a loop. Please refer to How To Print a List of Records and How To Use Loops to grasp the concept of Looping.

When this code runs, a form will display prompting the user for a Date Range. The form title will change depending on your field_code. In the above example, a form named "Debtor Listed Range" will display.

Whenever a prompt (?) is used with a Date field, the user will be prompted to enter a Date Range. The values entered are stored in the database as [ Transaction Summary Report.From ] and [ Transaction Summary Report.To ] and can be accessed with the codes @tsr.fr and @tsr.to.

The values for tsr.fr and tsr.to stay set until they are changed by another prompt for a date range. This means that you can reference these dates in your code as above. The result will be a Date Range that is identical to the range you set earlier in the prompt.

Prompt For Range Of Transaction Posted Dates

This prompts for a Posted Date range from transactions. The user can enter a date in the From and To fields when the Prompt box is displayed.

@tr WHERE (@tr.pda = ?) MAX = 1
@tr

To use these results in a report, you can use codes that Collect! reserves for holding the date values in a range.

@tsr.fr is used to hold the date value"From".

@tsr.to is used to hold the date value "To".

In the report body, this would look like:

List of All Transactions Created From @tsr.fr To @tsr.to

@tr WHERE (@tr.pda = @tsr.fr .. @tsr.to)
@tr.de<40> @tr.pda @tr.pd @tr.tu>12> @tr.di>12>
@tr

If the user entered April 1, 2001 in the From value and April 30, 2001 in the To value, this report snippet would display a list of transactions in this date range with a Report heading.

"List of All Transactions Created From April 1, 2001 To April 30, 2001"

This particular code snippet would then use the same range to filter transactions where the Posted Date is in the range the user chose.

Math With Dates

Dates can be represented in various ways. This is described in detail in How To topics about Dates and Date Formatting. In addition to the information presented in those topics, notice that you can add or subtract any number from the basic date codes to arrive at the date you wish to print. Also, you can add and subtract days, weeks, months and years.

For example:

@d-30

This is today minus 30 days.

@w-1

This is the first day of this week minus one week; in other words it's the first day of last week.

@m+1

This is the first day of next month.

@y+1

This is the first day of next year.

NO parentheses () are required in Date math. Math with Fields DOES require parentheses ().

Additional examples are shown in the How-to topic on Using WHERE Clauses with Dates.