Rolling Date Filters for Data Warehouse Query Views

When you create a custom query view against the Blackbaud data warehouse, you can now enable a rolling date filter option for date fields. This filter option allows end users to filter queries based on dates or date ranges that are relative to the dates when the users run the queries.

Note: The rolling date filter option is only available for custom query views against the data warehouse. The filter option is not available for date fields on query views against the transactional database.

When users apply a date field with the rolling date option as a filter on a data warehouse query, the rolling date option allows them to specify dates or date ranges that are relative to the current date. When they place such a date field under Include records where on the query editor, the Apply Criteria screen appears and they can select the new "Rolling date" option in the second field. For example, they can use the rolling date filter option to filter the query so that its output includes records created 3 days before the date when the query runs.

To enable the rolling date filter option for a date field on a custom query view against the data warehouse, the query view spec must include the DATEDIMID field that corresponds to that date field. In the data warehouse, each date field has a corresponding DATEDIMID field that joins the date field to the date dimension table. On your custom query view spec, you must include the DATEDIMID field as a hidden output field so that queries based on that query view can find the date dimension ID field for the date field.

For example, if you include the FINANCIALTRANSACTIONDATE field in a custom query view against the data warehouse, you must also include the FINANCIALTRANSACTIONDATEDIMID field to expose the rolling date filter option for the FINANCIALTRANSACTIONDATE field.

Tip: For more information about how to implement the rolling date filter option for a date field, see Enable the Rolling Date Filter Option for Date Fields in Data Warehouse Query Views.