Show NULL rows in Date Filter

Adding a date filter to a dashboard (including Timebar or Filter Snippet) results in NULL transactions, even when the Max Available Range is used. Users may expect the Max Available Range to include all transactions.

This article explains how to show transactions that have a NULL value in the date field by replacing the NULL value with a future date in a calculated column.

To show NULL rows in Date Filter:

1. Create a Derived Field calculation using the coalesce function to replace the NULL values with a future date.

   For example:

   

   coalesce(payment_date, TEXT_TO_TIME('2047-12-31','YYYY-MM-DD'))

2. Use the new calculated date filter as the timebar or Filter Snippet.

Date Filter Values Override

Note: This feature is optional and requires designer user permissions.

Filter Value Override is used to improve the load performance of the date filter. This is achieved by specifying a min and max date, saving the system effort by collecting this data from the business view.

To override the Date Filter values:

1. Navigate to Administration → Manage Business Views.

2. Select the required Business View → Source Creation.

3. Add a Filter Value Entry.

4. Provide an Entity Name.

5. Select the Connection.

6. Select Custom SQL.

7. Enter UNION ALL SQL where the first union gets the First Date, and the Second Union gets the Max date.

   The SQL might change depending on the DB type.

   This example is based on Postgres SQL:

   select min(payment_date) from iby_payments_all

   UNION ALL

   select '2047-12-31'::date

8. Run Apply and Save the changes.

9. Navigate to the Fields Screen.

10. Select the date field.

11. Select the Filter Values menu.

12. Choose Dynamic Override.

13. Select the Entity created in step 4.

14. Select the field and click Save.

Alternatively, you can set the Static date option, but the Maximum date value is limited to 2030.

Hide extra column from The Reports

Note: This feature is optional and requires designer user permissions.

To hide the extra column from the reports:

1. Navigate to Administration → Manage Business Views.

2. Select the required Business View → Fields → Update Field Capabilities

3. Filter the Derived field.

4. Disable the settings where you do not want the new field to show and leave the Filtering field enabled. .