Data View Filters Using "Less Than" with Date Attributes

Some time ago an issue was reported on GitHub (#1808) about Data View filters. We are going to fix this issue in Rock v17. But that might affect what your data views return, so you need to know how to update your existing data views so they work the way you intended. First, some background on what the issue actually is and how it can affect your results.

The report was in regards to the "less than" and "less than or equal to" filters when used with date attributes. The specific problem is that these filters will include empty values when used with attributes. We found that this also affects Integer and Decimal attribute values. Meaning, if you ask for a list of everybody with a date attribute whose value is less than "Jan 1, 2020" then it will also include individuals with no value in that attribute.

In some cases, including empty values is probably what you want. For instance, if you want a list of everybody without an up to date background check that is already serving, you might have built your data view to say something like "In group of type Serving Team and Background Check Date < Jan 1, 2020". In this case, it makes sense to get those empty values because their background check isn't up to date - since they never had one.

There are other cases where you wouldn't want those values included. Suppose you want a list of all individuals that have been at the church for a long time. You might build your data view with something like "People whose First Visit Date < Jan 1, 2010". In this case, it doesn't make sense to include individuals that don't have a value in their First Visit Date attribute - because they never attended.

One of the big reasons we consider this an issue is consistency. If you use the same logic against, for example, the Anniversary Date property on a Person, it will not include people without an anniversary date. Most other places in Rock also follow this pattern. A missing/empty value is not considered to be a match for a "less than" or "less than or equal to" filter. We are going to fix this issue in Rock v17.

The reason you are reading about this now is because your existing data views might start to return different data. We want to make sure you have a chance to update them before that change goes into effect. Actually fixing your data views is quite straight forward. Because the issue revolves around empty values, you can simply add an additional filter to your data view to explicitly state that you either do want empty values or don't want empty values.

Suppose you have this data view using the "less than" operator to filter by Baptism Date:

After updating the data view it might look something like this:

In truth, for this change you don't need the nested filter group. We just included it for clarity. If you wanted it to explicitly be "Baptism Date Less than '1/1/2010'" but also include individuals with no Baptism Date then you would switch the second filter to "Baptism Date Is Blank" and the nested group to "Show if Any...".

So, if you update your data views to explicitly state if you want empty values or not, then when you upgrade to Rock v17 you won't have to worry about the results suddenly changing. And this is also a good chance to go through those data views and make sure they are actually giving you the data you expected anyway. They might already be giving you the data you want and you just need to update them to ensure that doesn't change. Or, this all might be a surprise to you and you find out some of your data views aren't actually providing the data you thought they were and can update them to correct that.

Finding these data views might be tedious. Thankfully, itʼs a pretty specific set of criteria that needs to be checked. We have a SQL script below you can download and run on your database that will do its best to find these data views for you so you know which ones to update.

These are data views with potential problems. If you already have an explicit "is not blank" filter, it can't detect that and exclude the data view. But it should detect any data views with a "less than" or "less than or equal to" filter against a date, date time, integer or decimal attribute type. This also means it will be up to you to check things off the list of SQL results, because running it again after fixing the data view won't remove it from the results.

DECLARE @FilterData TABLE ([Id] INT, [ParentId] INT, [Key] NVARCHAR(200), [Type] INT)

INSERT INTO @FilterData
    SELECT
        [DVF].[Id]
        , [DVF].[ParentId]
        , TRY_CAST(JSON_VALUE([DVF].[Selection], '$[0]') AS NVARCHAR(200)) AS [Key]
        , TRY_CAST(JSON_VALUE([DVF].[Selection], '$[1]') AS INT) AS [Type]
    FROM [DataViewFilter] AS [DVF]
    INNER JOIN [EntityType] AS [ET] ON [ET].[Id] = [DVF].[EntityTypeId]
    WHERE [ET].[Name] = 'Rock.Reporting.DataFilter.PropertyFilter'
      AND ISJSON([DVF].[Selection]) = 1

;WITH CTE_Tree AS (
    SELECT
        [DVF].[Id]
        , [DVF].[ParentId]
        , [DVF].[Key]
    FROM @FilterData AS [DVF]
    WHERE [DVF].[Key] NOT LIKE 'Property_%'
      AND [DVF].[Type] IN (512, 1024)
    
    UNION ALL

    SELECT
        [DVF].[Id]
        , [DVF].[ParentId]
        , [CT].[Key]
    FROM [CTE_Tree] AS [CT]
    INNER JOIN [DataViewFilter] AS DVF ON [DVF].[Id] = [CT].[ParentId]
)

SELECT
    [DV].[Id]
    , [DV].[Name]
    , [C].[Name] AS [CategoryName]
    , [A].[Name] AS [AttributeName]
    , [IQ1].[Key] AS [AttributeKey]
FROM [DataView] AS [DV]
INNER JOIN [Category] AS [C] On [C].[Id] = [DV].[CategoryId]
INNER JOIN [DataViewFilter] AS [DVF] ON [DVF].[DataViewId] = [DV].[Id]
INNER JOIN (
    SELECT
        [Id]
        , CASE WHEN [Key] LIKE 'Attribute_%' THEN SUBSTRING([Key], CHARINDEX('_', [Key])+1, CHARINDEX('_', [Key], CHARINDEX('_', [Key])+1 ) - CHARINDEX('_', [Key]) - 1 ) ELSE [Key] END AS [Key]
    FROM [CTE_Tree] AS [CT]
    WHERE [CT].[ParentId] IS NULL
) AS [IQ1] ON [IQ1].[Id] = [DVF].[Id]
INNER JOIN [Attribute] AS [A] ON [A].[EntityTypeId] = [DV].[EntityTypeId] AND [A].[Key] = [IQ1].[Key]
WHERE [A].[FieldTypeId] IN (
    SELECT
        [Id]
    FROM [FieldType]
    WHERE [Class] = 'Rock.Field.Types.DateFieldType' OR [Class] = 'Rock.Field.Types.DateTimeFieldType' OR [Class] = 'Rock.Field.Types.IntegerFieldType' OR [Class] = 'Rock.Field.Types.DecimalFieldType'
)