Analytics- Creating a date filter or prompt that includes null values and filters the column.

The following formula was created to be used in a shelf list report which included prompts. For this use it allowed for the end user to filter down to items that have not been borrowed since a selected date while not filtering out null values (items that have never been borrowed).

Formula used:

IFNULL("Physical Item Details"."Last Loan Date (not In House) (calendar)",date '2000-01-01')

The IFNULL function replaces the null value with another, we used the date “2000-01-01” but dates further in the past could also be used if more appropriate.

This filter works for calendar dates.  For dates with a time stamp replace the value type “date” with “timestamp” in the formula.

Using the formula in a prompt

1. Create a column prompt for the field you want to filter

2. Edit the formula of the Prompt For Column

3. Enter your Column Formula and click ok

4. Select the Operator and User Input to determine how the field will be filtered. In this example we have used is less than or equal to

The report is then filtered to items that have a Last Loan Date selected by the user or earlier. The results will include items with the appropriate loan dates as well as items with a null value in the Last Loan Date column.

Example

search:

results:

Using the formula as a filter

1. Create a filter for the column of your choice

2.Select convert this filter to SQL and click ok

3. Edit the filter accordingly and click ok. In this example we have used:

"IFNULL("Physical Item Details"."Last Loan Date (not In House) (calendar)",date '2000-01-01')" <= date '2016-10-26'

This will yield the same results as the example provided when using a prompt.

The IFNULL function can also be used when editing columns, however if the column is displayed in the report the date the null was replaced with will be displayed rather than a blank cell.

Feedback or suggestions
Rachel Merrick
Australian Catholic University
Rachel.Merrick@acu.edu.au