Category / Section
How to Filter Data for Scheduled Reports Using Date Range Parameters
Published:
Updated:
When scheduling reports, it’s often necessary to filter data based on a specific date range so that the report includes only relevant records. This can be achieved by configuring date range parameters with dynamic expressions that automatically calculate the required interval.
Report Design Steps
-
Create Start Date and End Date parameters in your report.
Example: Last 30 Days (To filter data for the last 30 days)
- Start Date (30 days before today)
=DateAdd(DateInterval.Day, -30, Today())- End Date (current date)
=Today() -
Assign these expressions as Default Values so the date range is calculated dynamically at runtime.
When the report runs on schedule, filtering is applied automatically based on these dynamic values.
Common Date Expressions
| Interval | Description | Start Date Expression | End Date Expression |
|---|---|---|---|
| Last 7 Days | Data from the last 7 days including today | =DateAdd(DateInterval.Day, -7, Today()) |
=Today() |
| Last Week | Previous 7-day period | =DateAdd(DateInterval.Day, -7, Today()) |
=Today() |
| Last Month | Previous calendar month | =DateAdd(DateInterval.Month, -1, Today()) |
=Today() |
| Current Year | From Jan 1 of current year to today | =DateSerial(Year(Today()), 1, 1) |
=Today() |
| Last Week (Mon–Fri only) | Previous week excluding weekends | =DateAdd(DateInterval.Day, -((Weekday(Today())+5)), Today()) |
=DateAdd(DateInterval.Day, -(Weekday(Today())-2), Today()) |
Why Use Dynamic Expressions?
- No manual updates are needed for each schedule.
- Ensures reports always display the correct date range.
- Works for any interval (e.g., last week, last month, current year).
You can configure these parameters as hidden so users cannot modify them when scheduling.
