Recipe - Easy Page Views Reporting

Skill level: Beginner

Organization: Kingdom First Solutions Inc. (KFS)

Requires Rock: 1.10.3

---

{# strip images & classes from the HTML but otherwise leave structure #}

Interactions is a great feature in Rock. However sometimes, the reporting doesn’t give you exactly what you need. Here’s a simple way to report on page views using filters for the page, person and dates.

1. Make sure Log Page Views is turned on for the site(s) that you want to report on. Admin Tools > Sites > YourSite > Advanced Settings
2. Create a page for your report
3. Add the Page Parameter Filter block to the page
4. Add the Dynamic Data block to the page
5. In the settings for the Page Parameter Filter block, set as shown 
• 
  
  
6. You will need to add filters for Page, Person and Date Range as shown. You will want to update the URL shown in the Post-HTML of the Page filter.
• 
  
  
  
  
  
  
7. For the Dynamic Data block criteria, set as shown
• 
  
  
8. Make sure you set the Page and Person to default to 0.
9. Copy and paste the SQL below into the Query field

   DECLARE @StartDate Date
   DECLARE @EndDate Date
   DECLARE @PersonAlias uniqueidentifier
   DECLARE @Interaction nvarchar(max)
   
   SET @Interaction = CASE @Page
                        WHEN '0' THEN NULL
                        ELSE @Page
                    END
   
   SET @PersonAlias = CASE @Person
                        WHEN '0' THEN NULL
                        ELSE CONVERT(uniqueidentifier, @Person)
                    END
   
   SET @StartDate = CASE @DateRange
                        WHEN '' THEN DATEADD(day,-7,GetDate())
                        ELSE CAST(LEFT(@DateRange,10) AS Date)
                    END
                    
   SET @EndDate = CASE @DateRange
                        WHEN '' THEN GetDate()
                        ELSE CAST(SUBSTRING(@DateRange,29,10) AS Date)
                    END
   
   SELECT p.[Id], p.[NickName], p.[LastName], i.[InteractionData], i.[InteractionDateTime]
   FROM [Interaction] i
       JOIN [PersonAlias] pa ON i.[PersonAliasId] = pa.[Id]
       JOIN [Person] p ON pa.[PersonId] = p.[Id]
   Where i.[InteractionData] = IIF(@Interaction IS NULL, i.[InteractionData], @Interaction)
       AND i.[InteractionDateTime] >= @StartDate
       AND i.[InteractionDateTime] <= @EndDate
       AND pa.[Guid] = IIF(@PersonAlias IS NULL, pa.[Guid], @PersonAlias)
   ORDER BY i.[InteractionDateTime] Desc

That's it! You're all set to go.